【Access】VBAでSqlserverテーブルリンク作成

やりたい事は、

したいという事になります。

マイクロソフト様のサンプルコード

ネットを探し回って使えそうなVBAコードが無いかと検索しましたが、結局はマイクロソフト様の解説ページのVBAコードがそのまま使う事ができることがわかりました。

'//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

フォームボタンにVBA設定

このリンクボタンに、自分の環境では、

AttachDSNLessTable ("リンクテーブル名", "Sqlserverのテーブル名", "ODBC接続サーバー名", "データベース名", "接続ID", "接続PW")

となります。

Private Sub table_link_Click()

    If AttachDSNLessTable("order", "dbo.order", "TEST\SQLEXPRESS", "DATABASE", "ID", "PW") Then
        '// All is okay.
        MsgBox "リンク終了しました。"
    Else
        '// Not okay.
        MsgBox "リンク失敗しました。"
    End If

End Sub

これで「テーブルリンク」ボタンを押すことで、SqlserverのテーブルがAccessにテーブルリンクされました。

注意する点

テーブルリンクした後、データ更新ができない（読取専用）になっている場合があります。

Sqlserverのテーブルに主キー設定がないと読取専用になってしまいますので御注意ください。

また、ADOやDAOなどのVBAに関する参照設定が必要な場合がありますのでエラー等が出る場合は確認しても良いかと考えます。

今回は、SqlserverのテーブルをAccessにリンクテーブルするVBAを取り上げましたが、この機能を使えば、Accessを終了する時に、テーブルリンクを解除し、起動した時だけ、テーブルリンクを復元してセキュリティ効果もアップすると思います。