Solved

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

Posted on 2014-11-05
12
706 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 38

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 38

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 38

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 38

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 48

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Course of the Month10 days, 7 hours left to enroll

628 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