security on linked tables

[wil]

I have an Access 2003 DB which uses a series SQL linked tables for its data source. The connection between the DB and SQL is via a view-only user on SQL Server (2005), making the tables view only in Access.

When I started, users had to enter the password for the viewer connection when they opened the DB and that login was good for the whole session.

Now users are being asked for the PW every time a linked table is hit - this can be 6-10 times on start up, then at least twice every time a form is opened/updated.

Any idea what governs the security between the two linked DBs?

[TheShabz]

well, when linking the tables, there's an option for "save password". when that is checked, the user won't be asked for the password again. once that is set up, then all you need to do is maintain the security privileges of the user on the Access DB and not worry about the connection to the SQL (SQL Server I take it) DB.

[wil]

Right you are, sir. I wish it were always that simple.
Thanks!!

[pkstormy]

Here's some code also which might make things easier. It basically will automatically create the ODBC DSN on the user's computer when they open the application (providing they have permissions to do this).

This will prevent problems via manually creating each ODBC on the users computer should you accidently name an ODBC DSN slightly different on 1 users computer versus another.