ACC: Using DAO in Visual Basic Code to Import Relationships

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 shows you how to use Data Access Objects (DAO) 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.

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 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 current database.

The following example demonstrates how to use the 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.

   section if it is not already there:

      Option Explicit

   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

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.

8. 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.

   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 MdlDao 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbinfo

Last Reviewed: November 21, 1998