AccessでVBAコードを使ってテーブルリンクを再作成する方法

Accessでテーブルリンクを作成する場合、ローカルPCに設定したDSNを使って設定したりできますが、
個人的に使うような用途以外で、複数人のPCで利用するようなAccessファイルを作成した場合に、
あちらのPCにDNS設定が入っていないから動かないといったような事が起こり問題となることがあります。
個別にDSN設定をしてもらえる場合はまだいいですが、そうでない場合、
以下に示すようなVBAコードでDSNなしのテーブルリンク設定をすることができます。
(SQL Serverへのテーブルリンクを想定しています)

テーブルリンク再作成

Copy

Sub テーブルリンク再作成()
    On Error Resume Next
    
    ' テーブル定義
    Dim p_td As TableDef
    ' 既にリンクが存在する場合は削除する (存在しない場合のエラーは On Error Resume Nextにてスキップ)
    CurrentDb.TableDefs.Delete "dbo_Users"
    ' 再設定用のテーブル定義を作成
    Set p_td = CurrentDb.CreateTableDef( _
        Name:="dbo_Users", _
        Attributes:=dbAttachSavePWD, _
        SourceTableName:="dbo.Users", _
        Connect:="ODBC;DRIVER=SQL Server;SERVER=<サーバ名>;DATABASE=<データベース名>;UID=<ユーザID>;PWD=<パスワード>" _
    )
    ' 再設定
    CurrentDb.TableDefs.Append p_td
End Sub

動作概要は以下のようになります。
- CurrentDb.TableDefs.Deleteにて既存のテーブルリンクがある場合削除します。(テーブルリンク名はスキーマ名とテーブル名をアンダーバーで繋いだものとしています。たぶんAccess標準の動き)
- CurrentDb.CreateTableDefにて、TableDefを作成します。
- 作成したTableDefをCurrentDb.TableDefs.Appendにて追加します。
作成するテーブル名はdboスキーマのUsersというテーブルを想定しています。
<サーバ名>、<データベース名>、<ユーザID>、<パスワード>の部分はご自分の環境に置き換えて下さい。

なお、上記はユーザID/パスワード認証の例です。
Windows認証の場合、Connectの部分をTrusted_Connection=YesとすればOKです。

Copy

Set p_td = CurrentDb.CreateTableDef( _
    Name:="dbo_Users", _
    Attributes:=dbAttachSavePWD, _
    SourceTableName:="dbo.Users", _
    Connect:="ODBC;DRIVER=SQL Server;SERVER=<サーバ名>;DATABASE=<データベース名>;Trusted_Connection=Yes" _
)

AccessでVBAコードを使ってテーブルリンクを再作成する方法

目次

テーブルリンク再作成

参考

参考サイト