IMPORT OBJECTS WITH VISUAL BASIC CODE

Normally, Tables, Queries or other objects from another database can be imported manually by selecting Import from Get External Data option from File menu. But this can be achieved through VB Code too and this question, HOW TO? is raised in Microsoft Access User's Forums and I thought it is useful to those who look for this solution. Hence, I present the Code here for importing Tables, Queries and Forms separately.

The next method imports all Tables from a Source database into the active database except the Microsoft Access System Tables.

Public Function TableImport()
'-----------------------------------------------------------------
'Function to Import Microsoft Access Tables from another Database
'Author : a.p.r. pillai
'Date : 02/12/2006
'-----------------------------------------------------------------
Dim wrkSpace As Workspace, db As Database, tbldef
Dim strFile As String
Dim ObjFilter As String
'if conflict with existing object name then ignore
' and import next object
On Error Resume Next
Set wrkSpace = DBEngine.Workspaces(0)
'Check for Table Definitions in the Source database
'and import all of them except System Tables.
Set db = wrkSpace.OpenDatabase("c:\tmp\Sourcedb.mdb")
For Each tbldef In db.TableDefs
strFile = tbldef.Name
'Filter out Microsoft Access System Tables.
ObjFilter = left(strFile, 4)
If ObjFilter   "MSys" Then
    DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\tmp\Sourcedb.mdb", acTable, strFile, strFile, False
End If
Next
End Function

Next Function Imports all the Queries from the Source database into the current database.

Public Function QueryImport()
'------------------------------------------------------------------
'Function to Import Microsoft Access Queries from another Database
'Author : a.p.r. pillai
'Date : 02/12/2006
'------------------------------------------------------------------
Dim wrkSpace As Workspace, db As Database, QryDef
Dim strFile As String
'if conflict with existing object name then ignore
'and import next object
On Error Resume Next
Set wrkSpace = DBEngine.Workspaces(0)
'Check for Query Definitions in the Source database
'and import all of them.
Set db = wrkSpace.OpenDatabase("c:\tmp\Sourcedb.mdb")
For Each QryDef In db.QueryDefs
 strFile = QryDef.Name
 DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\tmp\Sourcedb.mdb", acQuery, strFile, strFile, False
Next
End Function 

The ImportForms() Function Imports all the Forms from an external Microsoft Access database into the current Database.

Public Function ImportForms()
'----------------------------------------------------------------
'Function to Import Microsoft Access Forms from another Database
'Author : a.p.r. pillai
'Date : 02/12/2006
'----------------------------------------------------------------
Dim FRM As Variant, wrkSpace As Workspace
Dim db As Database, strForm As String
Dim ctr As Container
'if conflict with existing object name then ignore
'and import next object
On Error Resume Next
Set wrkSpace = DBEngine.Workspaces(0)
Set db = wrkSpace.OpenDatabase("c:\tmp\Sourcedb.mdb")
Set ctr = db.Containers("Forms")
For Each FRM In ctr.Documents
strForm = FRM.Name
 DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\tmp\Sourcedb.mdb", acForm, strForm, strForm, False
Next
End Function 

The ExportForms() Function Exports all the Forms into an external Microsoft Access database.

Public Function ExportForms()
'----------------------------------------------------------------
'Function to Export Microsoft Access Forms into another Database
'Author : a.p.r. pillai
'Date : 02/12/2006
'----------------------------------------------------------------
Dim cdb As Database
Dim ctr As Container, doc, strFile As String
'if conflict with existing object name then ignore
'and import next object
On Error Resume Next
'Export all Forms from the current database into
'the Target database
Set cdb = CurrentDb
Set ctr = cdb.Containers("Forms")
For Each doc In ctr.Documents
strFile = doc.Name
  DoCmd.TransferDatabase acExport, "Microsoft Access", "c:\tmp\Targetdb.mdb", acForm, strFile, strFile, False
Next
End Function 

With little modifications to these Codes, they can be used for transferring objects between two external databases.

Next >> Create Excel File from Access.