DoCmd.TransferDatabase Automation

[plawrence]

I'm having a problem while using docmd.transferdatabase. Unless I first go to File>Get External Data>Import and "access" (just selecting the source and hitting cancel--not actually importing anything) all my data sources, I get an "ODBC Call Failed--Unable to connect" error. It's like I have to go through the steps myself manually and then the code will work. Any suggestions on completely automating the file imports?

The line from my code is:
DoCmd.TransferDatabase acImport, "ODBC Database", !ConnectString, acTable, !SourceTable, !LocalTableName, False

A typical connectstring is "ODBC;DSN=Caustic;DBQ=S:\RBMWare\RBMsuite\CustData\Caustic Hardwood.rbm;SERVER=s00ffs02DATABASE="

Thanks,

Patrick

 

[vbajock]

Is there some reason you can't attach the tables to the database ?

 

[plawrence]

If you're talking about linking, the ODBC driver won't allow me to access any data if anyone else is in the database. I've been importing the data either early in the morning or late in the evening or been asking everyone to stop working for a bit. These databases are in a vibration analysis program--not that easy to work with.

 

[vbajock]

If it is working manually but not automatically, I would suspect your connection string. The one you posted has an error in it, SERVER=s00ffs02DATABASE= has no ; separating the parameters.