Avoiding Login prompt for ODBC Connection to SQL Server DB

[AccessUser22]

Hi,

We are currently linking an MS Access 2003 front end database to an SQL server 2005 backend. When using SQL Server security and creating a dummy login on the SQL Server for all users that use the front end system to connect to the backend db, I'm getting a login box in the Access database when I attempt to connect to the data. I'm wondering if there is a way around this box, to store the username and password of the connection to the server? I dont want to have the users have to initiate the connection.

Maybe some sort of code I can add so that on application startup, the ODBC connection is automatically initialized? Any advice is appreciated.

 

[PHV]

You have the option to save the password when you create a linked table.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AccessUser22]

Where does it present you with the option to save the password? I did not see it while I was setting up the ODBC connection or when I was connecting to the linked table. Please let me know. Thanks.

 

[PHV]

Create a new linked table and tick the "save password" checkbox at the bottom

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AccessUser22]

There is no "save password" option on my Access 2003 when I create a new linked table. I did however find out that if I use a file data source ODBC instead of a system one, it doesn't prompt me for the password after it's initially set up. So I think I'll go that route for now at least.

Not sure why I can't see the save password option you are talking about. Are you on a different version of Access? I'm on 2003.

 

[PHV]

me too

 

[rodrunner79]

Hi,

I too have a similar problem as the OP. I did click on that option to save password but when I exit the Access dB, it ask for the password again. I'm thinking that I can put a code in my startup form using the On Load event procedure to call a routine that will logon the the SQL database.

How do I do this?

-=True wisdom comes from knowing you know nothing.=-

 

[jrbarnett]

Are your PC, user account and SQL Server box in an active directory domain? if you use domain authentication for your ODBC, you won't need to store a login ID/password combination.
You do need to configure all appropriate user accounts for domain authentication within SQL Server but its worth considering.

John

 

[djtwilight]

The "Save Password" check box is found after you select the ODBC source, enter the password for the first time, and it lists the tables, allowing you to select which tables you want linked (Access 2003). On the right side, there is an OK, Cancel, Select All, Deselect All and below that, a "Save Password" checkbox.

I, too, had trouble finding it, breezing past that screen multiple times. before I finally slowed down enough to find it.

-daniel