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

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

この記事は、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", "", "")
    
  • スタートアップ フォームを使用する場合は、次のようなコードを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リンク テーブルを既に作成していることを前提としています。