Access 97 Problem / SQL Server 2000

[Eradic8or]

I have a series of linked tables in MS Access 97. I am using a local DSN to connect to the SQL Server.
The problem is that although I have about 40 tables, I want to restrict access in the front end (MS Access)
How can I prompt a user for thier SQL Server credentials everytime they open the database.
I have tried linking the tables with the "Save password" option unchecked but that makes no difference.
When I run a pass through query to obtain the current user, it always comes back as me.
Any suggestions?

Eradic8or.

 

[tlbroadbent]

Does the pass-through query have a saved password? If you run a query that connects to SQL Server using a saved password, Access will reuse the same connection all query and table opens. It will not thereafter prompt the user.

Are all of the table connection strings cleared of the password? Again, if a table is opened that has a saved password, the connection is established and other tables will not require a password to open.

Are you using a NT authentication or SQL authentication?
If you use NT authentication Access will not prompt for a password. Make sure you use SQL authentication.

You should use Access security to restrict user permissions on the client side. Terry L. Broadbent - DBA
Computing Links:

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

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.