How to configure odbc-connection from vba-code

[torunsu]

Hi!



I have discovered a seriously annoying problem with Access 2003. I have linked some tables from SQL Server 2005, and if I change a table or if I set the linked tables to go to an odbc connection with another name, I need to link all the tables again. ALL of them, manually. Refresh does not help.

So - my question is:

How do I (if I can) change the odbc connection to go to another database (without changing the name of the connection) from vba code?

Hope you understand.

[rpeare]

If I'm understanding your problem correctly, the easiest solution is set up the ODBC connection to your database the SAME on every computer. You're not doing that, there's a difference in spelling or something that is making your database not recognize the connection from computer to computer. Is there a reason basically the same connection is named differently on different computers?

You can refresh links with VBA (to accomodate the new fields etc) but you should really make a 'global' name for the ODBC connection.

See this thread for an example of refreshing links:

https://www.accessforums.net/import-...les-16473.html

[torunsu]

The reason I need to know this, is that I need to make a button that switches to another database. I want to switch between the test and the production database. But if I make it switch to another odbc connection, the application crash. Therefore, I need to change the connection itself. Kind of...

[rpeare]

http://www.mrexcel.com/forum/showthread.php?t=294648