Sample code to import all database objects in Access

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

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.

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.
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;CNTACTMSTo 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 DatabaseOption ExplicitPublic Function ImportDb(strPath As String) As BooleanOn Error Resume NextDim db As Database 'Database to importDim td As TableDef 'Tabledefs in dbDim strTDef As String 'Name of table or query to importDim qd As QueryDef 'Querydefs in dbDim doc As Document 'Documents in dbDim strCntName As String 'Document container nameDim x As Integer 'For loopingDim cntContainer As Container 'Containers in dbDim strDocName As String 'Name of documentDim intConst As IntegerDim cdb As Database 'Current DatabaseDim rel As Relation 'Relation to copyDim nrel As Relation 'Relation to createDim strRName As String 'Copied relation's nameDim strTName As String 'Relation Table nameDim strFTName As String 'Relation Foreign Table nameDim varAtt As Variant 'Attributes of relationDim fld As Field 'Field(s) in relation to copyDim strFName As String 'Name of field to appendDim 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.TableDefsstrTDef = td.NameIf Left(strTDef, 4) <> "MSys" ThenDoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acTable, _strTDef, strTDef, FalseEnd IfNext'Import queries.For Each qd In db.QueryDefsstrTDef = qd.NameDoCmd.TransferDatabase acImport, "Microsoft Access", strPath, acQuery, _strTDef, strTDef, FalseNext'Copy relationships to current database.Set cdb = CurrentDbFor Each rel In db.RelationsWith rel'Get properties of relation to copy.strRName = .NamestrTName = .TablestrFTName = .ForeignTablevarAtt = .Attributes'Create relation in current db with same properties.Set nrel = cdb.CreateRelation(strRName, strTName, strFTName, varAtt)For Each fld In .FieldsstrFName = fld.NamestrFFName = fld.ForeignNamenrel.Fields.Append nrel.CreateField(strFName)nrel.Fields(strFName).ForeignName = strFFNameNextcdb.Relations.Append nrelEnd WithNext'Loop through containers and import all documents.For x = 1 To 4Select Case xCase 1strCntName = "Forms"intConst = acFormCase 2strCntName = "Reports"intConst = acReportCase 3strCntName = "Scripts"intConst = acMacroCase 4strCntName = "Modules"intConst = acModuleEnd SelectSet cntContainer = db.Containers(strCntName)For Each doc In cntContainer.DocumentsstrDocName = doc.NameDoCmd.TransferDatabase acImport, "Microsoft Access", strPath, intConst, _strDocName, strDocName'Debug.Print strDocName'for debugging, will list document names in debug window.Next docNext x'Clean up variables to recover memory.Set fld = NothingSet nrel = NothingSet rel = NothingSet cdb = NothingSet td = NothingSet qd = NothingSet cntContainer = Nothingdb.CloseSet db = NothingImportDb = TrueEnd 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.
inf ACC2000

Article ID: 298174 - Last Review: 01/31/2007 19:05:49 - Revision: 3.7

  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
  • Microsoft Access 2000 Standard Edition
  • kbhowto kbinfo KB298174