ACC: Using DAO in Visual Basic Code to Import Relationships

Last reviewed: August 29, 1997
Article ID: Q128157
The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article demonstrates how to use data access objects (DAO) in Visual Basic for Applications code to read the relationships from an external Microsoft Access database and import those relationships that can be successfully appended to the current database.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

When you attempt 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 current database.

The following example demonstrates how to use Relation objects in DAO to import the appropriate relationships into the current database:

  1. Create a new database called ImpRel.mdb.

  2. Import the following tables from the sample database Northwind.mdb (or NWIND.MDB in version 2.0) to the ImpRel database:

        - Products
        - Customers
        - Orders
        - Order Details
    

    NOTE: In Microsoft Access 7.0 and 97, click the Options button in the Import Objects dialog box and clear the Relationships option before you import the tables.

  3. Create a new module and type the following line in the declarations section:

          Option Explicit
    

  4. Type the following procedure:

    NOTE: In the following sample code, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

          Function ImportRelations (DbName As String) As Integer
          '------------------------------------------------------------------
          ' PURPOSE: Imports relationships where table names and field names
    
          '          match.
          ' ACCEPTS: The name of the external database as a string.
          ' RETURNS: The number of relationships imported as an integer.
          '------------------------------------------------------------------
    
          Dim ThisDb As Database, ThatDB As Database
          Dim ThisRel As Relation, ThatRel As Relation
          Dim ThisField As Field, ThatField As Field
          Dim Cr As String, i As Integer, cnt As Integer, RCount As Integer
          Dim j As Integer
          Dim ErrBadField As Integer
    
          Cr$ = Chr$(13)
          RCount = 0
    
          Set ThisDb = CurrentDB()
          Set ThatDB = DBEngine.Workspaces(0).OpenDatabase(DbName$)
    
          ' Loop through all existing relationships in the external database.
          For i = 0 To ThatDB.Relations.count - 1
             Set ThatRel = ThatDB.Relations(i)
    
             ' Create 'ThisRel' using values from 'ThatRel'.
             Set ThisRel = ThisDb.CreateRelation(ThatRel.name, _
                ThatRel.table, ThatRel.foreigntable, ThatRel.attributes)
    
             ' Set bad field flag to false.
             ErrBadField = False
    
             ' Loop through all fields in that relation.
             For j = 0 To ThatRel.fields.count - 1
                Set ThatField = ThatRel.fields(j)
    
                ' Create 'ThisField' using values from 'ThatField'.
                Set ThisField = ThisRel.CreateField(ThatField.name)
                ThisField.foreignname = ThatField.foreignname
    
                ' Check for bad fields.
                On Error Resume Next
                ThisRel.fields.Append ThisField
                If Err <> False Then ErrBadField = True
                On Error GoTo 0
             Next j
    
             ' If any field of this relationship caused an error,
             ' do not add this relationship.
             If ErrBadField = True Then
                ' Something went wrong with the fields.
                ' Do not do anything.
             Else
                ' Try to append the relation.
                On Error Resume Next
                ThisDb.Relations.Append ThisRel
                If Err <> False Then
                   ' Something went wrong with the relationship.
                   ' Skip it.
                Else
                   ' Keep count of successful imports.
                   RCount = RCount + 1
                End If
                On Error GoTo 0
             End If
          Next i
    
          ' Close databases.
          ThisDb.Close
          ThatDB.Close
    
          ' Return number of successful imports.
          ImportRelations = RCount
    
          End Function
    
    

  5. Save the module as DAOExample and close it.

  6. 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 re-creating this macro.

          Macro Name           Macro Actions
          ----------------------------------
          DAOImportRelations   RunCode
                               MsgBox
    
          DAOImportRelations Actions
          ---------------------------------------------------------------
          RunCode
             Function Name: ImportRelations("C:\Program Files\Microsoft _
                            Office\Office\Samples\Northwind.mdb")
          MsgBox
             Message: All Done.
    
       NOTE: If your copy of Microsoft Access is not installed in the
       Program Files\Microsoft Office\Office folder (directory) on drive C,
       substitute the correct drive and path in the ImportRelations() function.
    
    

  7. Save the macro, and then close it.

  8. Run the macro. The three relationships defined among the four tables you imported from the Northwind database are imported. The other four relationships in the Northwind database are not imported.

  9. To see the relationships in the current database, select the Database window and then on the Tools menu (or the Edit menu in version 2.0) click Relationships. On the Relationships menu, click Show All. All the tables and relationships are displayed in the window; the relationships among the tables are drawn automatically.
Keywords          : kbprg PgmHowTo MdlDao
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 29, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.