Change ODBC connection in VBA

[stringzz]

Hello all,

When I open an Access 2002 database and log into an Oracle table, I am connected to all objects that have that same ODBC link. So, for example, if I log into a connection called "PROD", the Access Database holds that connection for all tables that I open up. This is a good thing since you don't have to log into each table.

Is there a way to change the connection to another database, for example "DEV". I don't want to relink all tables, I just want to be able to clear the ODBC connection Access is holding, and log into another database.

Is this possible?

Thanks so much!

 

[DanR]

Good question!

I'm looking for a solution to this problem too. So that an Access 'front end' database can easily be redirected to point to a different ODBC Oracle back end.

I've been trying to do it by editing the ADOX table properties like this:

tabCurrent.Properties("Jet OLEDB:Link Provider String") = "DSN=" & strODBCLinkName & ";DBQ=" & strDataSourceName

Without success...

Has anyone out there solved this problem??

Dan

 

[DanR]

Happy to say I've found my own solution. I'm posting it in case others have the same problem.

It's easy to do using the DoCmd.TransferDatabase method.

Quoting the online help in Access 2003:

TransferDatabase Action
You can use the TransferDatabase action to import or export data between the current Microsoft Access database (.mdb) or Microsoft Access project (.adp) and another database. For Access databases you can also link a table to the current Access database from another database. With a linked table, you have access to the table's data while the table itself remains in the other database.

In my case I can simply delete a linked table (ie the link, not the underlying table), then recreate the link using DoCmd.TransferDatabase, inserting the parameters for the new source table location.

I hope this helps someone...