Auto Configure ODBC Connection

[DerickD]

Hi All,

Thanks for taking the time.....

One of the most frustrating things is once you have made a new Access Database, is having to go round to all the computers that want to use it and set-up the correct ODBC connection, through the control panel. This is a real pain if someone needs to use it at a remote site! Explaining t to them and all others.

Is there any way that VBA can create and configure a new ODBC connection automatically.

If this is possible, I think that it could take peoples work to a new level of professionalism, when you can click on an "Auto Configure" button and the connection is made automaticly.

Thanks allot for reading this far and I hope you can help me.

DerickD

 

[FancyPrairie]

Yes, instead of setting up a User DSN or System DSN, define your connection using the File DSN. Then make sure the file resides on a server that everyone has access to.

 

[kupz]

DerickD,

Have you tried using a reigistry file? The below example is for SQL Server but am sure you can modify for use with MS Access. Let me know if you are able to adapt this to MS Access. Make sure to save as .reg. Hope this helps.

REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
"Database Name"="SQL Server"


[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\SDGMEMSQL]
"Database"="Database Name"
"Driver"="sqlsrv32.dll"
"Server"="Server Name"
"Trusted_Connection"="Yes"
"Description"="My Database"