467,864 Members | 1,936 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,864 developers. It's quick & easy.

Batch Import text files to Access

Hi, I need some help. The routine below runs when a command button is clicked. A prompt appears to enter the directory path to the folder containing delimited text files to be imported into an Access 97 table. The test folder contains 9 files. The problem is that the 1st file is imported 9 times into the table. How do I correct the routine so it loops properly and imports each file? Thanks in advance.


Gary


Private Sub Command0_Click()
Dim InputDir, ImportFile As String, tblName As String
Dim InputMsg, Name As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
InputDir = InputBox(InputMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.txt")
Name = ((InputDir) & ("\") & (ImportFile))
Do While Len(ImportFile) > 0
' Use the import file name without its extension as the table
' name.
tblName = "Expenses"

DoCmd.TransferText [acImportDelim], "Batch", "ExpBatch", Name
ImportFile = Dir
Loop
End Sub
Jun 23 '06 #1
  • viewed: 10845
Share:
1 Reply
PEB
Expert 1GB
PEB
Hi,
When your file is inserted in your database it'll be good to change its extension from txt to tx1 for exemple! So this file won't apear in your file list to insert in the database

To do this you have to use:

Name OldName As NewName :)
Sep 3 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by Moosebumps | last post: by
7 posts views Thread by erniedude | last post: by
4 posts views Thread by ScoobyDoo | last post: by
12 posts views Thread by Selva Chinnasamy | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.