Hi guys,

Using Access 2003 on XP.

First time caller, long time listener.  I am not a programmer per se,
and all my training in coding in the past year or so has come from the
wealth of info in these newsgroups, but I've finally hit a brick wall
re importing tables from one Access db to another.

I have set up an import routine that loops through db's in a specified
folder, importing selected tables from each to a consolidated db.  The
thing is, the code I have below does work to some extent, but only
does so once I have "manually" imported any table from any db in the
folder I am calling on (ie - going through the File -> External Data
-> Import method).  It seems to spark it somehow or finds the right
place to look ....but if I dont do this, it appears that the file I am
searching for "cant be found" as such (even though it can still count
how many files are in the folder with the first part of my code), but
once it hits the TransferDatabase part of the code, it just stops.

From other similar posts, the only thing I can glean is that I may
have to set up a Workspace or have an OpenDatabase command first so
the db can be "found"?  Strange one that is a bit beyond my
guesstimation as a "non-coder".

Would appreciate any ideas ...the relevant code is posted below...

Private Sub btn_Import_Click()

Dim filename As String
Dim myFileCount As Integer

On Error GoTo Err_Section

filename = Dir("C:\Data\*.mdb")  'want all the Access files in this
dir

'Count the number of Access files in this directory
With Application.FileSearch
.Lookin = "C:\Data"
.SearchSubFolders = False
.filename = "*.mdb"
.Execute
myFileCount = .FoundFiles.Count

' If no files then give msg and get out
If myFileCount = 0 Then
MsgBox "No databases to import", vbInformation, MsgBoxTitle
Exit Sub

'Else away we go, display number of files we are importing in MsgBox
Else
MsgBox "You are about to import " & myFileCount & " databases into the
consolidated Dbase ", vbInformation
End If
End With

' Begin data transfer of Access files
Do While Len(filename) > 0  'start of loop

' Show on user form what file is currently being imported
Me.txt_Status = "Importing tables from " & filename '
Me.Repaint

'''''' Routine stops running here

DoCmd.TransferDatabase acImport, "Microsoft Access", filename, _
acTable, "tbl_EQUIPMENT_H", "tmp_tbl_EQUIPMENT_H"

DoCmd.TransferDatabase acImport, "Microsoft Access", filename, _
acTable, "tbl_EQUIPMENT_D", "tmp_tbl_EQUIPMENT_D"

....Other stuff..... appending data queries, dropping tmp tables
etc...

filename = Dir   'go to next Access file in loop
Loop

End Sub

Cheers,
 Dave G
 Melb, Aust