asked on

Linking tables to SQL Server with DSN-less connections

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:

Connection failed:
SQLState: ‘01500’
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver] Invalid connection string attribute
Connection failed:
SQL State: ‘01000’
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Connection failed:
SQLState: ‘08001’
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Sql Server does not exist or access denied.

SOLUTION

Arji

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Steve Bink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Preece

Maybe you need to enclose this string in quotes:

"ODBC;DRIVER=SQL Server;SERVER= Servername;UID=UserID;password=Password;DATABASE=dbname"

Hope this helps!
Preece