ACC2000: How to Use DAO in Visual Basic Code to Import Relationships (210302)

MORE INFORMATION

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.

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 one at a time from the sample database Northwind.mdb to the ImpRel database:
   • Products
   • Customers
   • Orders
   • Order Details

   NOTE: If you import the tables in one operation, Microsoft Access automatically imports the relationship between the tables as well.

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 DAO 3.6 Object Library check box. Click OK.
5. Type or paste the following procedure:

   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 DAO.Database, ThatDB As DAO.Database
   Dim ThisRel As DAO.Relation, ThatRel As DAO.Relation
   Dim ThisField As DAO.Field, ThatField As DAO.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. Save the module as DAOExample and 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 re-creating this macro.

         Macro Name           Macro Actions
         ----------------------------------
         DAOImportRelations   RunCode
                              MsgBox
   
         DAOImportRelations Actions and Action Arguments
         ---------------------------------------------------------------
         RunCode
            Function Name: ImportRelations("C:\Program Files\Microsoft _
                           Office\Office\Samples\Northwind.mdb")
         MsgBox
            Message: All Done.
   					

   NOTE: If your copy of the sample database Northwind.mdb is not installed in the Program Files\Microsoft Office\Office\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 DAOImportRelations, 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, select 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.