Design question. Should I use ODBC or is there something better?

[DaveMac]

I have an access app that uses 2 linked tables that are linked via standard ODBC. I use a lot of linked servers in MS SQL and can create those dynamically on the fly. I would like to be able to create the most robust connection type possible given my need.

The use would look like this:

20 users each with their own copy of the Access DB locally, I will email it to them.
They are all in our company AD and physically located throughout the country.
The source server is in 1 location.
The app is currently linked to the 2 tables and there is a command button that pulls the relevant data down to their local DB from MS SQL.
MS SQL security is read only to all AD users.
I would be open to creating a generic user and hard coding the user pass for a connection
The users will download about once a week

Thanks for any advice and sample code

 

[dhookom]

I would use pass-through queries to connect to the source server. You can use SQL authentication or hard-code a readonly login and password into the connection property of the p-t. If you want to be able to identify the connected users, it makes more sense to use NT Authenitication.

Duane
Hook'D on Access
MS Access MVP