Access でリンクテーブルのSQL Serverへの DSN レス接続を作成する方法

[アーティクル]
04/13/2023
適用対象:

Access 2003, Access 2002

高度: エキスパートコーディング、相互運用性、およびマルチユーザースキルが必要です。

この記事は、Microsoft Access データベース (.accdb または .mdb) にのみ適用されます。

はじめに

この記事では、データソース名 (DSN) を使用しない Microsoft Access のリンクテーブルの Microsoft SQL Serverへの接続を作成する方法について説明します。これは DSN レス接続とも呼ばれます。

詳細情報

DSN を使用して、Microsoft Access でリンクされたSQL Server テーブルを作成できます。ただし、データベースを別のコンピューターに移動する場合は、そのコンピューターで DSN を再作成する必要があります。この手順は、複数のコンピューターで実行する必要がある場合に問題が発生する可能性があります。このプロシージャが正しく実行されないと、リンクテーブルが DSN を見つけることができない可能性があります。そのため、リンクテーブルがSQL Serverに接続できない場合があります。

SQL Server テーブルへのリンクを作成するが、[データソース] ダイアログボックスで DSN をハードコーディングしない場合は、次のいずれかの方法を使用して、SQL Serverへの DSN レス接続を作成します。

方法 1: CreateTableDef メソッドを使用する

CreateTableDef メソッドを使用すると、リンクテーブルを作成できます。このメソッドを使用するには、新しいモジュールを作成し、次の AttachDSNLessTable 関数を新しいモジュールに追加します。

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'//     stServer: Name of the SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String

For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next

If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

CreateTableDef メソッドパラメーターの詳細については、「 Database.CreateTableDef メソッド (DAO)」を参照してください。

AttachDSNLessTable 関数を呼び出すには、Auto-Exec マクロまたはスタートアップフォーム Form_Open イベントで、次のいずれかのコード例に似たコードを追加します。

Auto-Exec マクロを使用する場合は、AttachDSNLessTable 関数を呼び出し、RunCode アクションから次のようなパラメーターを渡します。
```
    AttachDSNLessTable ("authors", "authors", "(local)", "pubs", "", "")
```

スタートアップフォームを使用する場合は、次のようなコードをForm_Open イベントに追加します。

Private Sub Form_Open(Cancel As Integer)
    If AttachDSNLessTable("authors", "authors", "(local)", "pubs", "", "") Then
        '// All is okay.
    Else
        '// Not okay.
    End If
End Sub

メモ Access データベースに複数のリンクテーブルを追加する場合は、プログラミングロジックを調整する必要があります。

方法 2: DAO を使用します。RegisterDatabase メソッド

DAO。RegisterDatabase メソッドを使用すると、Auto-Exec マクロまたはスタートアップフォームで DSN 接続を作成できます。このメソッドは DSN 接続の要件を削除しませんが、コードで DSN 接続を作成することで問題を解決するのに役立ちます。このメソッドを使用するには、新しいモジュールを作成し、次の CreateDSNConnection 関数を新しいモジュールに追加します。

'//Name     :   CreateDSNConnection
'//Purpose  :   Create a DSN to link tables to SQL Server
'//Parameters
'//     stServer: Name of SQL Server that you are linking to
'//     stDatabase: Name of the SQL Server database that you are linking to
'//     stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'//     stPassword: SQL Server user password
Function CreateDSNConnection(stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
    On Error GoTo CreateDSNConnection_Err

Dim stConnect As String

If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=Yes"
    Else
        stConnect = "Description=myDSN" & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr 
    End If

DBEngine.RegisterDatabase "myDSN", "SQL Server", True, stConnect

'// Add error checking.
    CreateDSNConnection = True
    Exit Function
CreateDSNConnection_Err:

CreateDSNConnection = False
    MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description

End Function

メモ RegisterDatabase メソッドが再度呼び出されると、DSN が更新されます。

CreateDSNConnection 関数を呼び出すには、Auto-Exec マクロまたはスタートアップフォーム Form_Open イベントで、次のいずれかのコード例に似たコードを追加します。

Auto-Exec マクロを使用する場合は、CreateDSNConnection 関数を呼び出し、RunCode アクションから次のようなパラメーターを渡します。
```
    CreateDSNConnection ("(local)", "pubs", "", "")
```