Originally Posted by
Gicu
Barry,
Just semantics, but you don't create a new "driver", you create/update a new "connection" using an "installed" driver. looks like your existing set up is using the legacy "SQL Server" driver that basically comes with all the recent versions of Windows since XP, so if it ain't broke I agree, you don't need to fix it. In Access you can inspect the "connection" info for any linked ODBC table in the msysObjects table. To view that go to File\Options\Current Database\Navigaion Options and check the Show System Objects box. Now open the msysObjects table and check the Connect field. You should see in there the old connection details used by the original developer. You can create a file DSN , plug in your new details (server name, db name, user), use it to relink the table with the Linked Table Manager and you are done.
Here is a typical file DSM for SQL server (just open Notepad and save it with the DSN extension):
[ODBC]
DRIVER=SQL Server
UID=YourUserName
PWD=YourPassword
DATABASE=YourDBName
SERVER=YourServerName
In your case because you use Windows authentication for the SQL server it should be something like this:
[ODBC]
DRIVER=SQL Server
Trusted_Connection=Yes
DATABASE=WORM
APP=Microsoft Office 2010
SERVER=YOUR_NEW_SERVER
Put the new server name in, save it somewhere then use the linked table manager, check the prompt for new location and when prompted, browse for the new DSN file and it should work (if all the permissions for all users got transferred to the new server). Now just deploy the updated front-end that you just relinked to all your users and they should all be OK.
Cheers,
Vlad