Access でリンクされたテーブルのSQL SERVER DSN レス接続を作成する方法

注意

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

Advanced: 専門的なコーディング、相互運用性、およびマルチユーザースキルが必要です。

この記事は、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

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", "", "")
    
  • スタートアップ フォームを使用する場合は、次のようなコードをイベントに追加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" SQL Serverを使用して、Access データベースにリンクされたテーブルを作成済みと見なします。