Multiple Table Import/Export

[proximity]

Hi,

I have an access 2002 database with 15 tables in it. From time to time, I need to export these tables into another, identical database, which I use for experimenting on! And then I need to do the reverse - export the tables back into the source database. In each case, any existing tables will be over-written with the copied ones.

I realise this can be done with a shedload of DoCmd.TransferDatabase acImport or DoCmd.TransferDatabase acExport things. Indeed, there are many similar postings as this, but nothing quite fits the bill . . .

There must be a nice easy way of coding a command button that will do it all for me. Any ideas greatly, as ever, appreciated!

--
Steven

 

[CautionMP]

proximity,
Here is a general structure that can be used to Import or Export (this sample is Export) by enumerating the AllTables collection.

Sub ExportAllTableObjects()
Dim objTable As AccessObject
For Each objTable In CurrentData.AllTables
  DoCmd.TransferDatabase acExport, , "C:\TestDatabase.mdb", acTable, objTable.Name, objTable.Name
Next objTable
Set objTable = Nothing
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[JerryKlmns]

Be aware that CurrentData.AllTables returns all tables including system tables.

With this query you get linked (Type=6) and local (Type=1) tables, but not system tables.

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "MSys*") AND ((MSysObjects.Type)=1 Or (MSysObjects.Type)=6));

Sub ExportAllTableObjects()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
   .ActiveConnection = CurrentProject.Connection
   .CursrorLocation = adUseServer
   .CursorType = adOpenForwardOnly
   .LockType = adReadOnly
   .Source  = "SELECT [Name] FROM MSysObjects " & _
              "WHERE ([Name] Not Like "MSys*") AND (Type In (1, 6));"
   .Open
    While Not .EOF
      DoCmd.TransferDatabase acExport, , "C:\TestDatabase.mdb", acTable, .Fields(0), .Fields(0)
    Wend
    .Close
End With
Set rst = Nothing

End Sub