automating text transfer

Hi,

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:

1961010100-1961123123_all-vars_219836
1962010100-1962123123_all-vars_219836
1963010100-1963123123_all-vars_219836

etc.

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!

Thanks



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

ferrantiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim P.Commented:
"Append" is a key/reserved word in Access/JET/VBA.

Try renaming Import_Files.

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/kb/321266

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/kb/286335/en-us
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rockiroadsCommented:
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
        Else
            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.


0
rockiroadsCommented:
also might be worth doing a compact/repair
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

rockiroadsCommented:
also might be worth doing a compact/repair
0
ferrantiAuthor Commented:
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.
0
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.