Create an MSAccess Relationship Using ADOX - March 2003

I have been using ADO for database processing a lot lately. Recently I needed code to create relationships between MSAccess [Jet] tables. I found some code by searching Google. I found useful code on the Microsoft web site. Click here to view that code. The code only supports relating one column and I needed a couple columns. And the code did not show how to create cascading updates and deletes. So I modified the code to support multiple column relationships and cascading updates and deletes. I decided to make this the March 2003 tip-of-the-month.

2009/02/01- Click here to view a class module that adds relationships to SQL Server tables using SMO.

The Demo Program

I created a demo program to show how to use function 'CreateRelationshipUsingADOX':

frmCreateRelationshipADOX.frm

Database - The demo creates database 'SampleDB.mdb' with two tables, and corresponding indexes:


Access 2000 SampleDB.mdb with two tables.


Table1 with IndexTable1 - a Unique index.


Table2 with IndexTable2 - not a Unique index - allows a one-to-many relationship.

Create a Relationship then Show Results - Click this button to create the relationship. Code similar to the following is used to call function 'CreateRelationshipUsingADOX':

If CreateRelationshipUsingADOX(mcatSample, msRelationshipName, _
    mcsTable2, marysForeignTableKeys(), _
    mcsTable1, marysRelatedTableKeys(), _
    adRICascade, adRICascade) Then

 
The created relationship and relationship properties.

Delete Relationship 'relsNameLastsNameFirst' - Click this button to delete the relationship. Code used to delete the relationship:

Public Function DeleteRelationshipUsingADOX(catDB As ADOX.Catalog, _
      sTable As String, sRelationshipName As String) As Boolean
    Dim kee As ADOX.Key
    Dim tbl As ADOX.Table
    Set tbl = catDB.Tables(sTable)
    For Each kee In tbl.Keys
        With kee
            If .Name = sRelationshipName Then
                tbl.Keys.Delete .Name 'delete it
                DeleteRelationshipUsingADOX = True
                Exit Function 'bye
            End If
        End With
    Next
End Function

modCreateRelationshipUsingADOX Functions

Function   Sample
CreateRelationshipUsingADOX Create a relationship in an MSAccess Database  
Arguments    
catDB As ADOX.Catalog The ADOX Catalog - Points to a connected database. mcatSample
sRelationshipName As String Name of the relationship. Error created if it already exists. relsNameLastsNameFirst
sForeignTable As String Foreign table name Table2
arysForeignTableKeys() As String An array of key names. (1) = sNameLast
(2) = sNameFirst
sRelatedTable As String The related table name. Table1
arysRelatedTableKeys() As String A corresponding array of key names. Must be the same size as the foreign table keys array. (1) = sNameLast
(2) = sNameFirst
lUpdateRule As ADOX.RuleEnum The update rule. adRICascade
lDeleteRule As ADOX.RuleEnum The delete rule. adRICascade
Function   Sample
DeleteRelationshipUsingADOX Delete a relationship. See source code for details.  
Function    
FormatADOXRuleEnum(lRule As ADOX.RuleEnum) This function returns a string using the ADOX.RuleEnum. For example: If enum 'adRICascade' is passed string "adRICascade" is returned.  

Modules Used in the Demo

Module - Click for More Info Description Used in the Demo To
modCreateRelationshipUsingADOX.bas Contains the two function described above. The subject of this tip. Include it in your application.
modConnectToWebSite.bas Connect to a web site Show this info when Help/Info is selected from the demo.
modInfoAbout.bas Info and about help. Provide help via the web and 'about' info.

Download VB 6 Source Code

Click here to download the VB 6 source code [10k].

Previous Tip   Tip Index   Next Tip   First Tip

Home

Updated 2009/02/01