Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA CODE TO EST RELATIONSHIPS - NBS

    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

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA CODE TO EST RELATIONSHIPS - NBS

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

    Post back if you need further assistance.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA CODE TO EST RELATIONSHIPS - NBS

    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. [img]/forums/images/smilies/smile.gif[/img]

    Thanks, John Graves

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA CODE TO EST RELATIONSHIPS - NBS

    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:
    <A target="_blank" HREF=http://msdn.microsoft.com/library/default.asp?URL=/library/books/dnjet/c3_body_37.htm>http://msdn.microsoft.com/library/default..../c3_body_37.htm</A>

    Stay tuned for a simplified example.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA CODE TO EST RELATIONSHIPS - NBS

    Sorry for the delay. This should work for you:<pre>Function CreateRelationship(strTable As String, _
    strOneField As String, strForeignTable As String, _
    strManyField As String)

    '=================================================
    'Created by Mark S. Johnston
    'MarkjSC@yahoo.com
    '<A target="_blank" HREF=http://www.geocities.com/MarkjSC>http://www.geocities.com/MarkjSC</A>
    '
    'Adapted from MSDN Library article:
    '<A target="_blank" HREF=http://msdn.microsoft.com/library/default.asp>http://msdn.microsoft.com/library/default.asp</A>? _
    '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</pre>

    Post back if there are any problems.

    <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA CODE TO EST RELATIONSHIPS - NBS

    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

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code To Test Relations - NBS

    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'.
    '<A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=44019>http://www.wopr.com/cgi-bin/w3t/showthread...cc&Number=44019</A>
    '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
    ' <A target="_blank" HREF=http://www.geocities.com/MarkjSC>http://www.geocities.com/MarkjSC</A>
    '
    'Adapted from MSDN Library article:
    ' <A target="_blank" HREF=http://msdn.microsoft.com/library/default.asp>http://msdn.microsoft.com/library/default.asp</A> ? _
    '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 - <A target="_blank" HREF=http://www.geocities.com/MarkjSC>http://www.geocities.com/MarkjSC</A>
    '<A target="_blank" HREF=http://www.wopr.com/w3tuserpics/MarkJ_sig.gif>http://www.wopr.com/w3tuserpics/MarkJ_sig.gif</A>

  8. #8
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code To Test Relations - NBS

    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.

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code To Test Relations - NBS

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •