Importing ODBC Data

[plawrence]

I have a routine that imports ODBC data using:

DoCmd.TransferDatabase acImport, "ODBC Database", !ConnectString, acTable, !SourceTable, !LocalTableName, False

The problem is sometimes I need to manually select File>Get External Data>Import, select ODBC from "Files of Type", select the Data Source, then hit "cancel" when the "Import Object" box pops up, THEN I can run the routine. I have to do this for each database I'm importing from or I'll get an ODBC error. It's like I have to go through all the motions manually without actually importing anything before my routine will do it.

Any ideas of what's happening?

Patrick

 

[jiqjaq]

You need to specify all the connection parameters for the database such as password, server etc. If any needed parameter is missing, Access can't figure out how to make the connection and it prompts you for the info.

 

[plawrence]

That's what I don't understand. There is no password and everything is set up correctly in the user DSN. What's bothering me is why do I have to step through the process manually before the code will work. I always get the same error: 3151 ODBC--connection to <my db> failed. When I go back and do it manually, the connection is fine.

Is there a way to check the connection first or open a connection with VBA?

Patrick