DSN-Lose verknüpfte Tabellen

Aus CrazyLinux

Anstatt einer (festen, pc-gebundenen) ODBC-Verbindung kann man auch eine 'DSN-Losen' Verbindung erstellen. Eine Beschreibung gibts bei Microsoft. Der Unterschied zu ODBC ist, daß diese Verbindung nur per VBA erstellt werden kann.

In Access ein neues Modul erstellen und den folgenden Code hinterlegen und die Function CreateConnection() ausführen:

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

Function CreateConnection()
    Dim strPasswd As String
    Dim strServer As String
    Dim strUser As String
    Dim strDB As String
    
    strPasswd = "ganzgeheim"
    strUser = "sqluser"
    strServer = "sqlserver"
    strDB = "database"
    
    Rem error
    On Error Resume Next    ' Fehlerbehandlung

    'diese Tabellen sollen verknüpft werden
    Set dummy = AttachDSNLessTable("Tabelle1", "Tabelle1", strServer, strDB, strUser, strPasswd)
    Set dummy = AttachDSNLessTable("Tabelle2", "Tabelle2", strServer, strDB, strUser, strPasswd)
        
End Function

Danke an Jens-Uwe für die Tips!