MS Access to SQL Server

[1DMF]

Hi,

Not sure if I should ask this here on in the MS Access forum as it relates to both, but here goes...

We have MS Access as a front end to our SQL server databases.

When I make changes, I then re-link the tables via the link table manager, and select the DSN I created on my system.

When distributed to all employees this works fine, obviously they are logged into the domain and their accounts have access to SQL.

However, remote users cannot run the DB, although they connect to the server via VPN and can see the SQL server, they don't actually log onto the domain, and the DB trys to use "Trusted Connection" and fails when passing their home PC's user details - which is how you'd expect it work!

What I want to do instead is link the MS Access front end to the tables via a UserName and Password, so then the VPN users can run the DB.

However, I've done this, I removed the use trusted connection when linking the table and supplied the 'sa' userid and password (for this test i used sa), the tables link ok - great.

I copy the db to my machine at home, fire up the VPN, when I open the DB, I get an error, saying connection failed for userid 'null', the used trusted connection is ticked, and a box asking for a userid and password pops up.

Why won't the DB hold the username and password I linked the tables with, it shouldn't ask for anything - how can i make it connect to the linked tables in SQL with a "hardcoded" userid and password, rather than using windows authentication, which fails for those not logged onto the domain.

thanks 1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[SQLWilts]

You are using linked tables straight from Access? I have got around this in the past using ADO connections to the tables.
It was a while ago, so I can't remember exactly what it entailed - have a google around and see if that helps

 

[1DMF]

yes I'm using ODBC, if I change the links to the tables from ODBC > ADO , will it affect the VBA coding?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[Catadmin]

What type of ODBC are you using? User, System or File?

Do you specify the user & pwd in the actual ODBC or just on the SQL Server?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[1DMF]

this is the DSN I created

[ODBC]
DRIVER=SQL Server
UID=[b]userid[/b]
PWD=[b]password[/b]
Network=DBMSSOCN
DATABASE=[b]database-name[/b]
APP=Microsoft Office 2003
SERVER=[b]I.P. of Server[/b]
Description=[b]database description[/b]
Trusted_Connection=No

bold = Names have been changed to protect the innocent

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.