The Batch Import Taboo

  • Thread starter Thread starter Artistyc
  • Start date Start date
A

Artistyc

I'm new at access and I've been searching for a way to
batch import. No one seems to talk about how to batch
import, everyone seems to be doing things one file at a
time. Can it be done in access?

I have 2,712 ".dbf" files in subdirectory
D:/Shipping/2002/

All have differant names but identical feild information.
"CLASSNO", "ITEMNO", "TITLE", "LISTDATE", and "LISTNO"
all feilds are 250 text feilds with no special formating.

In the past i've been told to export them as text and
merge them with a text editor, or to copy and past, there
are just to many to do by hand.
 
Combine these 2 tips to solve your problem.

1. 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
=================================================================
2. How to Add Directory File Names to an Access Table:

Create a table named tblDirectory with 2 fields:
FileName (Text 250)
FileDate (Date/Time)

Call the code below by pressing Ctrl-G to open the debug window and type:
GetFiles("c:\windows\")

Paste this code into a regular module:

Sub GetFiles(strPath As String)
Dim rs As Recordset
Dim strFile As String, strDate As Date

'clear out existing data
CurrentDb.Execute "Delete * From tblDirectory", dbFailOnError

'open a recordset
Set rs = CurrentDb.OpenRecordset("tblDirectory", dbOpenDynaset)

'get the first filename
strFile = Dir(strPath, vbNormal)
'Loop through the balance of files
Do
'check to see if you have a filename
If strFile = "" Then
GoTo ExitHere
End If
strDate = FileDateTime(strPath & strFile)
rs.AddNew
'to save the full path using strPath & strFile
'save only the filename
rs!FileName = strFile
rs!FileDate = strDate
rs.Update

'try for next filename
strFile = Dir()
Loop

ExitHere:
Set rs = Nothing
MsgBox ("Directory list is complete.")
End Sub
 
Back
Top