Access VBA:リンクテーブルのリンク元をVBAで変更する方法

AccessのVBAを使ってリンクテーブルのリンク元をVBAで変更する方法について覚書。

リンクしているテーブルを持つリンク元ファイルの保存場所が変更になったときなどに一括してリンクテーブルのパスを変更先のパスに書き換える方法。

最近何回か使ったので覚書しておく。

以下にサンプルプログラムを記述。

リンクテーブルのリンク元を変更するVBAのサンプルプログラム

DAOを使用したプログラムなのでDAOオブジェクトライブラリの参照設定を忘れずに。

VBAの画面で、[ツール] - [参照設定]でDAOを指定してください。

リンクテーブルを指定する場合のVBAのサンプルプログラム

任意のリンクテーブルを指定してリンク元を変更する場合のサンプルプログラムを以下に記載します。

Sub Sample01()
Dim db As DAO.Database, tb As DAO.TableDef
  
    Set db = CurrentDb
    Set tb = db.TableDefs("運送会社")
  
    tb.Connect = ";DATABASE=C:¥Northwind.mdb;TABLE=運送会社"
    tb.RefreshLink ' リンク情報の更新
  
End Sub
  

プログラムの注意点

プログラムの注意点としては、Connectプロパティで指定する接続文字列の先頭に";"が入ること。

リンクテーブルをデザインで開いたときの接続文字列では、先頭に";"がついてないの忘れがちですが、VBAで指定する場合は忘れずに。

ちなみにもともとリンクしていたテーブルを変更することはできません。

つまり上の例で言えば、運送会社でリンクしていたテーブルを取引先など他のテーブルに変えることはできない。

リンクテーブルマネージャでもできないんだからVBAでもできるわけない。

リンク元のパスしか変更できないということね。

データベースの全てのリンクテーブルのリンク元を一括で変更するVBAのサンプルプログラム

"For Each"ステートメントを使ってテーブルオブジェクトを列挙し、全てのリンクテーブルパスを変更するサンプルプログラムを以下に記載します。

1つのファイルからしかリンクしていない場合にリンクテーブルのリンク元を一括して変更することができる。

Sub Sample02()
Dim db As DAO.Database, tb As DAO.TableDef
    Set db = CurrentDb
        'テーブルオブジェクトを列挙
        For Each tb In db.TableDefs
            'リンクテーブルだけを処理
            If tb.Connect <> "" Then
                tb.Connect = ";DATABASE=C:¥Northwind.mdb;TABLE=" & tb.Name
                tb.RefreshLink ' リンク情報の更新
            End If
        Next tb
End Sub
  

接続文字列に関する補足

上記のサンプルプログラムで使用している接続文字列は、SQL Serverにデータソースレス接続する際の形式をとっています。

データソースレス接続は、DSNを作成しなくても接続に指定されているドライバがインストールされていれば接続ができる方法です。

詳細は以下の記事を参照してください。

ODBCデータソース(DSN)を作成せずにAccessデータベースにデータソースレスで接続するサンプルプログラム

ODBCドライバ経由でAccessのデータベースに接続する際にODBCデータソースを作成せずにVBAのプログラム中のODBC接続文字列に直接指定して接続するサンプルプログラムを覚書。 通常ODBCドライバ経由でデータベースに接続する場合、 ...

selifelog.com

2008.03.10

接続文字列の具体例を以下に記載します。

上記の接続情報をもとにした接続文字列は以下のようになります。

Driver={SQL Server};DATABASE=northwind;UID=sa;PWD=password01;SERVER=dbsrv01

SQL ServerのExpress版を使用してる場合、接続サーバー名はデフォルトだと"サーバー名\Express"になり、接続文字列は以下のようになります。

Driver={SQL Server};DATABASE=northwind;UID=sa;PWD=password01;

SERVER=dbsrv01\sqlexpress

※表示上改行されていますが、改行は不要です。

データベースの全てのリンクテーブルのリンク元を一括で変更するVBAのサンプルプログラム(データソースレス接続)

以下にSQL Serverのリンクテーブルの接続情報を一括で変更/更新するサンプルプログラムを記載します。

Sub Sample02()
Dim db As DAO.Database, tb As DAO.TableDef
Dim strCon As String

'接続文字列(データソースレス接続形式)
strCon = "Driver={SQL Server};DATABASE=northwind;UID=sa;PWD=password01;SERVER=dbsrv01¥sqlexpress"
    Set db = CurrentDb
    
    For Each tb In db.TableDefs
        If tb.Connect <> "" Then
            tb.Connect = strCon
            tb.RefreshLink
        End If
    Next tb
        
End Sub
  

リンクテーブルのリンク元をVBAで変更する方法のまとめ

サーバーのリプレースなどによりリンク元の接続情報が変更となった際に、新しい変更先をVBAを使って変更/更新する方法を紹介しました。

リンクテーブルの数が多い場合は、一括で変更/更新するサンプルプログラムも紹介しました。

基本的には、リンクテーブルマネージャでもできる内容ですが、VBAを使えばひとつひとつ指定する必要がなく、一度作ってしまえば何度でも流用できるので便利かと思います。