Import .dbf files to MS ACCESS


W

Wayne

I am trying to bulk import .dbf files into access. The numbers change every
time a transaction is input, but the first two positions stay constant. For
instance:
QB00331, QB00451.
Is there a way to check a directory C:\FSMFILES\ for all files that start
with QB and insert them into a table. It can import all into one table
because the format is the same for all the .dbf files.
Thanks in advance
 
Ad

Advertisements

D

Douglas J. Steele

To check the directory, use the Dir function:

Dim strFolder As String
Dim strFile As String

strFolder = "C:\FSMFILES\"
strFile = Dir(strFolder & "QB*.dbf")
Do Until Len(strFile) = 0

' At this point, strFile will contain the name of the file (no path).
' You can then import strFolder & strFile.

strFile = Dir()
Loop
 
W

Wayne

Douglas,
Thanks for the input but when I execute this nothing happens. I do not even
receive an error message.
I have used the below and it changes the names of my QB to the first name of
the .dbf directory. Just the QB's are imported though.

Dim strPath As String
Dim strTableName As String
Dim strFileName As String
Dim dbs As Database


Set dbs = CurrentDb
strPath = "C:\FSMFILES\"
strTableName = Dir(strPath)
strFileName = Dir(strPath & "QB*.dbf") .
Do While strFileName <> "" ' Start the loop.
DoCmd.TransferDatabase acImport, "dBase IV", strPath, acTable,
strFileName, strTableName

strFileName = Dir ' Get next entry.
Loop
 
D

Douglas J. Steele

Given you didn't show what you did to the snippet I gave before running it,
I can't comment on why it did nothing. (You do realize that it wasn't a
complete sample?)

What you've got below is essentially what I suggested (using
Len(strFileName) = 0 is more efficient than strFileName <> "")

Because you're setting

strTableName = Dir(strPath)

the name of your table is essentially undeterminable (I don't believe you
can be sure of which file Dir will return)

If you care what the table is going to be named, set it to a known value,
rather than using the Dir function.
 
Ad

Advertisements

W

Wayne

Douglas,
I am just starting out and am a novice but I am picking up the items really
quick. Thank you for you input it has been very helpful.
Regards,
Wayne
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top