I am wanting to DSNLess link multiple azure sql tables to my access database. This is the code I have
Code:
Public Function AttachDSNLessTable(table)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String
'Alter these variables to change any settings
Dim stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String
Dim stUsername As String, stPassword As String, S As String, pkfield As String
localTable = Replace(table, ".", "")
'What table name will be in access
stLocalTableName = "Azure_" & localTable
'What table name is in SQL Server
stRemoteTableName = table
pkfield = "ID"
'Server name
stServer = "server"
'Database
stDatabase = "database"
'User
stUsername = "user"
'Password
stPassword = "password"
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
This works fine for my inhouse SQL Server - but i get the error
Code:
AttachDSNLessTable encountered an unexpected error: ODBC--call failed
What am I missing here?