Import single table from multiple DB's using VBA

[tigersden]

A standalone system creates a new MDB log file daily.
I would like to import 1 table from each of these individual MDB's into a new MDB containing a single table for the month. The table names are the same.
Thanks in advance for your help

 

[dhookom]

Check the code in thread181-1572741. Your sql view would select from your table rather than msysobjects. You could use the query as the source for an append query.

Duane
Hook'D on Access
MS Access MVP

 

[CautionMP]

tigersden,
Assuming:
[ol][li]All the source database files are in the same directory.[/li][li]The table you want from the source files is called [tt]Table1[/tt][/li][li]This code resides in the database you want all the data moved into.[/li][li]All the data will be imported into [tt]Table2[/tt][/li][/ol]

Sub ScanDirectoryAndImport(Optional ByVal Path As String)
  'Define the Source and Destination table names here
  Const strcSource As String = "[i]Table1[/i]"
  Const strcDestination As String = "[i]Table2[/i]"
  'See if a path was provided, if not assume current db directory
  Dim strPath As String, strFile As String
  If Path = "" Then
    strPath = CurrentProject.Path
  Else
    strPath = Path
  End If
  'Make sure the syntax is correct
  If Right$(strPath, 1) <> "\" Then strPath = strPath & "\"
  
  strFile = Dir$(strPath & "*.mdb")
  'DoCmd.SetWarnings False
  Do While strFile <> ""
    DoCmd.TransferDatabase acImport, , strPath & strFile, _
         acTable, strcSource, strcDestination
    strFile = Dir$
  Loop
  'DoCmd.SetWarnings True
End Sub

If the table name changes from database file to database file you will need to use something similar to what Duane directed you to to find the table name.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[tigersden]

Thank you so much