Solved

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

Posted on 2014-11-05
12
590 Views
Last Modified: 2014-12-15
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
0
Comment
Question by:filtrationproducts
  • 6
  • 4
12 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
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
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Link the table manually and copy the connection string from the MSysObjects table to use in your code.
0
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I don't see admin_SOTD1 in the string.  Check the DSN.
0
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
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
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
 
LVL 1

Author Comment

by:filtrationproducts
Comment Utility
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
 
LVL 1

Accepted Solution

by:
filtrationproducts earned 0 total points
Comment Utility
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
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now