Attach DSN Less Connection to Link MS Access to SQL Server

link2

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

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh
This entry was posted in All, MS Access, SQL Server, VBA Code MS Access. Bookmark the permalink.