Linking tables to SQL Server with DSN-less connections
Posted on 2005-05-05
I am trying to link tables to an Access 2002 mdb using a DNS-less connect string. I am a user on a network that uses Active Directory and I’m connected via VPN. Note: I have been able to successfully connect using a saved DSN. However, I want to use code to link tables with “DSN-less” connections. My vba code is:
DoCmd.TransferDatabase acLink, “ODBC Database”, ODBC;DRIVER=SQL Server;SERVER= Servername;UID=UserID;password=Password;DATABASE=dbname, acTable, sourcetablename, targetablename, , True
When I run the code, first I am prompted for Login ID and Password. Then I get the error msg:
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver] Invalid connection string attribute
SQL State: ‘01000’
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Sql Server does not exist or access denied.