Solved

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

Posted on 2014-11-05
12
648 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 36

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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 36

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 36

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 36

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 46

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
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.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

830 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