ACC2002: How to use ADOX to Import Relationships

Article translations Article translations
Article ID: 304322 - View products that this article applies to.
This article was previously published under Q304322
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

Expand all | Collapse all

Summary

This article shows you how to use the ActiveX Data Objects Extensions for DDL and Security (ADOX) in Visual Basic for Applications code to read the relationships from an external Microsoft Access database and to import those relationships that can be successfully appended to the current database.

More information

NOTE: The sample code in this article uses Microsoft ActiveX Data Objects Extensions for DDL and Security. For this code to run properly, you must reference the Microsoft ActiveX Data Objects Extensions for DDL and Security 2.x Library (where 2.x is 2.1 or later.) To do so, click References on the Tools menu in the Visual Basic Editor, and ensure that the Microsoft ADO Ext. 2.x for DDL and Security check box is selected.

When you try to append relationships in the current database, you can append only those relationships that have table names and field names that match those in the other database.

The following example demonstrates how to use the Catalog, the Table, and the Key collections and objects in ADOX to import the appropriate relationships into the current database:
  1. Create a new database, and name it ImpRelADOX.mdb.
  2. Import the following tables from the sample database Northwind.mdb to the ImpRel database, one at a time:
    • Products
    • Customers
    • Orders
    • Order Details
    NOTE: If you import the tables in one operation, Microsoft Access automatically imports the relationship between the tables.
  3. Create a new module, and then type the following line in the Declarations section if it is not already there:
    Option Explicit
  4. On the Tools menu, click References, and then click to select the Microsoft ADO Ext. 2.x for DDL and Security check box. Click OK.
  5. Type or paste the following procedure:
    Function ImportRelationshipsADOX(strOtherDatabase As String)
    On Error GoTo Err_ImportRelationshipsADOX
    '------------------------------------------------------------------
    ' PURPOSE: Imports relationships where table names and field names
    '          match.
    ' ACCEPTS: The name of the external database as a string.
    '------------------------------------------------------------------
    
        ' Variables for current database and relationships to be created.
        Dim catCurrent As New ADOX.Catalog
        Dim tblCurrent As ADOX.Table
        Dim fkNew As New ADOX.Key
        Dim strTableSys As String
        
        ' Variables for external database and existing relationships.
        Dim catOther As New ADOX.Catalog
        Dim tblOther As ADOX.Table
        Dim fkOther As ADOX.Key
        Dim strOtherColumn As String
        Dim strOtherRelatedColumn As String
        
        ' Open the catalogs for the current and external databases.
        catCurrent.ActiveConnection = CurrentProject.Connection
        catOther.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & strOtherDatabase & ";"
    
        ' Loop through the tables in the current database
        ' and create relationships that are the same
        ' as the external database.
        For Each tblCurrent In catCurrent.Tables
            ' Get the first four letters of the table name.
            ' You can identify system tables by the prefix 'MSys'
            ' in the table name.
            strTableSys = Left(tblCurrent.Name, 4)
            ' Determine what type of table you have and create
            ' relationships as necessary.
            If tblCurrent.Type = "VIEW" Then
                ' You do not want to create relationships for Views.
            ElseIf strTableSys = "MSys" Then
                ' You do not want to create relationships for System Tables
            Else
                ' Look at the same table in the other database.
                Set tblOther = catOther.Tables(tblCurrent.Name)
                ' Loop through the keys in the collection for the
                ' other table and identify whether or not to duplicate.
                For Each fkOther In tblOther.Keys
                    ' Primary Key in other table reports a related
                    ' table property of empty-length string.
                    If fkOther.RelatedTable <> "" Then
                        ' Set current table relationship properties
                        ' to table located in other database.
                        fkNew.Name = fkOther.Name
                        fkNew.Type = adKeyForeign
                        fkNew.RelatedTable = fkOther.RelatedTable
                        strOtherColumn = fkOther.Columns(0).Name
                        strOtherRelatedColumn = fkOther.Columns(0).RelatedColumn
                        fkNew.Columns.Append strOtherColumn
                        fkNew.Columns(strOtherColumn).RelatedColumn = strOtherRelatedColumn
                        tblCurrent.Keys.Append fkNew
                        ' Reset the variable for the next foreign key.
                        Set fkNew = Nothing
                    End If
                Next
            End If
        Next
    
    Exit_ImportRelationshipsADOX:
        ' Clean up by destroying variables
        Set catCurrent = Nothing
        Set tblCurrent = Nothing
        Set fkNew = Nothing
        strTableSys = ""
        Set catOther = Nothing
        Set tblOther = Nothing
        Set fkOther = Nothing
        strOtherColumn = ""
        strOtherRelatedColumn = ""
        
        ' Exit the function.
        Exit Function
    
    Err_ImportRelationshipsADOX:
        Select Case Err.Number
            Case -2147217860
                ' Related table does not exist in this database.
                Resume Next
            Case -2147467259
                ' Relationship already exists, do not try to duplicate.
                Resume Next
            Case Else
                ' Some other error.
                Debug.Print Err.Number & " - " & Err.Description
        End Select
        Resume Exit_ImportRelationshipsADOX
    
    End Function
    					
  6. Save the module as ADOXExample, and then close it.
  7. Create the following new macro:
    NOTE: In the following macro expression, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when you re-create this macro.
          Macro Name            Macro Actions
          ----------------------------------
          ADOXImportRelations   RunCode
                                MsgBox
    
          DAOImportRelations Actions and Action Arguments
          ---------------------------------------------------------------
          RunCode
             Function Name: ImportRelationshipsADOX("C:\Program Files\_
                            Microsoft Office\Office10\Samples\Northwind.mdb")
          MsgBox
             Message: Compeleted
    					
    NOTE: If your copy of the sample database Northwind.mdb is not installed in the Program Files\Microsoft Office\Office10\Samples folder (directory) on drive C, substitute the correct drive and path in the Function Name argument in the macro.
  8. Save the macro as ADOXImportRelations, and then close it.
  9. Run the macro. The three relationships defined among the four tables that you imported from the Northwind database are imported. The other four relationships in the Northwind database are not imported.
  10. To see the relationships in the current database, switch to the Database window, and then on the Tools menu, click Relationships. All the tables and relationships are displayed in the window. The relationships among the tables are drawn automatically.

References

For more information about importing database objects such as tables, click Microsoft Access Help on the Help menu, type import data in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 304322 - Last Review: October 24, 2013 - Revision: 1.0
Applies to
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbnosurvey kbarchive kbhowto KB304322

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com