Access VBAでリンクテーブルの接続先を自動修復！起動時に「壊れたリンク」とおさらばする賢い方法
皆さん、こんにちは！Access VBAの世界へようこそ。
「マクロの記録」から一歩踏み出して、もっとパワフルなデータベース操作をしたい、そう思っているあなた。素晴らしい第一歩を踏み出しましたね！
今日は、Accessデータベース開発で誰もが一度は経験する「あの問題」に終止符を打つ、ちょっとした、でも強力なテクニックをご紹介します。それは、リンクテーブルの接続先を、データベース起動時に自動で修復するというもの。
バックエンドのファイルパスが変わったり、ファイルが移動されたりすると、Accessのリンクテーブルは「壊れたリンク」になってしまいますよね。あの「テーブルがありません」というエラーメッセージ、何度見てもうんざりします。
でも、大丈夫。このブログ記事を読み終える頃には、そんな面倒な手作業から解放され、Access VBAの基礎と、データベースをより堅牢にするための大切な考え方をマスターしているはずですよ。
まるで、データベースの「かかりつけ医」のように、起動時に健康診断をして、問題があればサッと治してくれる。そんな賢い仕組みを一緒に作っていきましょう！
—
なぜリンクテーブルの接続先が「壊れる」のか？
まず、なぜリンクテーブルの接続先が壊れてしまうのか、その原因を理解しておきましょう。
Accessでは、複数のデータベースファイルにまたがってデータを管理するために「リンクテーブル」という機能を使います。例えば、フロントエンド（フォームやクエリ、レポートがあるファイル）とバックエンド（実際のデータが格納されているファイル）を分ける、といった構成が一般的ですね。
このリンクテーブルは、バックエンドデータベースの「場所（ファイルパス）」と「テーブル名」を記録しています。Accessは、この情報をもとに、バックエンドデータベースに接続してデータを読み書きしているわけです。
もし、バックエンドデータベースのファイルが移動されたり、名前が変わったり、あるいはネットワーク上の場所が変わったりすると、Accessは記録された「場所」にファイルを見つけられなくなります。これが「壊れたリンク」の状態です。
（※実際には画像は挿入されませんが、イメージとしては、Accessが「あれ？ファイルがないぞ？」と困っている様子を想像してください。）
この問題を解決するには、本来なら「外部データ」->「リンクテーブルマネージャー」を開いて、一つずつ接続先を修正する必要があります。しかし、これがテーブル数が多いと、本当に大変！
そこで、今回は起動時に自動でこの接続先をチェックし、必要であれば修正するVBAコードを開発します。
—
VBAの扉を開けよう！ `Application` と `CurrentDb` オブジェクト
Access VBAを使いこなす上で、まず知っておきたいのが、データベースそのものを操作するための「窓口」となるオブジェクトです。今回は、その中でも特に重要な二つをご紹介しましょう。
1. `Application` オブジェクト：Accessアプリケーションそのもの
`Application` オブジェクトは、今あなたが操作しているAccessアプリケーション全体を表します。これを通して、Accessの様々な設定を変更したり、新しいデータベースを開いたり、あるいは現在開いているデータベースを取得したりできます。
例えば、Accessのバージョン情報を取得したいときなどに使います。
‘ Accessのバージョン情報をメッセージボックスに表示する例
MsgBox “現在のAccessのバージョンは ” & Application.Version & ” です。”
今回のテーマでは、直接 `Application` オブジェクトを多用するわけではありませんが、VBAの世界では「このAccessアプリケーションで何かをしたい！」と思ったときに、まず思い浮かべるべきオブジェクトです。
2. `CurrentDb` オブジェクト：現在開いているデータベース
そして、今回の主役とも言えるのが `CurrentDb` オブジェクトです。これは、今、あなたが作業している（開いている）Accessデータベースそのものを表します。
`CurrentDb` を使うと、このデータベース内のテーブル、クエリ、フォーム、レポート、モジュールなど、あらゆるオブジェクトにアクセスし、操作することができます。
例えば、テーブル一覧を取得したいときには、`CurrentDb.TableDefs` というプロパティを使います。
‘ 現在のデータベースにあるテーブルの名前をすべてメッセージボックスに表示する例
Dim tdf As TableDef
Dim strTableNames As String
For Each tdf In CurrentDb.TableDefs
‘ システムテーブルなどを除外するために、名前が “$” で始まらないものだけを表示
If Left(tdf.Name, 1) <> “$” Then
strTableNames = strTableNames & tdf.Name & vbCrLf
End If
Next tdf
MsgBox “このデータベースにあるテーブルは以下の通りです：” & vbCrLf & strTableNames
この `CurrentDb.TableDefs` は、データベースに定義されているすべてのテーブル（組み込みテーブル、ローカルテーブル、そしてリンクテーブル！）のリストを保持しています。
—
`TableDef` オブジェクトと `Connect` プロパティ：リンクテーブルの「住所」
`CurrentDb.TableDefs` で取得できるのは、`TableDef` というオブジェクトの集まりです。この `TableDef` オブジェクト一つ一つが、データベース内の個々のテーブル定義を表しています。
そして、この `TableDef` オブジェクトには、テーブルに関する様々な情報が格納されています。特にリンクテーブルの場合、重要なのが `Connect` プロパティです。
`Connect` プロパティには、リンクテーブルが接続しているバックエンドデータベースの接続文字列が格納されています。この接続文字列の中に、バックエンドデータベースのファイルパスが含まれているのです。
例えば、Excelファイルにリンクしている場合、`Connect` プロパティは「Excel 8.0;Database=C:\Data\MyData.xlsx」のような文字列になります。
Accessファイルにリンクしている場合、Accessのバージョンによって少し異なりますが、例えば「Access 2007;DATABASE=C:\Data\MyBackend.accdb」のようになります。
`TableDef` オブジェクトの `Connect` プロパティを調べる
実際に `Connect` プロパティの中身を見てみましょう。
‘ 指定したテーブルのConnectプロパティを表示する例
Dim tdf As TableDef
Dim strTableName As String
strTableName = InputBox(“接続先を調べたいテーブル名を入力してください:”)
On Error Resume Next ‘ テーブルが存在しない場合のエラーを無視
Set tdf = CurrentDb.TableDefs(strTableName)
On Error GoTo 0 ‘ エラーハンドリングを元に戻す
If Not tdf Is Nothing Then
If tdf.Connect <> “” Then ‘ Connectプロパティが空でない（リンクテーブルの可能性が高い）
MsgBox strTableName & ” のConnectプロパティ：” & vbCrLf & tdf.Connect
‘ リンクテーブルかどうかを判定する追加情報 (SourceTableName) も確認
If tdf.SourceTableName <> “” Then
MsgBox strTableName & ” はリンクテーブルです。元のテーブル名：” & tdf.SourceTableName
End If
Else
MsgBox strTableName & ” はローカルテーブルか、Connectプロパティが設定されていません。”
End If
Else
MsgBox “テーブル ‘” & strTableName & “‘ は見つかりませんでした。”
End If
Set tdf = Nothing ‘ オブジェクト変数を解放
このコードを実行すると、指定したテーブルがリンクテーブルであれば、その接続情報（ファイルパスなど）がメッセージボックスに表示されます。
`tdf.Connect` が空でなく、かつ `tdf.SourceTableName` が設定されている場合、そのテーブルはリンクテーブルであると判断できます。
—
起動時に自動修復！ VBAコードの実装
さて、いよいよ本題です。データベースが起動したときに、自動でリンクテーブルの接続先をチェックし、必要であれば修正するVBAコードを実装しましょう。
この処理は、データベースを開いたときに自動的に実行されるように設定するのが一般的です。Accessには、データベース起動時に自動実行されるマクロ（またはVBAコード）を設定する機能があります。
1. 自動実行マクロの設定
まず、VBAコードを実行するための「トリガー」を用意します。
1. Accessで、データベースを開きます。
2. リボンメニューの「データベースツール」タブをクリックします。
3. 「マクロ」グループにある「マクロ」ボタンをクリックします。
4. 開いたマクロデザイナーで、「新しいマクロを作成」を選択します。
5. マクロデザイナーのツールバーにある「コードの表示」ボタンをクリックします。
6. 表示されたVBAエディタ（標準モジュール）に、後述するVBAコードを貼り付けます。
7. VBAエディタで、このVBAコードを呼び出すための簡単なマクロを作成します。例えば、`Sub AutoExec()` という名前のプロシージャを作成し、その中に `Call UpdateLinkTables` のようなコードを書きます。
8. 作成したマクロを保存します。名前は例えば「AutoExec」など、Accessが自動実行するマクロ名に合わせます。
9. Accessのオプション設定で、このマクロ（またはVBAプロシージャ）を自動実行するように設定します。
- 「ファイル」メニュー -> 「オプション」 -> 「現在のデータベース」
- 「アプリケーションのオプション」セクションにある「起動時」の「フォームを表示する」のチェックを外し、「表示するフォーム/ページ」で何も選択しないか、あるいはダミーのフォームを選択します。（※自動実行したいVBAコードは、この「起動時」設定とは別に、VBAエディタで `AutoExec` という名前のサブプロシージャを作成してそこに記述すると、Accessが自動的に認識します。）
【重要】 `AutoExec` サブプロシージャについて
Accessは、データベースを開いたときに、標準モジュール（Module1など）に `AutoExec` という名前のサブプロシージャが存在すると、それを自動的に実行します。
なので、VBAエディタで標準モジュールを作成し、そこに `AutoExec` という名前で以下のVBAコードを記述するのが最も簡単な方法です。
2. VBAコードの実装
それでは、リンクテーブルの接続先を更新するVBAコードを記述しましょう。
ポイント：
- バックエンドの新しいパスを定義する: どこにバックエンドファイルがあるかをVBAコードで指定します。
- すべてのテーブル定義をループする: `CurrentDb.TableDefs` を使って、データベース内のすべてのテーブル定義を順番に見ていきます。
- リンクテーブルかどうかを判定する: `Connect` プロパティが空でなく、`SourceTableName` が設定されているかで判断します。
- 接続先を更新する: `Connect` プロパティを新しいパスで上書きします。
- エラーハンドリング: 予期せぬエラーが発生しても処理が止まらないようにします。
‘==========================================================================
‘ モジュール名: basLinkTableUpdater
‘ 目的: データベース起動時にリンクテーブルの接続先を自動更新する
‘==========================================================================
Option Compare Database
Option Explicit
‘==========================================================================
‘ プロシージャ名: AutoExec
‘ 概要: Access起動時に自動実行されるプロシージャ。
‘ リンクテーブルの接続先更新処理を呼び出す。
‘==========================================================================
Public Sub AutoExec()
‘ — データベース起動時に自動実行される —
‘ リンクテーブルの接続先を更新する関数を呼び出す
Call UpdateLinkTables
End Sub
‘==========================================================================
‘ プロシージャ名: UpdateLinkTables
‘ 概要: データベース内のすべてのリンクテーブルを走査し、
‘ Connectプロパティを更新する。
‘==========================================================================
Public Sub UpdateLinkTables()
Dim tdf As TableDef ‘ テーブル定義オブジェクト
Dim db As DAO.Database ‘ 現在のデータベースオブジェクト
Dim strOldConnect As String ‘ 更新前のConnect文字列
Dim strNewConnect As String ‘ 更新後のConnect文字列 (バックエンドの新しいパス)
Dim blnUpdated As Boolean ‘ 更新が行われたかどうかを示すフラグ
‘ —- 設定項目 —-
‘ ここに、バックエンドデータベースの新しいパスを設定してください。
‘ 例1: 同じフォルダにある場合
‘ strNewConnect = “Access 12.0;DATABASE=” & CurrentProject.Path & “\YourBackendDatabase.accdb”
‘ 例2: 特定のフォルダにある場合
strNewConnect = “Access 12.0;DATABASE=C:\YourNewPath\YourBackendDatabase.accdb”
‘ 注意: Accessのバージョンによって、”Access 12.0″ の部分は変わります。
‘ Access 2007/2010/2013/2016/2019/365 の場合は “Access 12.0;”
‘ Access 2003 以前の場合は “Access 9.0;” や “Jet DBS;” などになります。
‘ Link Table Managerで一度接続しておくと、正しいConnect文字列が分かります。
‘ —- 処理開始 —-
blnUpdated = False ‘ 初期化
‘ 現在のデータベースオブジェクトを取得
Set db = CurrentDb
‘ リンクテーブルの接続先が更新されたかどうかを記録する変数
Dim updateCount As Long
updateCount = 0
‘ データベース内のすべてのテーブル定義をループ処理
For Each tdf In db.TableDefs
‘ テーブルがリンクテーブルであるか判定
‘ Connectプロパティが空でなく、かつSourceTableNameが設定されていればリンクテーブル
If tdf.Connect <> “” And tdf.SourceTableName <> “” Then
strOldConnect = tdf.Connect ‘ 更新前の接続情報を取得
‘ —————————————————–
‘ ここで、接続先を比較・判定し、更新が必要か判断します。
‘ 今回はシンプルに、常に新しいパスで上書きする例を示します。
‘ より厳密にする場合は、strOldConnectを解析して、
‘ ファイルパス部分だけを比較・置換することも可能です。
‘ —————————————————–
‘ 新しいConnect文字列を設定 (設定項目で定義したパスを使用)
‘ strNewConnect は、上記の設定項目で定義済みです。
‘ Connectプロパティを更新
On Error Resume Next ‘ エラーが発生しても処理を続行
tdf.Connect = strNewConnect
tdf.RefreshLink ‘ リンクテーブルの情報を再読み込みさせる
On Error GoTo 0 ‘ エラーハンドリングを元に戻す
‘ 更新が成功したか（または変更があったか）を確認
‘ Connectプロパティが実際に変更されたか、またはRefreshLinkで問題がなかったかをチェック
‘ (tdf.Connect <> strOldConnect) は、Connectプロパティが書き換わったかどうかの判定に役立ちますが、
‘ RefreshLinkで問題が解消されたかどうかがより重要です。
‘ ここでは、RefreshLinkがエラーなく完了したことをもって「更新された」とみなします。
If Err.Number = 0 Then
‘ 更新成功
Debug.Print “テーブル ‘” & tdf.Name & “‘ の接続先を更新しました。”
Debug.Print ” 以前: ” & strOldConnect
Debug.Print ” 現在: ” & tdf.Connect
updateCount = updateCount + 1
blnUpdated = True
Else
‘ 更新失敗 – エラーログなどに記録すると良いでしょう
Debug.Print “テーブル ‘” & tdf.Name & “‘ の接続先更新中にエラーが発生しました。”
Debug.Print ” エラー番号: ” & Err.Number & “, 説明: ” & Err.Description
‘ エラーが発生した場合でも、強制的にリンクを再設定しようと試みる
‘ (RefreshLink が失敗した場合でも、Connect プロパティ自体は設定されている可能性があるため)
If tdf.Connect <> strOldConnect Then
‘ Connect プロパティ自体は変更された場合
Debug.Print ” Connect プロパティは変更されましたが、RefreshLink に失敗しました。”
End If
Err.Clear ‘ エラーをクリア
End If
End If
Next tdf
‘ —- 処理完了 —-
‘ オブジェクト変数を解放
Set tdf = Nothing
Set db = Nothing
‘ 更新が行われた場合にメッセージを表示
If blnUpdated Then
MsgBox updateCount & ” 件のリンクテーブルの接続先を更新しました。”, vbInformation
Else
‘ MsgBox “リンクテーブルの接続先更新は不要でした。”, vbInformation ‘ 必要であればコメント解除
End If
End Sub
‘==========================================================================
‘ 補足: Connect文字列の解析と更新について
‘
‘ Connectプロパティは、データベースの種類によって異なる形式を取ります。
‘ 例:
‘ Access: “Access 12.0;DATABASE=C:\Data\MyBackend.accdb”
‘ Excel: “Excel 8.0;DATABASE=C:\Data\MyData.xlsx”
‘ SQL Server: “ODBC;DSN=MyDataSourceName;UID=MyUser;PWD=MyPassword;”
‘
‘ より複雑なケース（例：ネットワークドライブの変更、サーバー名の変更など）では、
‘ strOldConnectを文字列操作関数（InStr, Mid, Replaceなど）で解析し、
‘ ファイルパスやサーバー名部分だけを動的に変更する必要があります。
‘
‘ 例: ファイルパス部分だけを置換する場合
‘ Dim strPath As String
‘ Dim lngPathStart As Long
‘ Dim lngPathEnd As Long
‘
‘ lngPathStart = InStr(strOldConnect, “DATABASE=”) + Len(“DATABASE=”)
‘lngPathEnd = InStr(lngPathStart, strOldConnect, “;”) ‘ 次の区切り文字まで
‘
‘ If lngPathStart > Len(“DATABASE=”) Then ‘ DATABASE= が見つかった場合
‘ strPath = Mid(strOldConnect, lngPathStart, lngPathEnd – lngPathStart)
‘ ‘ ここで strPath を新しいパスに置き換える処理を行う
‘ ‘ 例: strNewConnect = Replace(strOldConnect, strPath, “C:\NewPath\NewFile.accdb”)
‘ Else
‘ ‘ DATABASE= が見つからなかった場合の処理 (通常は発生しない)
‘ End If
‘
‘ ※上記はあくまで概念的な例です。実際の文字列解析は、
‘ Connect文字列の仕様を正確に理解して実装する必要があります。
‘ 基本的には、Link Table Managerで一度正しく設定した際のConnect文字列を
‘ 参考にすることをお勧めします。
‘==========================================================================
コードの解説
- `Public Sub AutoExec()`: このプロシージャは、Access起動時に自動実行されます。`basLinkTableUpdater` というモジュールに記述することで、Accessが自動で認識してくれます。
- `Call UpdateLinkTables`: `AutoExec` から、実際の更新処理を行う `UpdateLinkTables` プロシージャを呼び出しています。
- `strNewConnect = “Access 12.0;DATABASE=C:\YourNewPath\YourBackendDatabase.accdb”`: ここが最も重要です！ バックエンドデータベースの新しいパスを、Accessが認識できる形式で指定してください。`”Access 12.0;”` の部分は、お使いのAccessのバージョンに合わせてください。
- Access 2007〜365: `”Access 12.0;”`
- Access 2003: `”Access 9.0;”`
- 古いバージョンや他のデータベース形式では、さらに異なる場合があります。
- `C:\YourNewPath\YourBackendDatabase.accdb` の部分を、実際のバックエンドファイルへのパスに置き換えてください。
- `Set db = CurrentDb`: 現在開いているデータベースオブジェクトを取得しています。
- `For Each tdf In db.TableDefs`: `db.TableDefs` コレクションをループし、データベース内のすべてのテーブル定義 (`TableDef` オブジェクト) を一つずつ取り出します。
- `If tdf.Connect <> “” And tdf.SourceTableName <> “” Then`: これは、そのテーブルがリンクテーブルであるかを判定する条件です。`Connect` プロパティに何らかの値が入っており、かつ `SourceTableName`（リンク元テーブル名）が設定されていれば、リンクテーブルと判断できます。
- `tdf.Connect = strNewConnect`: これが接続先を更新する核心部分です。 `Connect` プロパティに、先ほど設定した新しい接続文字列を代入します。
- `tdf.RefreshLink`: `Connect` プロパティを変更した後、このメソッドを呼び出すことで、Accessにリンクテーブルの情報を再読み込みさせ、新しい接続先を有効にします。
- `On Error Resume Next` / `On Error GoTo 0`: `tdf.Connect = strNewConnect` や `tdf.RefreshLink` でエラーが発生した場合（例えば、指定したパスにファイルが存在しないなど）、処理が停止しないようにしています。エラーが発生した場合は、`Err.Number` でエラーコードを確認し、デバッグウィンドウに表示するようにしています。
陥りやすいエラーとその対策
1. `Connect` プロパティの文字列形式が間違っている:
- 原因: Accessのバージョンや、リンクしているバックエンドのファイル形式（Access, Excel, SQL Serverなど）によって、`Connect` プロパティの文字列の形式が異なります。
- 対策: 一度「リンクテーブルマネージャー」でリンクテーブルを正しく設定し、そのテーブルの `Connect` プロパティをVBAで調べて、正しい形式を把握しましょう。上記のコード例の `”Access 12.0;”` の部分を、ご自身の環境に合わせて調整してください。
2. バックエンドファイルのパスが正しくない:
- 原因: VBAコードに記述した `strNewConnect` のパスが間違っている、またはバックエンドファイルがそのパスに存在しない。
- 対策:
- パスのタイプミスがないか確認する。
- ネットワークドライブの場合、ドライブレターが割り当てられているか、あるいはUNCパス（`\\ServerName\ShareName\…`）が正しく指定されているか確認する。
- バックエンドファイルが実際に指定したパスに存在することを確認する。
- VBAのデバッグウィンドウ（`Ctrl + G` で表示）に、`Debug.Print tdf.Connect` と `Debug.Print tdf.Name` を入れて、処理中にどのような接続情報が確認されているかを見てみましょう。
3. `AutoExec` プロシージャが自動実行されない:
- 原因:
- `AutoExec` という名前のプロシージャが標準モジュールに存在しない。
- VBAコードが保存されていない。
- Accessのオプションで、マクロの実行が無効になっている（セキュリティ警告が表示されて実行がブロックされている）。
- 対策:
- VBAエディタで、標準モジュールに `Public Sub AutoExec()` という名前でプロシージャが正しく記述・保存されているか確認してください。
- Accessのセキュリティ設定を確認します。「ファイル」->「オプション」->「セキュリティセンター」->「セキュリティセンターの設定」->「マクロの設定」で、「すべてのマクロを無効にする（通知なし）」以外を選択し、再起動時にセキュリティ警告が表示されたら「コンテンツの有効化」をクリックしてください。
4. `RefreshLink` が失敗する:
- 原因: バックエンドファイルへのアクセス権がない、ファイルが破損している、ネットワークに接続できないなど、様々な原因が考えられます。
- 対策:
- VBAコードに `On Error Resume Next` を入れて、エラーが発生しても処理が止まらないようにし、`Err.Number` でエラーコードを確認して原因を特定しましょう。
- 手動で「リンクテーブルマネージャー」を開き、同じテーブルの接続先を更新できるか試してみてください。手動でも更新できない場合は、VBAコードの問題ではなく、ファイルやアクセス権の問題である可能性が高いです。
—
さらに堅牢にするために（応用編）
今回ご紹介したコードは、バックエンドのパスが「固定」で変わる場合、あるいは「特定の新しいパス」が分かっている場合に有効です。
もし、バックエンドファイルが移動するたびに手動でパスを更新するのが面倒、あるいはどのパスに移動するか分からない、というような状況であれば、さらに高度な仕組みが必要になります。
例えば：
- 設定ファイルの使用: バックエンドのパスを、VBAコード内に直接書くのではなく、別途テキストファイルやAccessテーブルに保存しておき、起動時にその設定ファイルを読み込むようにする。
- ファイル検索機能: バックエンドファイルがどこにあるか分からない場合、指定したフォルダ以下を検索して、ファイル名からバックエンドファイルを特定する機能を追加する。
- ネットワークドライブの再接続: ネットワークドライブが切断されている場合に、自動的に再接続を試みる処理を入れる。
これらの応用は、データベースの利用状況や要件に応じて、さらにVBAの知識を深めていくことで実現可能です。
—
まとめ：Access VBAでデータベースの「安定運用」を実現！
いかがでしたでしょうか？
今回は、Access VBAを使って、リンクテーブルの接続先を起動時に自動修復する、実践的なツール開発の方法をご紹介しました。
- `Application` や `CurrentDb` オブジェクトで、Accessアプリケーションやデータベースにアクセスする方法。
- `TableDef` オブジェクトの `Connect` プロパティが、リンクテーブルの「住所」であること。
- `tdf.RefreshLink` メソッドで、接続先情報を更新すること。
- `AutoExec` プロシージャを利用して、データベース起動時に処理を自動実行させる方法。
これらの基本をマスターすれば、あなたのAccessデータベースは、より堅牢で、ユーザーフレンドリーなものになります。
「壊れたリンク」に悩まされる日々はもう終わり。自信を持って、Access VBAの世界をさらに探求していきましょう！
もし、このコードを試してみて、うまくいかなかったり、さらに質問があったりする場合は、遠慮なくコメントなどで教えてくださいね。皆さんのAccess VBAライフを、心から応援しています！
