In Access 2002, how can I transfer databases from multiple oracles sources?

I have some simple VBA to import tables from multiple Oracle databases. I have one ODBC entry, and I connect to different databases using different credentials.

My transfer database code is simple:

DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DNS=source;UID=user;PWD=pass;", acTable, "SomeRemoteTable", "MyLocalTable", True

Th开发者_如何学编程at code works, but when I run the same command with a different user/pass immediatley after this command I get this error:

Run-time error '3011': The Microsoft Jet database engine could not find the object 'SomeRemoteTable'. Make sure the object exists and that you spell its name and the path name correctly.

Here's the catch:

The DNS, user credentials, and table names are correct. If I open access and comment either line out, then either transfer will run successfully. However, once one command runs, the other will not run until Access has been closed and reopened.

I'm guessing that there must be a way to close the first connection before proceeding to the next. Does anyone have any ideas on what I can try?

If the databases are owned by you how about setting up a dblink on one of them to the other ? You will then only need 1 connection.