I'm having a bit of difficulty creating a DSN-less connection in MS Access. I'm building a database that will be distributed to mutliple users and I don't want to have to set up ODBC connections on each PC. However, when I test the connection string on my PC something odd seems to happen. When I use a trusted connection it works fine. However, when I try to embed my UID and PWD into the string (which I will be embedding a generic UID/PWD on rollout since the end users don't have individual access) it does not connect and I get the message:
General network error. Check your network documentation.
The code I'm using is as follows:
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
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"
'//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
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
AttachDSNLessTable = True
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
I'm calling it through an AutoExec Macro using:
AttachDSNLessTable ("task", "task", "PRODSQL12\WH1", "ImageRight", "ACCESS\myUID", "myPWD")
But it works as:
AttachDSNLessTable ("task", "task", "PRODSQL12\WH1", "ImageRight", "", "")
Does anyone have any thoughts on the cause? Any help on this would be greatly appreciated! VBA is not my forte, so it's probably something simple I'm messing up! Thanks in advance for the help!