Import to Access

[P Dooley]

Is it possible to import multiple text files during an
import to Access?

If not, does anyone have any suggestion on how to merge
multiple text files together to avoid having to do
multiple imports?


Thanks

 

[Joe Fallon]

How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub

 

[Guest]

Joe,
This seems to be exactly what I need but am really new to programming and can't get it to work. I have created a command button and have pasted the code into the on click event. The only parts in the code I have changed are:
ChDir (C:\MyFiles") to point to where the files to import are stored.
I have changed the AccessTableName to the name of the table I want the data to go to.
From the same line I have changed the "c:\MyFiles\" to the directory of where the file are and done the same on the KIll line.
Have I missed something? Maybe the ImportScecName as I don't know what this is referring to.

Thanks for your help,
Mark

 

[Joe Fallon]

You have to build an ImportSpec for text files manually (once) before you
can use code like this.
Step through the import wizard all the way to the final screen and do NOT
hit Finish.
Instead click Advanced and Save As...
Give the spec a name and write it down.

What you wrote goes in the code as ImportSpecName.

Also, check out TransferText in Help.

Kill will delete your files.
Do NOT use it unless you have backups!
(Or that is the behavior you expect.)
--
Joe Fallon
Access MVP

Joe,
This seems to be exactly what I need but am really new to programming and

can't get it to work. I have created a command button and have pasted the
code into the on click event. The only parts in the code I have changed are:

ChDir (C:\MyFiles") to point to where the files to import are stored.
I have changed the AccessTableName to the name of the table I want the data to go to.
From the same line I have changed the "c:\MyFiles\" to the directory of

where the file are and done the same on the KIll line.