Need to remotely create an ODBC connection to a SQl server.

Question

I have an Access 2007 database with a table in it that is linked to a SQL server. I need to roll this version of the database out to approximately 10 people in different states. In order to do that, they need an ODBC connection to the SQL server installed on their machines. I am looking for a way to do this remotely. Either through VBA in the database itself or perhaps a Batch file linked to their shortcut....I am open to ideas....

Answer 1

If you're naming the SQL Server instance and database exactly the same way on each machine, you should be able to create a DSN on your testbed machine, use it to link tables, then convert it to a DSN-less connection and distribute that.

Answer 2

system ODBC is configured via the registry -- see HKLM\SOFTWARE\ODBC\ODBC.INI. You will need a key to match the DSN name and an entry in ODBC Data Sources for the key.