Sample code to import all database objects in Access (298174)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: To import all the objects from another database into the current database, follow these steps:

1. Start Access, and then open the database where you want to import objects.
   
   This may be a new blank database.
2. In the Database window, click Modules, and then click New.
3. On the Tools menu, click References. Make sure that Microsoft DAO 3.6 Object Library or later is selected in the list of references. Also make sure that any reference to Microsoft ActiveX Data Objects is not selected. Click OK.
4. Type or paste the following code in the module window:

   Option Compare Database
   
   Option Explicit
   
   Public Function ImportDb(strPath As String) As Boolean
   
   On Error Resume Next
   
   Dim db As Database 'Database to import
   Dim td As TableDef 'Tabledefs in db
   Dim strTDef As String 'Name of table or query to import
   Dim qd As QueryDef 'Querydefs in db
   Dim doc As Document 'Documents in db
   Dim strCntName As String 'Document container name
   Dim x As Integer 'For looping
   Dim cntContainer As Container 'Containers in db
   Dim strDocName As String 'Name of document
   Dim intConst As Integer
   Dim cdb As Database 'Current Database
   Dim rel As Relation 'Relation to copy
   Dim nrel As Relation 'Relation to create
   Dim strRName As String 'Copied relation's name
   Dim strTName As String 'Relation Table name
   Dim strFTName As String 'Relation Foreign Table name
   Dim varAtt As Variant 'Attributes of relation
   Dim fld As Field 'Field(s) in relation to copy
   Dim strFName As String 'Name of field to append
   Dim strFFName As String 'Foreign name of field to append
   
   'Open database which contains objects to import.
   
   Set db = DBEngine.Workspaces(0).OpenDatabase(strPath, True)
   
   
   'Import tables from specified Access database.
   
   For Each td In db.TableDefs
   
   strTDef = td.Name
   
   If Left(strTDef, 4) <> "MSys" Then
   
   DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, _
   strTDef, strTDef, False
   
   End If
   
   Next
   
   
   'Import queries.
   
   For Each qd In db.QueryDefs
   
   strTDef = qd.Name
   
   DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acQuery, _
   strTDef, strTDef, False
   
   Next
   
   
   'Copy relationships to current database.
   
   Set cdb = CurrentDb
   
   For Each rel In db.Relations
   
   With rel
   
   'Get properties of relation to copy.
   
   strRName = .Name
   strTName = .Table
   strFTName = .ForeignTable
   varAtt = .Attributes
   
   'Create relation in current db with same properties.
   
   Set nrel = cdb.CreateRelation(strRName, strTName, strFTName, varAtt)
   
   For Each fld In .Fields
   
   strFName = fld.Name
   strFFName = fld.ForeignName
   nrel.Fields.Append nrel.CreateField(strFName)
   nrel.Fields(strFName).ForeignName = strFFName
   
   Next
   
   cdb.Relations.Append nrel
   
   End With
   
   Next
   
   
   'Loop through containers and import all documents.
   
   For x = 1 To 4
   
   Select Case x
   
   Case 1
   strCntName = "Forms"
   intConst = acForm
   
   Case 2
   strCntName = "Reports"
   intConst = acReport
   
   Case 3
   strCntName = "Scripts"
   intConst = acMacro
   
   Case 4
   strCntName = "Modules"
   intConst = acModule
   
   End Select
   
   Set cntContainer = db.Containers(strCntName)
   
   For Each doc In cntContainer.Documents
   
   strDocName = doc.Name
   
   DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, intConst, _
   strDocName, strDocName
   
   'Debug.Print strDocName
   'for debugging, will list document names in debug window.
   
   Next doc
   Next x
   
   'Clean up variables to recover memory.
   
   Set fld = Nothing
   Set nrel = Nothing
   Set rel = Nothing
   Set cdb = Nothing
   Set td = Nothing
   Set qd = Nothing
   Set cntContainer = Nothing
   
   db.Close
   Set db = Nothing
   
   ImportDb = True
   
   End Function
   					

5. On the View menu, click Immediate Window.
6. In the Immediate window, type the following command line, and then press ENTER:

   ?ImportDb("C:\pathname\MySourceDatabase.mdb")

   Note Substitute the correct path and file name for the source database. This code returns "True" (or -1) if it runs successfully.