automating text transfer

Posted on 2008-11-14
Last Modified: 2013-11-27

I need to import multiple text files into an ACCESS table. All text files have the same format, but are for dfferent years. I have imported the first file (1961) and now wish to import a further 45 files by using a loop in VBA and replacing the year by x.

The file names are:



I have written a smple loop using the transfer text function, however i get the error message 'Run-time error '3051': The Microsoft Jet database engine cannot open the file C:\Program Files\BarBack 6.0\drinks.mdb.  It is already opened exclusively by another user or you need permission to view its data'. However, the text file is not open, the acces table is not open, and the databse is no read-only. Please help!


Sub Append()

    For x = 61 To 99


        DoCmd.TransferText acImportDelim, , "Mean_wind", "C:\Documents and Settings\19" & x & "010100-19" & x & "123123_all-vars_219836", Yes


    Next x

End Sub

Open in new window

Question by:ferranti
    LVL 38

    Accepted Solution

    "Append" is a key/reserved word in Access/JET/VBA.

    Try renaming Import_Files.

    List of Microsoft Jet 4.0 reserved words

    List of reserved words in Access 2002 and Access 2003
    LVL 65

    Assisted Solution

    the test you did was on this db?

    so if you wrote the code to do 2 imports, one after the other, does that work?

    Just to make sure, the db might be writable but the folder it exists on, is that writable?

    Also its worth adding validation in your code, make sure your file exists

        Dim x As Integer
        Dim sFile As String
        For x = 61 To 99
            sFile = "C:\Documents and Settings\19" & x & "010100-19" & x & "123123_all-vars_219836"
            If Dir$(sFile) <> "" Then
                DoCmd.TransferText acImportDelim, , "Mean_wind", sFile, True
                MsgBox Not "WARNING: The file " & vbCrLf & sFile & vbCrLf & does
            End If
        Next x

    Not sure about append though, I thought it was valid for it to be a procedure name. I could be mistaken though.

    LVL 65

    Expert Comment

    also might be worth doing a compact/repair
    LVL 65

    Expert Comment

    also might be worth doing a compact/repair

    Author Closing Comment

    Hi - there were 2 problems with the code. I needed to change Append to Transfer_File (thanks jimpen), and add .txt to the end of the filename. Rockiroads thanks for the tip on using the message box.
    LVL 38

    Expert Comment

    by:Jim P.
    Glad to be of assistance. May all your days get brighter and brighter.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    746 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

    16 Experts available now in Live!

    Get 1:1 Help Now