How to transfer *.dbf (many files)

[Piotr]

Hi,
I cant solve my problem, I wish to transer all .dbf files from
directory, into one access table ?
All files have same structure.

[Piotr]

I have written VBA to transfer but I have many files, now i need to do
SELECT INTO, but how can I make VBA to know the tables name ?
So i would make some loop statement.

[osmethod]

Piotr wrote:

I have written VBA to transfer but I have many files, now i need to do
SELECT INTO, but how can I make VBA to know the tables name ?
So i would make some loop statement. From the help file, the following shows how to get a list of files...

Function DbfDir()
Dim MyFile As String

MyFile = Dir("C:\WINDOWS\*.INI")
Do While MyFile <> "" ' Start the loop.
Debug.Print MyFile
' Call Dir again without arguments to return the next *.INI file in the
' same directory.
MyFile = Dir
Loop

End Function
osmethod

[pietlinden]

follow osmethod's example, but where he has the line
Debug.Print MyFile

that's where you want to change this thing

Function DbfDir()
Dim MyFile As String
Dim rsDAO As DAO.Recordset
set rsDAO =
DBEngine(0)(0).OpenRecordset("MyTAble",dbopentable ,dbappendonly)

MyFile = Dir("C:\WINDOWS\*.INI")
Do While MyFile <> "" ' Start the loop.
rsDAO.AddNew
rsDAO.Fields("FileName") = MyFile
rsDAO.Update '--save the record
' Call Dir again without arguments to return the next *.INI file in the

' same directory.
MyFile = Dir
Loop

rsDAO.Close
set rsDAO=Nothing

End Function

[pietlinden]

umm... sorry, fingers working faster than my brain...
instead of using AddNew and all that, you can probably just use
something like this:

dim strSQL as string
strSQL = "INSERT INTO xlsToAccess SELECT xlsData.* FROM " & strFile
'which you get from Dir()
DBEngine(0)(0).Execute strSQL, dbFailOnError

and then you just wrap the loop around that whole mess.