【Access】リンクテーブルをVBAで行う(SQLServer編)

AccessのリンクテーブルをVBAで設定する方法のSQLServer版を紹介したいと思います。

MySQLとは少しだけ記述方法が違います。
DSNを使ったODBC接続とDSNを使わないODBC接続があります。
２種類の接続方法について紹介したいと思います。

手動で出来るけど毎回手動で行いたくない時など、やはりVBAで記述しておけば
ボタン１つでリンクテーブルが設定できるので非常に便利になります。

▼開発環境：win8.1 + access2016 + SQLServer

DSNとは？

DSNとは、ODBCなどのデータベース接続インターフェースにおいて、プログラム側が操作対象のデータベースを指定するための識別名。この識別名を含む接続文字列のことを指してDSNという場合もある。

DSNの設定方法
ファイル名を指定して実行で「%windir%\SysWOW64\odbcad32.exe」
と入力して３２ビットの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を使用しないで実現可能でした。これで問題なくリンク接続できました。

DSNとは？

DSNを使ったODBC接続

DSNを使わないODBC接続

関連記事: