Importing Multiple Text files in Acc 97

[mike67]

I searched the posts so far on this subject and haven't been able to pull together all the pieces I need. I have a directory of about 50 txt files I want to import into an existing Access table. There is one extra field in the table (the last field in table, in doing manual importing I was simply changing the default value of this field to reflect the filename) in which I would like to place the filename on each record.

It seems the TransferText method would be the easiest approach but do not know how to include the filename as part of the equation. Any suggestions?

 

[KenReay]

Hi

in pseudo code

For each file get file name (dir Function)
Transfertext
setwarnings off
runsql to set file name where file name is blank
set warnings on
next file name

Do you need more precise code than that? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[mike67]

Ken,

Thanks for the tip. I'm just learning some of the basics of programming in Access and will give it a shot.

What is the best way to loop through all files in a directory? I know the number of files at any given time so I could change the variable with each run, but is there an easy way of looping through all files in a directory until there are no more?

Thanks again for your help!

Mike

 

[KenReay]

Hi

Basically

Dim FileName As String
FileName = Dir(*.txt)
Do Until FileNmae = ""
...do your thing here
dir
Loop

see dir() in help for various switches )ie look for file, look for directories (folders) etc

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK