Category: SQL Server

This code is generally available all over the tinternet nonetheless I list it here for my own personal use. I use an autoexec macro to trigger the code on open

With a Run Code action to trigger the AttachDSNLessTable…
So the code in the macro might look something like this
AttachDSNLessTable(“Table01Invoices”,”Table01Invoices”,”Server01″,”AccountingDatabase”,””,””)

Alternatively you could run it from the immediate window of the VBA module section.
Ctrl + G to get the immediate window up then create the function with the required parameters placing a question mark in front of the function eg
?AttachDSNLessTable(“Table01Invoices”,”Table01Invoices”,”Server01″,”AccountingDatabase”,””,””)

Pressing return will result in True result and when you go to the tables section Table01Invoices or your table should appear. Note if you have the tables section open of the database window then you will need to refresh.

Honestly works a treat and you can totally revolutionise processes if you are allowed to use the ease of front end design of something like MS Access with the scalability and power of SQL Server.

Needless to say vendors tend to be universally unwilling to give me details of their(/our!) backends.

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName:Name of the table
'//     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
        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