VBA CODE TO EST RELATIONSHIPS – NBS

Topic

New Reply

VBA CODE TO EST RELATIONSHIPS – NBS

Using Access 2000 (9.0.4402) SR-1

I have to convert several identical users to access 2000. I’m trying to automate the file conversion process as much as possible. I’m using Macro’s and Queries to input and prepare the data for Access. At the end of this process I would to run some code to automatically establish my relationships as follows:

tblMaster tblDetail

IngMasterID lngMasterID

One to Many
Yes – Enforce Referential Integrity
Yes – Cascade Update Related Fields
Yes – Cascade Delete Related Records

Looking for code to perform the above relationship.

Thanks, John Graves

Reply | Quote

Replies

You could use the Relation(s) objects in DAO to create the relationship and set its properties.

Post back if you need further assistance.

Reply | Quote

Hi Mark

Very new to access, can’t even spell DAO, looking for code/example to perform the above relationship, 3rd grade version wood be nice.

Thanks, John Graves

Reply | Quote

Sorry for the delay. This one isn’t something I do very often and Access help is kinda sketchy on this one. I’m still working on code for an example. I found an example on the MSDN site:
http://msdn.microsoft.com/library/default…./c3_body_37.htm

Stay tuned for a simplified example.

Reply | Quote

Sorry for the delay. This should work for you:

Function CreateRelationship(strTable As String, _
    strOneField As String, strForeignTable As String, _
    strManyField As String)
    
'=================================================
'Created by Mark S. Johnston
'MarkjSC@yahoo.com
'http://www.geocities.com/MarkjSC
'
'Adapted from MSDN Library article:
'http://msdn.microsoft.com/library/default.asp? _
'URL=/library/books/dnjet/c3_body_37.htm
'=================================================
    Dim strRelationName As String
    Dim db As DAO.Database
    Dim rln As DAO.Relation
    Dim fld As DAO.Field
    
    Set db = CurrentDb()
    
    'Destroy any currently existing relationships between
    'the two tables
    For Each rln In db.Relations
        If rln.Table = strTable And rln.ForeignTable = strForeignTable Then
            db.Relations.Delete rln.Name
        End If
    Next 'rln
    
    strRelationName = strTable & "_" & strForeignTable
    
    'Create the Relationship
    Set rln = db.CreateRelation(strRelationName)
    
    'Set the properties of the Relationship
    With rln
        .Table = strTable
        .ForeignTable = strForeignTable
        
        'Relationship will be Cascade Update and Cascade Delete
        .Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
    End With
    
    'Set the Field of the Relation object
    Set fld = rln.CreateField(strOneField)
    fld.ForeignName = strManyField
    
    'Add the Field to the Relation object
    rln.Fields.Append fld
    
    'Add the Relation to the Databse
    db.Relations.Append rln
    
    'destroy the evidence ;^)
    Set fld = Nothing
    Set rln = Nothing
    Set db = Nothing
    
End Function

Post back if there are any problems.

Reply | Quote

Hi Mark

Thanks for the quick reply, I will try to implement this code over the next several days.

With the service you provide, I vote for a forth star.

John Graves

PS: any clues on post # 44075 and 44073

Reply | Quote

Hi Mark

I get the following error when running CreateRelationship code. I’m very new to access & it’s probably somthing missing on my end.

Dim db As DAO.Database (Compile error – User-defined type not defined)

Thanks, John Graves

————————
‘ button on form
Private Sub Set_Relationships_Click()
On Error GoTo Err_Set_Relationships_Click

Dim strTable As String, strOneField As String
Dim strForeignTable As String, strManyField As String

strTable = “tblBPermit”
strOneField = “lngBPermitID”
strForeignTable = “tblBPFees”
strManyField = “ingBPermitID”

Call CreateRelationship(strTable, strOneField, strForeignTable, strManyField)
DoCmd.Close

Exit_Set_Relationships_Click:
Exit Sub

Err_Set_Relationships_Click:
MsgBox Err.Description
Resume Exit_Set_Relationships_Click

End Sub

————————-
‘ your code
Option Compare Database

‘MarkJ replied to your post on the Access board at the Woody’s Lounge site:
‘Re: VBA CODE TO EST RELATIONSHIPS – NBS’.
‘http://www.wopr.com/cgi-bin/w3t/showthread…cc&Number=44019
‘Sorry for the delay. This should work for you:

Function CreateRelationship(strTable As String, _
strOneField As String, _
strForeignTable As String, _
strManyField As String)

‘=================================================
‘Created by Mark S. Johnston
‘MarkjSC@yahoo.com
‘ http://www.geocities.com/MarkjSC
‘
‘Adapted from MSDN Library article:
‘ http://msdn.microsoft.com/library/default.asp ? _
‘URL=/library/books/dnjet/c3_body_37.htm
‘=================================================

Dim strRelationName As String
Dim db As DAO.Database
Dim rln As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()

‘Destroy any currently existing relationships between
‘the two tables
For Each rln In db.Relations
If rln.Table = strTable And rln.ForeignTable = strForeignTable Then
db.Relations.Delete rln.Name
End If
Next ‘rln

strRelationName = strTable & “_” & strForeignTable

‘Create the Relationship
Set rln = db.CreateRelation(strRelationName)

‘Set the properties of the Relationship
With rln
.Table = strTable
.ForeignTable = strForeignTable

‘Relationship will be Cascade Update and Cascade Delete
.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade
End With

‘Set the Field of the Relation object
Set fld = rln.CreateField(strOneField)
fld.ForeignName = strManyField

‘Add the Field to the Relation object
rln.Fields.Append fld

‘Add the Relation to the Databse
db.Relations.Append rln

‘destroy the evidence ;^)
Set fld = Nothing
Set rln = Nothing
Set db = Nothing

End Function

‘Post back if there are any problems.
‘MarkJ – http://www.geocities.com/MarkjSC
‘http://www.wopr.com/w3tuserpics/MarkJ_sig.gif

Reply | Quote

In the Visual Basic Editor, go to Tools | References… and select “Microsoft Data Access Objects 3.6” (aka DAO).

If you’re not planning to use Active Data Objects (ADO) in this project, you can uncheck the reference for it – to simplify things.

Reply | Quote

Hi Mark

Your right, DAO 3.6 was not checked.

Your code worked perfectly.

Do you know of any front-end code that would allow the developer to open a unbound form combo box and display a user relationship table as follows:

tblRelationshipsBP
strTable
strOneFeild
strForeignTable
strManyFeild
ysnEnforceRefInt
ysnCascadeUpdate
ysnCascadeDelete
strJoinType 1, 2, or 3
ysnSetRelationship Include this relationship in the batch
(don’t know if I’m missing something)

The developer would Ctrl Click all the relationship records that would be included in the batch.

The code would then perform all the relationships that were selected.

I’m not quite sure of the code required to cycle through the record selected and feed them to your CreateRelationship function.

Thanks, John Graves

NBS7335

Reply | Quote