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

スポンサーリンク

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

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

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

▼開発環境:win8.1 + access2016 + SQLServer
スポンサーリンク

DSNとは?

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

DSNの設定方法

(1)ODBC設定

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

コメント

タイトルとURLをコピーしました