?
Solved

Import text file using DoCmd.TransferText sometimes has 3011 error

Posted on 2009-12-17
3
Medium Priority
?
812 Views
Last Modified: 2013-11-28
Hi. I am trying to write VBA code to import data from a text file into an already existing table.

The VBA code is:
DoCmd.TransferText acImportDelim, "SPEC_091216", "RESULTS", Forms![Form1]![txt_RESULTS].Value

The import specification is saved as "SPEC_091216"
The already existing table is named "RESULTS"
The text file is in the same directory as the MSAccess dB and I tell Access the name of the file by opening Form1 and putting the text filename (e.g. Data.txt) into a text box.

When I execute this code, I get a 3011 run time error: "Jet database engine could not find the object 'Data.txt'. Make sure the object exists and that you spell its name and the path name correctly."

Ironically, this DOES work, if I do the following: File > Get External Data > Import > Browse for the file Data.txt > Select Advanced button > Select Specs button > Cancel out of everything.

I don't know why "looking" at the saved import specifications makes the import work. How can I change my VBA code to prevent the 3011 error. thanks.

0
Comment
Question by:davidmanjhu
  • 2
3 Comments
 
LVL 14

Assisted Solution

by:Bill Ross
Bill Ross earned 800 total points
ID: 26076475
Hi David,

Have you tried to put the full path name into the text box Forms![Form1]![txt_RESULTS].Value

Bill


0
 

Accepted Solution

by:
davidmanjhu earned 0 total points
ID: 26077640
Bill,
Entering the full path (e.g. C:\PROJECT\Data.txt) into the text box does work, but I didn't want to burden the user with that since the production dB may end up having a very long path.

I changed the code to this instead and it seems to work:

DoCmd.TransferText acImportDelim, "SPEC_091216", "RESULTS", Application.CurrentProject.Path & "\" & Forms![Form1]![txt_RESULTS].Value

thanks
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 26079442
Glad to be of help.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

807 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