こんにちは！現場のシステム開発で日々Accessと格闘している皆さん、先輩エンジニアの私です。
「社内のサーバーが移行することになった……」
「SQL Serverのパスワードを変更したら、Accessのリンクテーブルが全部エラーを起こした……」
こんな悪夢のような状況に直面したことはありませんか？
何十個、あるいは何百個もあるリンクテーブルを、一つひとつ手動で右クリックして「リンクテーブル マネージャー」を開き、パスワードを打ち直す……。そんな不毛な作業で大切な残業時間を溶かすのは、もう今日で終わりにしましょう。
今回は、Accessの裏側を支配する「TableDefオブジェクト」の`Connect`プロパティをVBAで自在に操り、サーバーの移行やパスワード変更を完全に自動化する極意を伝授します。
ここをクリアすれば、Access VBAの構造的な理解が一段上のステージに上がりますよ。さあ、一緒に扉を開けましょう！
—
1. リンクテーブルの正体を知る（敵を知る）
私たちが普段何気なく使っているAccessの「リンクテーブル」。
実はこれ、Accessの内部に実体があるわけではありません。Accessは単に「あそこのSQL Serverの、このテーブルを見に行ってね」という「看板（ポインター）」を保持しているに過ぎないのです。
その看板に書かれている住所や合言葉（接続情報）が格納されている場所、それが`TableDef`オブジェクトの `Connect` プロパティです。
Connectプロパティの文字列の裏側
SQL ServerにODBC接続しているリンクテーブルの `Connect` プロパティの中身を覗いたことはありますか？ 大体こんな文字列が入っています。
ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=Old_Server_Name;DATABASE=MyDatabase;UID=my_user;PWD=old_password;
お気づきですね？
この文字列の中にある `SERVER` や `UID`、`PWD` をVBAで動的に書き換えて `RefreshLink` メソッドを叩いてやれば、プログラム側から一瞬で接続先をコントロールできるというわけです。
—
2. 【実践】接続文字列を解析・書き換えるVBAコード
それでは、現場で即座に使える実用的なプロシージャを公開します。
このコードは、現在のプロジェクト内にあるすべてのSQL Serverリンクテーブルをスキャンし、指定した新しいサーバー名とパスワードに自動で書き換える優れものです。
標準モジュールに貼り付けて実行してみてください。
Option Explicit
Public Sub UpdateSQLServerLinks()
‘ =========================================================================
‘ 目的: すべてのSQL Serverリンクテーブルの接続文字列を動的に書き換え、再接続する
‘ 著者: シニアアーキテクト
‘ =========================================================================
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim targetConnect As String
Dim successCount As Long
Dim errorCount As Long
‘ — 設定エリア（ここをご自身の環境に合わせて書き換えてください） —
Const NEW_SERVER As String = “New_Server_Name\SQLEXPRESS”
Const NEW_DB As String = “MyDatabase”
Const NEW_UID As String = “app_user”
Const NEW_PWD As String = “SuperSecretPassword123”
‘ ————————————————————————-
Set db = CurrentDb
successCount = 0
errorCount = 0
‘ 画面描画を停止して処理速度を爆発的に上げる（お約束のテクニック）
DoCmd.Hourglass True
On Error GoTo ErrorHandler
‘ データベース内のすべてのテーブル定義をループ処理
For Each tdf In db.TableDefs
‘ テーブルのAttributes（属性）をチェックし、「外部テーブル」かつ「ODBC接続」のものに絞り込む
‘ ※SystemTablesやローカルテーブルを除外するための必須ガード
If (tdf.Attributes & dbAttachedODBC) = dbAttachedODBC Then
‘ 新しいODBC接続文字列を組み立てる
‘ ※Driver部分は既存のものを維持しつつ、接続先と認証情報を強制上書きします
targetConnect = “ODBC;DRIVER=ODBC Driver 17 for SQL Server;” & _
“SERVER=” & NEW_SERVER & “;” & _
“DATABASE=” & NEW_DB & “;” & _
“UID=” & NEW_UID & “;” & _
“PWD=” & NEW_PWD & “;” & _
“Trusted_Connection=No;”
‘ Connectプロパティに新しい文字列を流し込む
tdf.Connect = targetConnect
‘ 【超重要】Connectプロパティの変更をAccessとバックエンドに適用させる
tdf.RefreshLink
successCount = successCount + 1
Debug.Print “成功: ” & tdf.Name
End If
Next tdf
DoCmd.Hourglass False
MsgBox “リンクテーブルの更新が完了しました！” & vbCrLf & _
“成功件数: ” & successCount & ” 件”, vbInformation, “処理成功”
Exit Sub
ErrorHandler:
‘ 万が一エラーが出たテーブル名を表示し、処理を止めずに次へ進む耐障害設計
errorCount = errorCount + 1
MsgBox “テーブル [” & tdf.Name & “] の更新中にエラーが発生しました。” & vbCrLf & _
“エラー番号: ” & Err.Number & vbCrLf & _
“詳細: ” & Err.Description, vbCritical, “更新エラー”
Resume Next
End Sub
—
3. コードのキモと「初心者が陥りやすい罠」
上記のコードには、Access VBAを極めるための重要なエッセンスが詰まっています。いくつかポイントを解説しましょう。
① `dbAttachedODBC` によるフィルタリング
Accessの `TableDefs` コレクションには、ローカルに作成した通常のテーブルも、システムが裏で使っている隠しテーブルも、すべてごちゃ混ぜに入っています。
もし `If` 文での絞り込みを忘れると、ローカルテーブルのConnectプロパティを書き換えようとして盛大にエラー（実行時エラー）が発生します。
`If (tdf.Attributes & dbAttachedODBC) = dbAttachedODBC` というビット演算のガードを忘れないようにしましょう。
② `RefreshLink` を忘れない
`tdf.Connect = targetConnect` で文字列を書き換えた「だけ」では、まだAccessは動きません。
`tdf.RefreshLink` を実行して初めて、Accessが実際にSQL Serverへ新しい情報でハンドシェイク（接続テスト）を行いにいきます。 ここを書き忘れる人が非常に多いので注意してください。
③ パスワードがコードに残るリスクへの配慮
今回のサンプルではコード内に直接パスワードを書いていますが、セキュリティ要件が厳しい現場では、ログインフォームをポップアップさせて動的に `UID` と `PWD` を変数に格納し、それを組み上げる設計にするのがプロの作法です。
—
先輩エンジニアからのエール
お疲れ様でした！ここまで理解できれば、あなたもう「マクロの記録」に頼る初学者ではありません。DAOライブラリのオブジェクトモデルを自在に操る立派なAccessエンジニアです。
サーバーの移転、IPアドレスの変更、パスワードの定期変更――。
今後どんなインフラの変更イベントが起きたとしても、このVBAを走らせるだけで数秒で環境適応が完了します。業務自動化の快感を、ぜひご自身のシステムでも味わってみてください。
「ここをもっとこうしたい」「こんなエラーが出るんだけど」といった疑問があれば、いつでもエンジニアリングの扉を叩いてくださいね。応援しています！
