[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

automating text transfer

Posted on 2008-11-14
6
Medium Priority
?
348 Views
Last Modified: 2013-11-27
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
Comment
Question by:ferranti
  • 3
  • 2
6 Comments
 
LVL 38

Accepted Solution

by:
Jim P. earned 1200 total points
ID: 22962014
"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
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 300 total points
ID: 22962662
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 22962665
also might be worth doing a compact/repair
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 65

Expert Comment

by:rockiroads
ID: 22962672
also might be worth doing a compact/repair
0
 

Author Closing Comment

by:ferranti
ID: 31516837
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
 
LVL 38

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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