MENU

リンクテーブル管理と再接続のベストプラクティス

nanikatoaccess

目次

  1. はじめに：なぜリンクテーブル方式を使うのか
  2. リンクテーブルの基本的な作成手順
  3. 再接続（再リンク）の必要性と課題
    • 3.1 接続先の変更
    • 3.2 DSNレス接続とDSN接続
    • 3.3 共有フォルダ＆複数ユーザー環境
  4. 再リンクを自動化するVBA実装例
    • 4.1 リンクテーブル一覧の取得
    • 4.2 新しい接続情報を使った再リンク
    • 4.3 入力チェックとエラー処理
  5. リンクテーブル管理のベストプラクティス
    • 5.1 パフォーマンスを意識したテーブル・ビューの選択
    • 5.2 ユーザー権限・セキュリティ考慮
    • 5.3 フロントエンド配布と自動更新の仕組み
  6. リンクテーブルと「Call データベース接続」の併用
    • 6.1 ADO直接接続との使い分け
    • 6.2 パススルークエリやストアドプロシージャとの組み合わせ
  7. まとめ
ないものはない！お買い物なら楽天市場

1. はじめに：なぜリンクテーブル方式を使うのか

AccessとSQL Serverを連携する方法は、大きく分けて以下の2つがあります。

  1. リンクテーブル: Access側から「外部データの取り込み」でSQL Serverのテーブル（またはビュー）を「リンク」する方法。Accessではこれらがローカルテーブルのように見え、フォームやレポートを簡単に作成・編集できます。
  2. ADO/ODBC直接接続（VBAコード）: Call データベース接続() で作成した cn (ADODB.Connection) を用いて、SQL文を直接実行します。動的な処理や複雑なトランザクション、パラメータ化クエリなどに向いています。

リンクテーブル は、Accessに慣れた方が最低限の設定でSQL Serverを利用できるという点が大きなメリットです。一方で、接続先が変わったときにリンク情報を再設定（再リンク） しなければならないという運用上の課題が生じます。
本記事では、この「リンクテーブルの再接続」を自動化・簡便化する方法と、それに伴う注意点について解説していきます。

2. リンクテーブルの基本的な作成手順

  1. Accessで［外部データ］タブ→［新しいデータソース］→［データベースから］→［ODBCデータベース］ を選択。
  2. 「リンク テーブルを作成してデータ ソースに接続する」を選択 し、ODBCデータソースを指定。
  3. SQL Server上のテーブルやビュー を選択してリンクを作成すると、Access内にテーブルが表示される。アイコンには「地球＋矢印」のようなマークが付きます。

この時点で、Access上は「リンクテーブル」 としてSQL Serverのデータを操作できます。フォームを作成する際は、リンクテーブルを基にレコードソースを設定すればOKです。

3. 再接続（再リンク）の必要性と課題

3.1 接続先の変更

運用を進めていると、次のような要件で「リンク先を変えたい」というケースが発生します。

  • 開発環境と本番環境を切り替える: テスト用SQL Serverと本番用SQL Serverでサーバー名やデータベース名が異なる
  • サーバーマイグレーション: SQL Serverのホスト先をオンプレミスからAzure SQL Databaseに移行した

いずれの場合も、リンクテーブルが抱えている接続文字列を更新してあげる必要があります。

3.2 DSNレス接続とDSN接続

DSN (Data Source Name)ファイル を使ってSQL Serverへの接続を定義する方法と、DSNレスで直接接続文字列を扱う方法があります。DSNファイルに依存していると、ファイルのパスが変わったり、ユーザーのPCごとに設定が異なったりして混乱を招くことがあります。
DSNレス接続 であれば、接続情報（サーバー名・データベース名・認証情報など）をVBAコード上で一元管理できます。

3.3 共有フォルダ＆複数ユーザー環境

リンクテーブルを持ったAccessファイルを共有フォルダに置くと、複数のユーザーが同時に開こうとしたときに接続先が不一致になってしまうことがあります。
理想としては、「フロントエンド分割」 して各ユーザーのPCに配布し、再リンク処理を自動化しておけば、常に最新の正しい接続先を使うことができます。

4. 再リンクを自動化するVBA実装例

4.1 リンクテーブル一覧の取得

Accessには「TableDef」というオブジェクトがあり、現在のAccessファイルが保持しているテーブルやリンクテーブルの情報を取得できます。

  • TableDef.SourceTableName: 元のSQL Server上のテーブル名
  • TableDef.Connect: 接続文字列（ODBC;…など）
'------------------------------------------------------------------------------------------
'【目的】   現在のAccessファイルに存在するすべてのリンクテーブルの情報を取得し、デバッグ出力
'【ポイント】TableDefオブジェクトを使い、Connectプロパティでリンク情報を確認
'------------------------------------------------------------------------------------------
Public Sub リンクテーブル一覧出力()

    Dim tdf As DAO.TableDef
    For Each tdf In CurrentDb.TableDefs
        ' 「MSys」などのシステムテーブルやローカルテーブルは除外
        If (tdf.Attributes And dbAttachedODBC) <> 0 Then
            Debug.Print "テーブル名:" & tdf.Name & ", 接続文字列:" & tdf.Connect & ", ソース:" & tdf.SourceTableName
        End If
    Next tdf

End Sub

4.2 新しい接続情報を使った再リンク

以下では、DSNレス接続の例を示します。たとえば、以下のような文字列をLinkに使います（SQL Server認証の場合）:

ODBC;Driver={SQL Server Native Client 11.0};Server=MyServerName\SQLEXPRESS;
Database=MyDatabase;Uid=MyUser;Pwd=MyPassword;

次のVBAコードは、既存のリンクテーブルに対して新しい接続文字列を付与し直すサンプルです。

'------------------------------------------------------------------------------------------
'【目的】   既存リンクテーブルの接続先を新しい接続文字列で再設定（再リンク）する
'【ポイント】
'   ①DSNレス接続で接続先を指定
'   ②dbAttachedODBC属性を確認してリンクテーブルだけを処理
'   ③エラー発生時はロールバック（全または個別処理）の検討
'------------------------------------------------------------------------------------------
Public Sub リンクテーブル再設定(strServer As String, strDatabase As String, strUser As String, strPass As String)

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strNewConnect As String
    
    ' 新しい接続文字列（DSNレス）を作成
    strNewConnect = "ODBC;Driver={SQL Server Native Client 11.0};" & _
                    "Server=" & strServer & ";" & _
                    "Database=" & strDatabase & ";" & _
                    "Uid=" & strUser & ";" & _
                    "Pwd=" & strPass & ";"

    Set db = CurrentDb
    
    ' テーブル定義を順番に確認
    For Each tdf In db.TableDefs
        ' ODBCリンクテーブルだけを対象にする
        If (tdf.Attributes And dbAttachedODBC) <> 0 Then
            ' 再リンク（Connectを書き換え）
            tdf.Connect = strNewConnect & ";APP=Access;"
            tdf.RefreshLink   ' 実際に再リンクを反映
        End If
    Next tdf
    
    MsgBox "リンクテーブルを新しい接続先に再設定しました。", vbInformation

End Sub

上記の Linkテーブル再設定 サブに、例えば本番環境への接続情報を渡すと、既存の全リンクテーブルが一度に更新されます。

入力チェックとセキュリティ

  • strServer, strDatabase, strUser, strPass など引数はNullや空文字でないか必ずチェックしたほうが安全です。
  • パスワードはソースコードにベタ書きせず、INIファイルや暗号化した設定テーブルなどで管理する方が望ましいです。

4.3 入力チェックとエラー処理

実際の運用では、再リンク時に以下のようなエラーや問題が起こるかもしれません。

  • サーバーが見つからない
  • ログインに失敗（パスワード誤り）
  • テーブル名がSQL Server側で変更されていた

これらをキャッチしてユーザーにメッセージを出す、ロールバックするなどの処理が必要です。簡易的には On Error GoTo ErrorHandler を使い、再リンク失敗したテーブルを表示するなどの対処が一般的です。

5. リンクテーブル管理のベストプラクティス

5.1 パフォーマンスを意識したテーブル・ビューの選択

リンクテーブルとして扱うデータは、アクセス頻度が高い かつ 行数がそこまで多くない テーブルを優先的にリンクするのが基本です。大規模集計や大量行を扱う場合は、サーバー側でビューを作成 し、そのビューをリンクテーブル化するとパフォーマンスが向上する場合があります。

5.2 ユーザー権限・セキュリティ考慮

  • SQL Server側で各テーブル・ビューへのアクセス権を設定し、必要最小限の権限を与えることが重要。
  • Windows認証を使うか、SQL Server認証を使うかの方針を決めておき、定期的にパスワードを変更するなどのセキュリティ対策を行いましょう。

5.3 フロントエンド配布と自動更新の仕組み

  • フロントエンド分割: Accessの機能で「データベースの分割」を使い、テーブルをリンクした「バックエンド（.accdb/.mdb）」と、フォームやレポートなどを含む「フロントエンド（.accdb/.mdb）」に分割するのが望ましい。
  • 自動更新: フロントエンドはユーザーPCへ配布し、起動時に「新バージョンがあればダウンロード→再リンク実行」という流れを自動化できます。バージョンアップのたびに手動でリンクを付け替える手間を省けます。

6. リンクテーブルと「Call データベース接続」の併用

6.1 ADO直接接続との使い分け

「リンクテーブル」で日常的なデータ入力や照会を行い、より複雑な処理やトランザクション管理が必要な場面では Call データベース接続() を使う というハイブリッド運用が効果的です。

  • リンクテーブル向き: 単純なレコード参照・更新、フォームやレポートの通常操作
  • ADO接続向き: パラメータ化クエリによるINSERT/UPDATE、ストアドプロシージャ呼び出し、トランザクション制御

6.2 パススルークエリやストアドプロシージャとの組み合わせ

リンクテーブルだけでなく、パススルークエリ（Accessクエリ内でSQL Serverに直接SQLを投げる機能）を定義しておき、複雑な集計処理はサーバー側に任せるとパフォーマンスの改善につながります。
ストアドプロシージャをパススルークエリで呼び出す場合、リンクテーブルのように自動再リンクは必要ありませんが、クエリの「ODBC接続文字列」 を変更する仕組みを作る場合もあります。

7. まとめ

リンクテーブル管理と再接続のベストプラクティス を要約すると、以下のポイントが重要です。

  1. リンクテーブルのメリット: AccessからローカルテーブルのようにSQL Serverを扱えるため学習コストが低く、フォーム・レポートを簡単に作成できる。
  2. 再接続の必要性: 開発・本番環境切り替え、サーバー移行などでリンク先を都度変更する場合は、再リンク処理の自動化が鍵となる。
  3. DSNレス接続で一元管理: DSNファイルを使わずに接続文字列をVBAで作成し、TableDef.Connect を更新→ RefreshLink する仕組みが便利。
  4. セキュリティと権限: SQL Server認証やWindows認証を使い分け、必要最小限の権限管理とパスワード管理を徹底する。
  5. フロントエンド分割と配布戦略: 各ユーザーPCにフロントエンドを配り、自動更新や再リンクを行うことで運用コストを下げる。
  6. ADOとの併用: 単純操作はリンクテーブル、複雑ロジックや大規模処理はCall データベース接続()でADO接続するハイブリッド運用が最適。

リンクテーブルを正しく管理すれば、Access×SQL Serverのアーキテクチャをより簡単かつ安定的に活用できます。実装の詳細を自動化しておくことで、システム移行やバージョンアップ時の工数を大幅に削減できるでしょう。ぜひご活用ください。

本記事が、リンクテーブル管理と再接続のポイントを押さえるうえで役立てば幸いです。今後もより高度なテーマ（ストアドプロシージャとの連携、Azure SQL Database移行など）を取り上げていきますので、ぜひご期待ください。

DMM
ABOUT ME
管理人
管理人
駆け出しブロガー
InstagramYouTube
入社した会社では、Accessを活用した基幹システムが長年運用されていました。しかし、開発者の高齢化により保守が困難となり、システムの維持・更新が急務に。 ほぼAccessに触れたことのなかった私は、ゼロから学びながら基幹システムを再構築してみることに。ついにはAccessによるシステム開発エンジニアとしてのスキルを身につけるまでに成長。 元々の業務のノウハウとそれを効率化するためのツール(Access)によって業務効率化システムをいくつも開発してきました。 みなさんの”なにか（業務のノウハウ）”とAccessで業務効率化を実現するお役に立てれば幸いです。
ないものはない！お買い物なら楽天市場
googleアドセンス
Recommend
こんな記事も読まれています
記事URLをコピーしました