You could create a link to the other database dynamically using the createtabledef method too.
dim db as database, tdef as tabledef
set db = currentdb()
Set Tdef = db.CreateTableDef(“Table2009″)
Tdef.Connect = “;Database=C:\history.mdb”
Tdef.SourceTableName = “Table2009″
Later you could simply delete the linked table object after using a query to append the records into your new table.
If you want to select a list of tables from the foreign table from a combo box, you could build the combo box’s row source from the list of tables. It would be set up as a row source type of value list. Then you could put this in the combo box’s got focus event.
Dim db As Database, x As Long, TableList As String
Set db = OpenDatabase(“c:\history.mdb”)
For x = 0 To db.TableDefs.Count – 1
If Left(db.TableDefs(x).Name, 4) <> “msys” Then
TableList = TableList & db.TableDefs(x).Name & “;”
TableList = Left(TableList, Len(TableList) – 1) ‘Remove the last ;
Me![Combo0].RowSource = TableList
On the combo box’s after update event, you could use the same code as above; but use the combo box’s value to supply the table to link
Dim db As Database, tdef As TableDef
Set db = CurrentDb()
Set tdef = db.CreateTableDef(“Table2009″)
tdef.Connect = “;Database=C:\history.mdb”
tdef.SourceTableName = Me![Combo0]