Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-11-05
12
Medium Priority
?
727 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
12 Comments
 
LVL 39

Expert Comment

by:PatHartman
ID: 40424688
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
ID: 40426028
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
ID: 40426108
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:PatHartman
ID: 40426186
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
ID: 40426624
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
 
LVL 39

Expert Comment

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

Author Comment

by:filtrationproducts
ID: 40426918
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 39

Expert Comment

by:PatHartman
ID: 40427024
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
ID: 40427039
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
ID: 40450194
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 49

Expert Comment

by:Martin Liss
ID: 40499966
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

705 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