IMPORT OBJECTS WITH VBCODE

Introduction.

Normally, Tables, Queries, or other objects from another database can be imported manually by selecting the Import option from Get External Data option from the File menu. But this can be achieved through VBA 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.

Importing All Tables.

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

Public Function Table Import() 
'----------------------------------------------------------------- 
'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 

Importing All Queries.

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 

Importing All Forms.

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 

Exporting All Forms

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.