I have an Access 2010 front end application with a SQL Server back end with an ODBC connection. The front end links to the back end tables and there are a few passthrough qeuries for inserts, updates, and deletes. Each user ( around 5 in all) has a copy of the front end on their machine. I am looking for the best way to configure the ODBC connection. I am still trying to fully understand ODBC's.
My goal is to have a connection that allows me to make modification to the front end and post it without having to mess with the connection again. Currently, I am using a file DSN with Windows authentication. With the current connection, I had to reconfigure each machine for the front end. I thought I could avoid this with the file DSN. I don't know what will happen when I post a new version of the front end yet.
What is the best connection configuration? Machine data source, file data source with Windows authentication, file data source with SQL authentication?
Thanks for your time.