Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

Sample code to import all database objects in Access

View products that this article applies to.

This article was previously published under Q298174
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

↑ Back to the top


This article describes how you can use Data Access Objects (DAO) to import all objects from one Microsoft Access database into the current Access database. In some situations, this code can be used to recover database objects from a corrupted or damaged database that can be opened but cannot be compacted successfully. This code does not import the following elements:
  • References
  • Import/Export specifications
  • Security information (user and group permissions)
The current user (typically the administrator) becomes the owner of all imported objects.

↑ Back to the top

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 Microsoft Advisory Services. For more information, visit these Microsoft Web sites:

Microsoft Certified Partners -

Microsoft Advisory Services -

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:;EN-US;CNTACTMS 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
    'Import queries.
    For Each qd In db.QueryDefs
    strTDef = qd.Name
    DoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acQuery, _
    strTDef, strTDef, False
    '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
    cdb.Relations.Append nrel
    End With
    '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
    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:
    Note Substitute the correct path and file name for the source database. This code returns "True" (or -1) if it runs successfully.

↑ Back to the top

Keywords: KB298174, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 298174
Revision : 10
Created on : 1/31/2007
Published on : 1/31/2007
Exists online : False
Views : 356