Run Time 3011 error when running DoCmd.TransferDatabase command in Access

I am attempting to import a table from an ODBC source in Access and import a table into my current Access Database.

I am running the following code;
DoCmd.TransferDatabase transfertype:=acImport, databasetype:="ODBC Database", _
databasename:="ODBC;DSN=OSASData", objecttype:=acTable, Source:="admin.SOTD", _
destination:="admin_SOTD", structureonly:=False

And I am receiving the error attached.

Thanks in advance for any help!
Dan
error.GIF
LVL 1
filtrationproductsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
The message says that Access can't find the database.  Is that the name of the database you are importing from?  If so, change the TransferDatabase type to be Access.  Otherwise check the DSN.
0
filtrationproductsAuthor Commented:
The database in the message is the database I am importing into (where the code is running from). And I am importing from a ODBC data source.

It appears that it is making the connection to the ODBC fine because I get a license prompt like I do when I do it manually.

Thanks!
Dan
0
filtrationproductsAuthor Commented:
I did not change the code but I tried running it again today and I am receiving a different error. I attached a screen shot.
Capture.JPG
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PatHartmanCommented:
Link the table manually and copy the connection string from the MSysObjects table to use in your code.
0
filtrationproductsAuthor Commented:
I did that and modified the code to this;

DoCmd.TransferDatabase transfertype:=acImport, databasetype:="ODBC Database", _
databasename:="ODBC;DSN=OSASData;SERVER=192.168.0.15;PORT=2001;RETRIES=5;POOLREMAIN=10;PREFETCH=20;USER=user;DESCRIPTION=OSAS Data;DATABASE=OSAS Data;SSL=N;READONLY=N;CONNECTIONCACHE=N", objecttype:=acTable, Source:="admin.SOTD", _
destination:="admin_SOTD", structureonly:=False

But I am still receiving the error that says it cannot find "admin_SOTD1". It seems like it is connecting to the ODBC source fine, but it is giving the error when trying to copy the table in the current (open) database.  

Is it possible I have the destination portion of the code wrong?

Dan
0
PatHartmanCommented:
I don't see admin_SOTD1 in the string.  Check the DSN.
0
filtrationproductsAuthor Commented:
If I change destination:="admin_SOTD" to destination:="TEST" it will return the same error but instead of admin_SOTD1 it will say TEST ( no 1 after it because no table named TEST exists in the database). So the table it cannot find is the destination table not the external ODBC table I am importing from.

If I create table named TEST it will return the error again but it will say TEST1. (just like it does when you manually import a table that already exists. It creates the same table with a number at the end to indicate that the table already exists and it is creating a new table.

Also, I don't think the DSN  is the problem because I am getting the license connection prompt when I run the code (verifying that I am connecting properly to the ODBC database). I attached an image of this prompt which is typical, meaning I always receive this message when exporting manually.

License prompt
I hope this makes sense.
Dan
0
PatHartmanCommented:
I hate to abandon this method but if you need to move on, you can try linking to the table and then running a make table query.

Also, keep in mind that importing tables repeatedly bloats the database and you will need to compact regularly so linking is usually best in any case, especially to a RDBMS.
0
filtrationproductsAuthor Commented:
I would prefer to link but unfortunately the BBj service this database is running on does not allow live connections, I can only import when no one is logged into it.  

I guess I might just have to stick to manual imports. I will do a bit more searching on the web for a solution and will post back if I find anything.
0
filtrationproductsAuthor Commented:
I figured out an easier way to accomplish this. Instead of using the DoCmd.TransferDatabase function I ran and saved an import (Under External Data on the ribbon/Saved Imports) and executed it via VBA. And I ran the DoCmd.DeleteObject function to delete the table prior to importing so it doesn't save it with the "1" at the of the table name.

DoCmd.DeleteObject acTable, "admin_SOTD"
DoCmd.RunSavedImportExport "admin_SOTD"

Dan
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.