Microsoft KB Archive/298176

= Sample code to import all database objects in Access 97 =

Article ID: 298176

Article Last Modified on 1/31/2007

-

APPLIES TO


 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q298176



Moderate: Requires basic macro, coding, and interoperability skills.





For a Microsoft Access 2000 or later version of this article, see 298174.



SUMMARY
This article describes how you to 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 (usually the Administrator) becomes the owner of all imported objects.



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:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

To import all the objects from another database into the current database, follow these steps:  Start Access, and then open the database into which you want to import objects.

This may be a new blank database. In the Database window, click Modules, and then click New. On the Tools menu, click References.

Make sure that Microsoft DAO 3.0 Object Library or later is selected in the list of references, and then click OK.  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) <> &quot;MSys&quot; Then

DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, strPath, acTable, _ strTDef, strTDef, False

End If

Next

'Import queries

For Each qd In db.QueryDefs

strTDef = qd.Name

DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, 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 = &quot;Forms&quot; intConst = acForm

Case 2 strCntName = &quot;Reports&quot; intConst = acReport

Case 3 strCntName = &quot;Scripts&quot; intConst = acMacro

Case 4 strCntName = &quot;Modules&quot; intConst = acModule

End Select

Set cntContainer = db.Containers(strCntName)

For Each doc In cntContainer.Documents

strDocName = doc.Name

DoCmd.TransferDatabase acImport, &quot;Microsoft Access&quot;, 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  On the View menu, click Debug Window.</li> In the Debug window, type the following command line, and then press ENTER:

?ImportDb(&quot;C:\ \ .mdb&quot;)

Note Substitute the correct path and file name for the source database. This code returns &quot;True&quot; (or -1) if it runs successfully.</li></ol>

Additional query words: inf import

Keywords: kbimport kbprogramming kbhowto kbinfo KB298176

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.