AccessとSQL ServerをODBC接続する方法｜設定手順と接続エラー対策

概要

この記事でわかること

• ODBC・DSN・リンクテーブルの役割とインポートとの違い
• Accessのbit数に合うドライバーと接続前の確認事項
• DSNの作成からリンクテーブル作成までの具体的な操作
• 接続エラーと更新できない問題の切り分け方
• 利用者PCへの配布と接続先変更時の確認ポイント

この記事の主な内容

1. ODBC接続の仕組みとリンクテーブルの役割
2. 接続前に確認する環境とSQL Server側の準備
3. ODBCドライバーを確認してDSNを作成する
4. AccessからSQL Serverのリンクテーブルを作成する
5. ODBC接続できない・更新できないときの確認点
6. 配布・再リンクの注意点とよくある質問

想定読了時間：約16分～約20分

1. ODBC接続の仕組みとリンクテーブルの役割

AccessからSQL Serverを継続的に利用する場合は、ODBC接続でリンクテーブルを作成する方法が基本です。フォームやレポートはAccessに残し、データはSQL Serverへ保存する構成にできます。まず、接続に関わる用語と、インポートとの違いを整理しましょう。

ODBC・ドライバー・DSNとは

ODBC接続とは、共通のデータベース接続インターフェイスと対応ドライバーを使い、Accessなどのアプリから外部データベースを利用する仕組みです。ODBCはOpen Database Connectivityの略で、アプリとデータベースの間をつなぐために使われます。

SQL Serverへ接続するときは、利用者のPCにMicrosoft ODBC Driver for SQL Serverを用意します。一方、DSNはData Source Nameの略で、ドライバーや接続先などの設定をまとめて呼び出すための名前です。DSNそのものに業務データが保存されるわけではありません。

Access側のリンクテーブルには、外部のテーブルを利用するための情報が保持されます。利用者がフォームで参照・更新すると、接続先のSQL Serverにあるデータが対象になります。ただし、既存フォームやクエリが移行後もそのまま動くかは、別途確認が必要です。

リンクとインポートの違い

リンクは外部データを継続して参照する用途、インポートはデータをAccessへ取り込んで利用する用途に向きます。業務システムの保存先をSQL Serverにするなら、リンクを選びます。どちらを選んだかによって、変更が反映される場所が変わります。

本記事は、SQL Server側にデータベースと対象テーブルが用意されている状態から、接続を設定する手順です。Accessの既存データをSQL Serverへ移す作業全体は、関連記事「AccessからSQL Serverへアップサイジングする方法」で扱います。

2. 接続前に確認する環境とSQL Server側の準備

接続前に、利用するAccessの環境とサーバー側の情報をそろえます。PCの設定だけでは解決できない問題もあるため、SQL Serverの管理者に接続先・権限・通信条件を確認しておくことが大切です。作業には既存Accessのバックアップと検証用コピーを使いましょう。

接続先・認証・必要権限を確認する

まず、次の情報を管理者から受け取ります。特に、サーバー名とデータベース名は別の項目です。似た名前の本番環境と検証環境がある場合は、接続先を取り違えないように、環境名もセットで記録してください。

Windows認証では、許可されたWindowsアカウントでSQL Serverへ接続します。SQL Server認証では、サーバー側に作成されたログイン名とパスワードを使います。後者を使う場合は、サーバーの認証モードやDBユーザーとの対応も、管理者による設定が必要です。

認証は「誰として接続するか」、権限は「何の操作を許可するか」です。ログインに成功しても更新できるとは限りません。参照だけの利用者に追加・更新・削除の権限を与えたり、接続を通すためだけに管理者権限を付けたりしないようにします。

Accessの32bit・64bitを確認する

ODBCドライバーと管理ツールは、接続するAccessのbit数に合わせます。Windowsが64bitでもAccessが32bitの環境はあります。Accessの［ファイル］→［アカウント］→［Accessのバージョン情報］などで、製品情報とbit数を確認してください。

64bit Windowsには、32bit用と64bit用のODBCデータソースアドミニストレーターがあります。どちらも実行ファイル名はodbcad32.exeですが、保存フォルダーが異なります。次の対応を見て、Accessと同じbit数のツールを開きます。

［Windows］＋［R］キーで［ファイル名を指定して実行］を開き、表のパスを入力する方法なら、開くツールを明確にできます。%windir%はWindowsがインストールされたフォルダーを表します。スタートメニューから選ぶ場合も、表示されたbit数を確認しましょう。

TCP/IP・実ポート・証明書を確認する

ネットワーク経由の接続では、SQL Server側のTCP/IP設定と、接続に使う実際のポートを確認します。TCP 1433は既定インスタンスの一般的な例です。名前付きインスタンスでは異なるポートや動的ポートが使われるため、1433を全環境共通の値として入力してはいけません。

また、名前付きインスタンスへの接続では、名前からポートを調べるためにSQL Server Browserが必要になる場合があります。管理者指定の固定ポートへ直接接続する構成なら、Browserを使わない方法もあります。ファイアウォールは必要な接続元とポートに絞って設定します。

暗号化接続で使う証明書も事前に確認します。証明書を発行した機関をPCが信頼しているか、接続時のサーバー名と証明書の名前が一致するかが重要です。IPアドレスへ置き換えると名前が一致しなくなる場合があるため、指定された接続名を使ってください。

3. ODBCドライバーを確認してDSNを作成する

ここでは、Windows認証で接続するユーザーDSNを作成します。ユーザーDSNは現在のWindowsユーザーが使う設定なので、Accessを実際に利用するアカウントで作業してください。SQL Server認証が指定されている場合は、認証方法の選択だけを環境に合わせて変更します。

ドライバーの登録を確認する

Accessと同じbit数のODBC管理ツールを開き、［ドライバー］タブで［ODBC Driver 18 for SQL Server］が登録されているかを確認します。本記事では18系を使用します。表示されない場合は、Microsoftの公式配布ページから環境に対応するインストーラーを入手します。

「利用するドライバーのbit数」と「ダウンロードするインストーラーの種類」は区別してください。Microsoftの案内では、x64 Windowsにはx64インストーラーを使い、このインストーラーが64bitと32bitの両方のドライバーを導入します。インストール後に、Accessと同じbit数の管理ツールで登録を確かめます。

導入権限がないPCでは、管理者にインストールを依頼します。古い解説に出てくる［SQL Server］や［SQL Server Native Client］というドライバー名を、そのまま新規設定の標準にしないようにしましょう。導入条件は公式配布ページで確認してください。

ユーザーDSN・システムDSN・ファイルDSNを選ぶ

DSNには、利用者ごとの設定、PC内の共通設定、ファイルに保存する設定があります。接続を覚える最初の作業では、ユーザーDSNを使うと対象を限定しやすくなります。複数ユーザーへの配布では、運用方法に合わせて選択しましょう。

ファイルDSNを配れば、すべてのPCから自動的に接続できるわけではありません。どの方式でも、接続先への通信、認証、SQL Server側の権限は必要です。また、DSNやAccessファイルに資格情報を安易に保存する運用は避けましょう。

接続先・認証・データベースを設定する

以下は新しいユーザーDSNを作成する操作例です。ドライバーの表示言語や更新状況によって、設定画面の名称や配置が異なる場合があります。画面を進めることだけを目的にせず、入力した値が管理者から受け取った情報と一致するかを確認します。

1. ODBC管理ツールの［ユーザーDSN］タブで［追加］を選びます。
2. ［ODBC Driver 18 for SQL Server］を選び、［完了］をクリックします。
3. データソース名に、用途がわかる名前を入力します。説明用の例は「Access_SQL_Test_32」です。名前に32と付けてもbit数は切り替わりません。
4. サーバー欄に管理者指定の接続先を入力します。名前付きインスタンスは「ホスト名\インスタンス名」、TCPポート指定は「tcp:ホスト名,ポート番号」の形式を使います。
5. Windows統合認証に相当する選択肢を選びます。SQL Server認証の場合は、指定されたログイン名とパスワードを入力します。
6. 既定のデータベースを変更する項目で、利用するDBを指定します。対象DBが一覧に出ない場合は、綴りやDBへのアクセス権を管理者に確認します。

サーバー欄の名前一覧に表示されなくても、指定された接続先を直接入力して接続できる場合があります。自分の判断で似たサーバーを選ばず、正式な名前を使用しましょう。ここで指定するDSN名・ホスト名・DB名は、すべて役割が異なります。

暗号化を設定して接続テストを実行する

ODBC Driver 18以降では、接続の暗号化が既定で有効です。本記事では、暗号化を必須にし、サーバー証明書を検証する構成を基本とします。接続文字列ではEncrypt=yes、TrustServerCertificate=noに相当します。

7. ［Connection Encryption］などの暗号化項目で、Mandatory／必須に相当する設定を確認します。サーバー証明書を信頼して検証を省略する項目は、通常は有効にしません。
8. その他の設定は管理者の指定に合わせ、［完了］まで進みます。確認画面の［データソースのテスト］または［Test Data Source］を実行します。
9. 成功を確認して設定を保存します。失敗した場合は、エラーの全文、時刻、接続先、利用ユーザー、ドライバー名を記録します。パスワードは記録・共有しません。

証明書エラーを消すために暗号化を無効にしたり、TrustServerCertificateを常にyesへ変更したりする手順は、標準の解決策にしません。まず信頼関係と接続名を管理者に確認します。接続テストが通ったら、Access側でリンクテーブルを作成しましょう。

4. AccessからSQL Serverのリンクテーブルを作成する

DSNが作成できたら、Accessの［外部データ］からSQL Serverのテーブルをリンクします。ポイントは、インポートではなくリンクを選ぶことです。既存システムを変更する場合は、バックアップした検証用コピーで、対象テーブルと名前の対応を確認しながら進めます。

外部データからリンクを選ぶ

1. 接続先を追加するAccessデータベースを開きます。接続だけを試す場合は、空の検証用データベースを作成しても構いません。
2. ［外部データ］→［新しいデータソース］→［データベースから］→［SQL Serverから］を選びます。画面によっては［その他のデータソースから］→［ODBCデータベース］を使用します。
3. 外部データの取り込み画面で、［リンクテーブルを作成してソースデータにリンクする］に相当する項目を選び、［OK］をクリックします。
4. データソース選択画面の［コンピューターデータソース］タブから、先ほど作成したユーザーDSNを選びます。ファイルDSNを使用する構成では、［ファイルデータソース］側で対象を選択します。
5. 必要に応じて認証を行い、リンク対象のテーブルまたはビューを選びます。最初は管理者が指定した検証用テーブル1つで確認すると、原因を切り分けやすくなります。

Microsoftの公式手順でも、外部データのリンクを選択してデータソースを指定し、対象のテーブルやビューを選ぶ流れが示されています。実際のメニュー名はAccessの版・更新状況によって異なるため、操作の目的を確認しながら進めてください。

一意キーとテーブル名を確認する

リンク時に［固有レコード識別子の選択］などが表示された場合は、1行を一意に識別できる列を確認します。主キーや適切な一意キーを管理者に確認し、重複のある名称・日付などを推測で選ばないようにしてください。複数列の組み合わせが必要な場合もあります。

この画面で列を選ぶだけで、SQL Serverの元テーブルに主キーや一意制約が作成されるわけではありません。誤った識別子を選ぶと正しく更新できない原因になります。ビューや結合クエリは、一意キーがあっても内容によって更新できない場合があります。

リンク後の名前は、SQL Server側のスキーマ名を含む形になることがあります。例えば「dbo.Product」がAccessでは「dbo_Product」と表示されます。既存フォームが「商品マスタ」を参照しているなら、その対応を整理したうえでリンク名や参照先を調整します。

参照と必要な更新操作を確認する

接続テストの成功は、業務画面まで正しく動くことの証明ではありません。まず意図したDBとテーブルを開いているかを確認し、そのうえで必要な操作をテストします。参照専用の利用者なら、更新を拒否されることも確認すべき動作の一つです。

• 接続先とデータの内容が、指定された検証環境・対象テーブルと一致している。
• 代表的なレコードを参照でき、日本語、日付、数値などが想定どおり表示される。
• 更新が必要な場合は、用意されたテストデータを追加・変更・削除できる。
• 実際に使うフォーム、検索、集計、レポートが正しく動作する。
• Accessを閉じて開き直しても、想定したユーザーと接続先で動作する。

更新結果は必要に応じてSQL Server側でも照合します。実データを使って試したり、確認のためだけに権限を広げたりしないことが重要です。操作後はテストデータを後片付けし、確認した環境と結果を記録してください。

5. ODBC接続できない・更新できないときの確認点

接続の問題は、ドライバー、通信、認証、証明書、更新条件に分けて確認すると整理しやすくなります。エラー番号だけで原因を断定せず、表示された全文と「どの操作で失敗したか」を記録しましょう。ODBCの接続テストと、Accessでの操作結果も分けて確認します。

DSN・ドライバーが見つからない場合

DSNを作ったWindowsユーザーと、Accessを開いているユーザーが同じかを確認します。また、32bitのAccessから64bitのドライバーを使うことはできません。DSNの名前が見えていても、対応するドライバーが読み込めるとは限らない点に注意してください。

特にユーザーDSNは、32bitと64bitの両方の管理ツールに表示される場合があります。表示の有無だけで判断せず、Accessと同じbit数の［ドライバー］タブを確認します。PC入替後は、以前のPCにしか設定が残っていない可能性もあります。

接続先に到達しない・認証に失敗する場合

到達性の問題では、ホスト名、VPNなどのネットワーク接続、SQL Serverサービス、TCP/IP、実ポート、ファイアウォールを順に確認します。Pingが成功してもSQL Serverのポートへ接続できるとは限りません。サーバー側の変更は管理者へ依頼します。

認証エラーでは、Windows認証とSQL Server認証を取り違えていないかを確認します。管理者のPCで接続できても、利用者のアカウントでは権限が不足する場合があります。実際の利用者として再現し、ログインと対象DBへのアクセス権を確認しましょう。

証明書エラーが出る場合

「証明書チェーンが信頼されていない」というエラーでは、発行元の信頼関係や中間証明書などを確認します。「対象プリンシパル名が間違っている」などの名前に関するエラーでは、接続名と証明書の対象名が一致しているかを確認します。

TrustServerCertificate=yesは、サーバー証明書の検証を省略する指定です。原因調査のために管理された検証環境で一時的に使う判断はありますが、その状態を本番の標準設定として残さないようにします。最終的な設定は管理者と決めてください。

読み取りはできるが更新できない場合

まずSQL Server側で必要な更新権限があるかを確認します。次に、リンク対象をAccessが一意に識別できるか、対象のビューやクエリ自体が更新可能かを見直します。フォームだけで失敗する場合は、フォームの更新許可やレコードソースも確認してください。

例えば、集計結果を表示するクエリでは、元データのどの行を更新するかが決まらないため、編集できないことがあります。接続設定を何度も作り直す前に、単純な検証用テーブルと業務画面で結果を比較すると、問題が接続層か画面側かを分けやすくなります。

AccessとSQL Serverの接続設定でお困りの方へ

接続先や権限の確認先がわからない場合や、複数PCで異なる症状が出る場合は、設定と症状を一覧にして調査することが近道です。AccessとSQL Serverの接続設定、既存リンクの切替、フォーム・クエリへの影響についてお気軽にご相談ください。

6. 配布・再リンクの注意点とよくある質問

接続できたAccessファイルを配布するときは、配布先PCのドライバー・認証・通信条件もそろえます。Accessファイルのコピーだけでは、ユーザーDSNの登録や必要な権限が自動で設定されるわけではありません。配布後の動作確認まで含めて運用を決めましょう。

利用者ごとにフロントエンドを配布する

フォームやレポートなどを持つAccess側をフロントエンド、データを保存するSQL Server側をバックエンドとして分けて管理します。利用者ごとにAccessのフロントエンドを配布すれば、画面の更新とデータの管理を分けやすくなります。

配布管理には、Accessの製品情報とbit数、ODBCドライバー、接続先、認証方法、必要なDSN、アプリの版番号を記録しておくと便利です。なお、SQL ServerのパスワードをAccessのリンク情報へ保存すると資格情報を取り出されるリスクがあるため、保存の扱いを慎重に決めます。

接続先変更は再リンク後まで確認する

サーバーの移設やDB名の変更時は、Accessの［外部データ］→［リンクテーブルマネージャー］で、該当する接続情報やリンクを確認します。現行画面ではデータソースの編集・更新・再リンクなどを行えますが、版によって機能と表示が異なります。

1. 変更前のAccessをバックアップし、接続先と対象テーブルを記録します。
2. 新しい接続先に、必要なテーブル・認証・権限が用意されているかを確認します。
3. 検証用コピーで接続情報を修正し、対象テーブルを再リンクします。
4. 参照・必要な更新操作・フォームを試し、新しい接続先を使っていることを確認します。
5. 配布版を更新し、代表的な利用者PCでも確認します。切り戻し時の接続先も記録します。

DSNを変更しただけで、すべての既存リンクが必ず切り替わるとは限りません。リンク作成時にAccessが保持した接続情報も関係するため、再リンク後の接続先を確認します。また、SQL Server側で列を変更した場合は、リンク情報の更新も必要になることがあります。

よくある質問

まとめ

AccessとSQL ServerのODBC接続は、ドライバーの確認、DSN設定、リンクテーブル作成、動作確認の順で進めます。Accessのbit数、認証と権限、暗号化と証明書をそれぞれ確認しておくと、接続時の問題を切り分けやすくなります。

設定が完了したら、必要な参照・更新操作と、配布先PCでの動作まで確認しましょう。データ移行や性能改善も必要な場合は、【054】「Access SQL Server移行時の注意点」や【053】「AccessからSQL Serverへ移行すると速くなるのか」もあわせて確認してください。

接続設定から既存Accessの改修までご相談ください

Accessの保守・改修やSQL Serverへの移行に関する相談を受け付けています。
接続エラーの確認だけでなく、既存フォームやクエリの調整、利用者PCへの配布、接続先変更時の運用まで、課題に合わせて対応範囲を検討します。

相談時は、Accessの製品情報とbit数、ドライバー名、発生する操作、エラー全文、影響するPCの範囲がわかると状況を整理しやすくなります。パスワードなどの秘密情報を含めずにお知らせください。

参考情報

Import or link to data in an SQL Server database

Check ODBC Driver Installation and DSN Configuration in Windows

Connection encryption troubleshooting in the ODBC driver

Configure the Windows Firewall to allow SQL Server access

Manage linked tables

Download ODBC Driver for SQL Server

ODBC Data Source Administrator DSN Options