AccessのリンクテーブルをVBAで設定する方法のSQLServer版を紹介したいと思います。
MySQLとは少しだけ記述方法が違います。
DSNを使ったODBC接続とDSNを使わないODBC接続があります。
２種類の接続方法について紹介したいと思います。
手動で出来るけど毎回手動で行いたくない時などに、
VBAでボタン１つでリンクテーブルが設定できるので非常に便利になります。
DSNとは？
DSNとは、ODBCなどのデータベース接続インターフェースにおいて、プログラム側が操作対象のデータベースを指定するための識別名。この識別名を含む接続文字列のことを指してDSNという場合もある。
DSNの設定方法
(1)ODBC設定
ファイル名を指定して実行で「%windir%\SysWOW64\odbcad32.exe」
と入力して３２ビットのODBC設定を開きます。
ユーザーDSNかシステムDSNを選択してDSN設定をします。
私はシステムDSNを使う方が多いですが、ユーザーDSNでも問題ありません。
ファイルDSNは使ったことありませんが、説明見るとこれを使って共有すれば良さそうにも思いますが
今一つ用途メリットがわからず。。
(2)DSN設定
新しいデータソースを名前を入れてくださいとあります。
このデータソース名がDSN名です。自分で定義した名前を入力してください。
windows認証でもどちらでも・・
(3)接続テスト
接続テストができます。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を使用しないで実現可能でした。これで問題なくリンク接続できました。
コメント