Access SQL Sever

【全手順を公開】AccessとSQL Serverの接続方法

更新日:

【仕組み(知識)】
SQL Serverからテーブルリンクを張る時に、AccessとSQL Serverの間の架け橋となるデータソース・ODBC(Open Database Connectivity )(SQL Serverのホスト名や認証方法をセットするプロファイルのようなもの)を手段として利用します。

「Access」 ⇔ ODBC ⇔ 「SQL Server」

【手順】
・ODBCデータソースの作成

「コントロールパネル」→「管理ツール」→「ODBC データ ソース (64 ビット)」を起動します。
※ODBC Data Sources (32-bit)でも問題ないはずですが、念のためPCに合わせて64bitで進めましょう。

「ユーザーDSN」タブで「追加」をクリックします。

SQL Server を選択し「完了」をクリック

必要な項目を入力し「次へ」をクリック
– 名前:プロファイルの名前(任意)
– 説明:空欄可能
– サーバー:SQL Serverのホスト名を入力

SQL Server への接続方法を選んで「次へ」をクリック
※Windows 認証(ActiveDirectory等のドメイン認証)で接続する場合は「ネットワークへのログインIDで、Windows NTの認証メカニズムを使う」にチェック。
※SQL Server 認証(SQL Server に登録してあるユーザーアカウント)で接続する場合は「ユーザが入力するSQL Server 用ログインIDをパスワードを使う」にチェック。
SQL Server 認証の方が確実です。

「既定のデータベースを以下のものに変更する」で接続先データベースを選んで「次へ」をクリック。
※このプルダウンメニューでSQL Server 内で作成したデータベースが見えているという事は認証が通った証拠です。

そのままで「完了」をクリック。

「OK」でも問題ありませんが、テストをしてみます。

「テストは無事に完了しました。」と出れば問題ありません。「OK」で閉じます。

ユーザーデータソースに追加されました。
「OK」で閉じます。

以上でODBCデータソースの作成は完了です。次にAccessを開いてリンクテーブルを作成します。

「外部データ」→「新しいデータソース」→「データベースから」→「SQL Serverから」へ進みます。

「リンクテーブルを作成してソースデータにリンクする」を選択して「OK」

「コンピューターデータソース」タブで先ほど作成したODBCプロファイルを選択して「OK」をクリック。

認証が聞かれるので、ログインIDとパスワードを入れ「OK」をクリック。

SQL Server内で作成してあるテーブルが見えてます。
接続するテーブルを選択して「OK」をクリック。
※この画像では筆者の環境のテーブルは隠してあります。

選択したテーブルでリンクするフィールドを選び「OK」をクリック。
基本的には全部で良いでしょう。

ご覧のようにSQL Server内テーブルがリンクされました。
おめでとう!

【重要情報】
ODBCはリンクを張る時に認証をするために使われる機能のため、リンクが貼られた後に、例えば、フォームとして使うAccessファイルは、ODBC設定を作っていない他のPCで開く事ができます。

■VBAでSQL Server認証を設定しておく

SQL ServerのテーブルをAccess内にリンクできましたが、リンクしたテーブルを開こうとすると、SQL Server認証のPWが毎回聞かれます。(Windows認証にする手法もありますが)Access内にVBAで認証情報をセットしておく事もできます。
Access起動時自動的に実行される「AutoExec」とうい名前のマクロを作成し、下記コード(参考)を仕込んでおきましょう。

—————————————————————————–
Function マクロ名()

Dim con As Object, rec As Object

Dim Dbs As DAO.Database
Dim Tdf As DAO.TableDef
Dim strODBC As String

Set Dbs = CurrentDb
strODBC = “ODBC;DRIVER=SQL Server;UID=sa;PWD=****;LANGUAGE=日本語;DATABASE=****;SERVER=SQL01;”

‘ODBCLink の再接続
For Each Tdf In Dbs.TableDefs
If Tdf.Connect Like “ODBC;*” Then
Debug.Print “Set Tdf Connect:” & Tdf.Name & “:” & strODBC
Tdf.Connect = strODBC
Tdf.RefreshLink
Tdf.Fields.Refresh
End If
DoEvents
Next Tdf
Dbs.TableDefs.Refresh

End Function
—————————————————————————–

コードの中央部分にあるSQL Server認証に関する記述の説明です。
「UID」→SQL Server のユーザーアカウントのユーザーIDを記述します。
「PWD」→ユーザーIDのパスワードを記述します。
「DATABASE」→Accessが接続するSQL Server のデータベース名を記述します。
「SERVER」→SQL Server がインストールされているマシンのホスト名を記述します。

<<今回の環境>>
Access 2016
SQL Server 2017 Standard Edition
Windows 10 Pro 64bit

-Access, SQL Sever

Translate »

Copyright© 解決方法あった! , 2021 All Rights Reserved Powered by STINGER.