We currently have an Access database that is split, with both the front and back ends currently in Access. We are going to be moving the back-end to SQL Server using the Upsizing Wizard. We have other databases with Access front-ends and SQL back-ends. Typically, we create and place ODBC connections on the users' computers so their front-end databases are able to link to the SQL server tables.
My question is this. Is there another way link to the data, maybe with some connection information stored right in the Access front-end database?
The reason I ask is because the user group is a bit larger than others, and I would prefer not to have to go around and create ODBC connection on 30 different users' desktops. I am looking for a more efficient way of doing this.
On a related note:
I have worked on Access databases that have pass-through queries to SQL tables, where the connection information is stored right in the query properties. So what are the advantages/disadvantages to connecting to the SQL tables from an Access front-end using linked tables versus pass-through queries? Is it a ridiculous idea to link to all the SQL tables using pass-through queries instead of linked tables?