[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Linking tables to SQL Server with DSN-less connections

Posted on 2005-05-05
4
Medium Priority
?
1,229 Views
Last Modified: 2008-06-15
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.
0
Comment
Question by:paulmcneil
3 Comments
 
LVL 17

Assisted Solution

by:Arji
Arji earned 1600 total points
ID: 13935573
try:

 "DRIVER={SQL Server};Server='Servername';Database='dbname'....etc.

here is a good site:
http://www.connectionstrings.com/
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 400 total points
ID: 13935673
Here's another one with pages specifically for DSN-less connections to a wide variety of databases:

http://www.able-consulting.com/
0
 
LVL 12

Expert Comment

by:Preece
ID: 13938674
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
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question