AccessのリンクテーブルをVBAで設定する方法のSQLServer版を紹介したいと思います。
MySQLとは少しだけ記述方法が違います。
DSNを使ったODBC接続とDSNを使わないODBC接続があります。
2種類の接続方法について紹介したいと思います。
手動で出来るけど毎回手動で行いたくない時など、やはりVBAで記述しておけば
ボタン1つでリンクテーブルが設定できるので非常に便利になります。
▼開発環境:win8.1 + access2016 + SQLServer
目次
DSNとは?
DSNとは、ODBCなどのデータベース接続インターフェースにおいて、プログラム側が操作対象のデータベースを指定するための識別名。この識別名を含む接続文字列のことを指してDSNという場合もある。
出典:http://e-words.jp/w/DSN-1.html
DSNの設定方法
ファイル名を指定して実行で「%windir%\SysWOW64\odbcad32.exe」
と入力して32ビットのODBC設定を開きます。
ユーザーDSNかシステムDSNを選択してDSN設定をします。
私はシステムDSNを使う方が多いです。ユーザーDSNでも全然OKです。
ファイルDSNは使ったことがないんだけど、説明見るとこれを使って共有すれば楽・・?
今一つ用途メリットがわからず。。
新しいデータソースを名前を入れてくださいとあります。
このデータソース名がDSN名です。自分で定義した名前です。
windows認証でもどちらでも・・
接続テストができます。SQLServerに接続するのに必要なのは
サーバー名
DB名(入れ物)
ユーザー名
パスワード
です。ユーザ名とパスワードは、DB作成時に権限付与すると思うので
その時に作成したユーザ名とパスワードになります。
テスト接続が正常にできれば、Accessの外部接続→でリンクテーブルが作成できます。
DSNを使ったODBC接続
推奨はこちらの接続方法です。
ODBCのシステムDSN設定で設定した情報を元にODBC接続をする方法です。
Dim td As TableDef Dim stConnect As String Dim stDSN,stDatabase as String stDSN = "testDSN" ' DSN名 ODBC設定で付けた名前 stDatabase = "testDb" ' DB名 stUsername = "hoge" stPassword = "hoge" stConnect = "ODBC;DSN=" & stDSN & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword & ";Encrypt=no;TrustServerCertificate=no;ConnectRetryCount=24;ConnectRetryInterval=5;Connection Timeout=120;" Set td = CurrentDb.CreateTableDef("リンク先テーブル名", dbAttachSavePWD, "リンク元テーブル名", stConnect) CurrentDb.TableDefs.Append td
stConnect の部分が非常に大事です。
ここでは、あらかじめ設定してあるDSN経由でODBC接続してSQLServerと接続しています。
そこでリンク設定をするとできます。
手動でまずリンクテーブルを設定してみてください。正しくできるとプロパティに
stConnect のパスがセットされると思います。
その作業をVBAで行っているということです。
応用として複数のテーブルを一括リンクする場合は
ループしながらリンク設定をしていけば実現可能です。
その場合、サーバー情報をどこからか取得してきて設定するのが理想的です。
案としては外だしINIファイルで対応も可能ですし、設定テーブルを作成して
そこからサーバー情報を読み込むのでもありだとは思います。
個人的にはお客様にあまり直接テーブルを操作してもらいたくないのでINIファイル派です。
DSNを使わないODBC接続
こちらの方法でもできましたが検証が上手くできていません。
考え方はNSN接続と一緒です。(多分)
stConnectの接続文字列を下記のように変更してみてください。
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stdbSource & ";DATABASE=" & stDatabase & ";Uid=" & stUsername & ";Pwd=" & stPassword & ";"
ODBC設定のシステムDSNを使用しないで実現可能でした。これで問題なくリンク接続できました。