Would highly suggest DNS-Less. If you search several of us have posted articles on this.
The other question is to use the OS ODBC driver or to install each workstation with SQL Server Native Client 11.0. The latter is my choice.
The SQL Server Native Client 11.0 is used for all MS demos for AZURE, Outlook, .NET and other applications. It is free. From my point of view, it will dimish any issues with older ODBC drivers on a workstation.
DSN-LESS: I create a local Access Table that includes all of the tables that should be connected. Mine has 2 columns of true/false checkbox, one column for users, one for developers.
A function destroys all linked tables. Then goes down the list of table names and creates the DSN-LESS linked table. Every time a new SQL table (or view) is created, the name should be listed in this local table as a table to be connected.
Personal preference: I use Linked tables (and views) with this odbc.
From there, the DAO is my favorite method since I have been using it since its introduction. Nothing against ADO.
My preference is to prototype in Access Local Tables then use the SQL Server Migration Tool for Acces (free). See other postings on that too.
SQL Server Security has two flavors. One is to have a username/password the other is to integrate the Network Security user names.
My enterprise uses Citrix as a secured sandbox. The users can only see a window with the Access applicaiton running full screen.
Because of that, I have one User / Password for the connection. Everyone uses that. It prevents me from depending on an IT staff to refresh expired passwords and such. IT staffs make excuses not to like Access, from my experience it usually comes down to the additional time to maintain it. Password management is one of those maintence things for them.
That is not to say that tracking the individual users is not being done. It is still easy to determine the user ID for a new record or updated record.
I might suggest starting with a single UN /PW because the code to relink is very, very easy to change. It is in a loop anyway. A single line of code.
You also want to have the option to back up production into a TEST DB.
On SQL Server, mine is MyDBName and MyDBNameT (for test).
The same code that reads the local table to create the DSN-LESS tables has a option to connect to Production or Test DB.
Be prepared to move larger queries over to SQL Server as Views. They can really speed things up. From Access just refrence those views as a linked table from a query. e.g. Select SuperComplexCustomerView where CustomerID = 24429234
The Linked Table ODBC will actually thake the "where" criteria and merge that into a SQL Server SP on the SQL execution plan side so long as SQL syntax is used. This means avoiding IIF and custom Access functions we commonly use in Access.
example of Local Linking Table
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=50155&d=1378907710
You may also be interested in how to distribute a new Access Release to your clients.
http://www.access-programmers.co.uk/forums/showthread.php?t=269966&highlight=linked
Example of linking code:
http://www.access-programmers.co.uk/forums/showthread.php?t=269947&highlight=linked