Import text file using DoCmd.TransferText sometimes has 3011 error

Posted on 2009-12-17
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.

Question by:davidmanjhu
    LVL 14

    Assisted Solution

    by:Bill Ross
    Hi David,

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



    Accepted Solution

    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

    LVL 14

    Expert Comment

    by:Bill Ross
    Glad to be of help.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Suggested Solutions

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    755 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

    26 Experts available now in Live!

    Get 1:1 Help Now