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.
I created a demo program to show how to use function 'CreateRelationshipUsingADOX':
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
If .Name = sRelationshipName Then
tbl.Keys.Delete .Name 'delete it
DeleteRelationshipUsingADOX = True
Exit Function 'bye
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.
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.
Click here to download the VB 6 source code [10k].
Previous Tip Tip Index Next Tip First Tip