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

注意

Office 365 用リソース は、 エンタープライズ向け Microsoft 365 アプリに名前変更されています。 この変更の詳細については、 このブログの投稿を参照してください。

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

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

はじめに

この記事では、microsoft Access でデータソース名 (DSN) を使用しないリンクテーブルに対して 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

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

  • 自動 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 メソッドを使用すると、自動 Exec マクロまたはスタートアップフォームで DSN 接続を作成できます。 このメソッドは DSN 接続の要件を削除しませんが、コードで DSN 接続を作成することによって問題を解決するのに役立ちます。 このメソッドを使用するには、新しいモジュールを作成し、次の作成した Snconnection 関数を新しいモジュールに追加します。

'//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 が更新されます。

作成した Snconnection 関数を呼び出すには、次のコード例のいずれかに似たコードを、自動実行マクロまたはスタートアップフォーム Form_Open イベントに追加します。

  • 自動 Exec マクロを使用する場合は、作成した Snconnection 関数を呼び出し、次のようなパラメーターを "RunCode/プロシージャの実行" アクションから渡します。

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

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

注意

この方法では、DSN 名として "myDSN" を使用して、Access データベースに SQL Server のリンクテーブルが既に作成されていることを前提としています。