Close an ODBC connection

[devSoft144]

I am importing data to Access 2002 from an Oracle database. My vba code is:

Sub importTbl()
DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=myDSN;UID=myUID;PWD=myPW", acTable, "SourceTbl", "DestTbl", False
' the above transfer is succesful.
' The next transfer produces an error
' Note that I need a different UID
DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=myDSN;UID=myUID2;PWD=myPW", acTable, "SourceTbl2", "DestTbl2", False
End Sub

The error is: 3011 cannot find object 'DestTbl2'

I can see the second transfer in the clipboard.

If I place the two transferDatabase commands in two different subs, run one sub, quit and restart Access then I can run the second transferDatabase. Evidently the ODBC connection from the first transfer is interfering with the ODBC connection for the second transfer. Is there a method to log-out/quit/close the first ODBC connection without quiting Access?

Thanks in advance for your help.

 

[PHV]

What about this ?
DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=myDSN;UID=myUID;PWD=myPW", acTable, "SourceTbl", "DestTbl", False
DoEvents
DoCmd.TransferDatabase acImport, "ODBC", "ODBC;DSN=myDSN;UID=myUID2;PWD=myPW", acTable, "SourceTbl2", "DestTbl2", False


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[devSoft144]

Thank you for the suggestion. I tried DoEvents and the same 3011 error occurred at the second TransferDatabase command. Evidently the second transfer collected the Oracle data. The problem seems to be creating the Access table. For some reason VBA cannot see the table collection. I researched DoEvents and agree that relinquishing control to the operating system should close the ODBC connection.

Thanks again

 

[jsteph]

devsoft,
Many years ago resorted to making raw calls directly to the odbc api to get around this and other odbc issues in Access where I wanted more flexibility, but that is a lot of work for just this issue.

Access is doing some sort of caching and I know this is frustrating.

If it's only two different users, I'd make two dsns and be done with it.

If you're not married to the docmd.transferdatabase method to get this data, using other methods with OLEDB might be more flexible to do what you want.
--Jim