• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 360
  • Last Modified:

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

0
ferranti
Asked:
ferranti
  • 3
  • 2
2 Solutions
 
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
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now