Establishing an ODBC Connection from Access 2000 to SQL Server 2000 db

[hoialmen]

Hi All!

I have been asked to set up a series of linked tables between a SQL Server 2000 db and an Access 2000 db app. Now, the person requesting this does not want to set up all the users as users in the SQL db but rather use a universal ODBC connection string to establish a connection to the SQL db everytime a user logs onto the Access app. What I would like to do is pass the connection string into SQL Server, establish the ODBC connection and then have that connection maintained until the user closes out of the Access app.

Now, the problem I seem to be having is not establishing the connection but maintaining it. I have a VBA function in Access that appears to be establishing the connection but, when I try to open one of the linked tables, I am getting an ODBC Connection Failure error for [Network Username]. It seems that the linked tables are trying to establish a "Trusted Connection" using the network username and password rather than the Universal connection that I am trying to establish.

Is there an ODBC guru out there that can shed some light on where the problem may be occurring?

Any help will be greatly appreciated.

Nate

 

[tlbroadbent]

Does the following link help you resolve the problem?

FIX: SQL Server ODBC Driver Ignores Authentication Setting

http://support.microsoft.com/default.aspx?scid=KB;en-us;q279526

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.