Using Wildcard Characters in TransferDatabase 1

[Lightning]

What is the correct syntax for using wildcard characters in the TransferDatabase method?

I need to import data into a summary db from up to 100 similar db's. Each of these db's is named "RegionalOfficexxxx", where xxxx is a four-character alphabetical code.

I have tried using

DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Survey\RegionalOffice????.mdb", acTable, "tblOffice", "tblImportOffice", False

and

DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Survey\RegionalOffice*.mdb", acTable, "tblOffice", "tblImportOffice", False

but each of these gives me the error message "Not a Valid Filename".

Can somebody help me with the correct syntax please?

TIA
Lightning

 

[PaulF]

you can do this but the code you are using will create additional files with the name tblImportOffice (i.e. tblImportOffice1, tblImportOffice2, etc), If you are trying to import this to the table (append) you need to use additional code for that

Private Sub cmdImportFiles_Click()
Dim varItem, intCount As Integer
varItem = Dir("C:\Survey\", vbDirectory)
'This Code does the Importing by Looping through the C:\Survey\ Directory
Do While varItem <> &quot;&quot;
If InStr(varItem, &quot;RegionalOffice&quot > 0 Then
intCount = intCount + 1
DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, &quot;C:\Survey\&quot; & varItem, acTable, &quot;tblOffice&quot;, &quot;tblImportOffice&quot;, False
End If
varItem = Dir
Loop
' This code does the Appending of the Tables, and then Deletes them
For intCount = 1 To intCount
DoCmd.SetWarnings False
DoCmd.RunSQL (&quot;INSERT INTO tblImportOffice ( Field1 ) SELECT tblImportOffice&quot; & intCount & &quot;.Field1 FROM tblImportOffice&quot; & intCount)
DoCmd.DeleteObject acTable, &quot;tblImportOffice&quot; & intCount
DoCmd.SetWarnings True
Next intCount
MsgBox &quot;Done&quot;
End Sub

PaulF

 

[Lightning]

Thanks Paul

Yes, I know that this will create additional files. It is done this way to enable certain checks on the data, and to halt the append if the data already exists. the data from the 100+ returns will not all be available at the one time, so some checks to avoid duplication are mandatory.

Thanks for the suggested code. I hadn't even thought about using the Instr() function. That should solve my problem very neatly, and that's worth a star!!

Thanks
Lightning