Results 1 to 8 of 8
  1. #1
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Create Left Join in Code (Access 97 SR-1)

    Does anybody know how to do this in DAO? DAO recognizes the dbRelationLeft attribute constant but Access 97 doesn't seem to know what to do with it. Is there another way to make the relationship a left outer join programmatically? Our code updates back end table design nicely, and my new code happily creates multiple field relationships with referential integrity in the BE database, but I can't figure out how to make it create left joins!
    Charlotte

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create Left Join in Code (Access 97 SR-1)

    Charlotte,

    The following code (or something close to it for your tables) seems to set up the left join relationship:
    <font face="Georgia">
    Dim rel As Relation
    Dim fld As Field
    Set rel = CurrentDb.CreateRelation("Relation1", "Addresses", "Names", dbRelationLeft)
    Set fld = rel.CreateField("AddressID")
    rel.Fields.Append fld
    rel.Fields!AddressID.ForeignName = "AddressID"
    CurrentDb.Relations.Append rel
    </font face=georgia>
    Hope this helps (I fear that I may be missing something in the problem when I have a possible answer to the Pro's question).

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Create Left Join in Code (Access 97 SR-1)

    Well said Tom, Savour this everybody !! You won't see this very often !! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    SOLVED-Create Left Join in Code (Access 97 SR-1)

    Thanks Tom, but I figured out an answer that applies to my situation and hadn't had a chance to post it . As for missing something in the problem, I'm just as prone to that any anyone. I can't remember the last time I used code to create relationships, and when I did it was with ADO, not DAO. <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>

    In fact, what I'm doing is complex and table-driven and the routine has to handle possible multifield relationships and various possible attributes and create the relationships in the linked backend of the app, not in the database where the code runs. Oh, yeah, they're also secured databases. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I use a custom datatype to pass the relationship name, primary and foreign tables, and the several primary and foreign fields into the function, as well as a series of boolean elements to allow the calling routine to specify the particular attributes to apply to the relationship. It looks like this:

    <pre>Public Type NewRelation 'holds values for a relationship
    RelName As Variant 'name of relationship object
    PrimaryTable As String 'primary table name (parent table)
    ForeignTable As String 'foreign table name (child table)
    PrimaryField1 As String 'key field name in primary table
    PrimaryField2 As String 'additional keys for multi-field relation
    PrimaryField3 As String '
    ForeignField1 As String 'key field name in foreign table
    ForeignField2 As String 'additional keys for multi-field relation
    ForeignField3 As String '
    AttribUnique As Boolean 'Unique relationship?
    AttribDontEnforce As Boolean 'No referential integrity?
    AttribInherited As Boolean 'relationship in non-current database?
    AttribUpdateCascade As Boolean 'Cascading updates?
    AttribDeleteCascade As Boolean 'Cascading deletes?
    AttribLeftJoin As Boolean 'Left outer join
    End Type 'NewRelation</pre>


    Since attributes are set bitwise, I finally decided to loop through the several boolean elements and create a string of "dbRelationLeft or dbRelation ...", etc. and then use an Eval function to translate that when I set the attributes of the relationship. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> Aren't computers fun? It took me a day to create and test the DAO code to create multifield relationships based on a record in a table and another half day to figure out how to make it a left join! <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create Left Join in Code (Access 97 SR-1)

    Now, Pat, be nice! I get programmer's block just like everyone else. <img src=/S/crybaby.gif border=0 alt=crybaby width=15 height=15>
    Charlotte

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Create Left Join in Code (Access 97 SR-1)

    From what I have seen over many months, I find that hard to believe !!

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Create Left Join in Code (Access 97 SR-1)

    Trust me! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SOLVED-Create Left Join in Code (Access 97 SR-1)

    I didn't explain the solution very well, so here's the code snippet I actually used:
    <pre> 'if any attribute flags were passed, apply the attributes
    For intloop = 1 To 6
    blnAttrib = Choose(intloop, NewRel.AttribDontEnforce, NewRel.AttribDeleteCascade, _
    NewRel.AttribInherited, NewRel.AttribUnique, NewRel.AttribUpdateCascade, _
    NewRel.AttribLeftJoin)
    If blnAttrib Then
    Select Case intloop
    Case 1
    'cascading updates/deletes can only exist if relational integrity
    'is enforced
    rel.Attributes = rel.Attributes Or dbRelationDontEnforce
    Case 2
    If Not NewRel.AttribDontEnforce Then
    rel.Attributes = rel.Attributes Or dbRelationDeleteCascade
    End If
    Case 3
    rel.Attributes = rel.Attributes Or dbRelationInherited
    Case 4
    rel.Attributes = rel.Attributes Or dbRelationUnique
    Case 5
    If Not NewRel.AttribDontEnforce Then
    rel.Attributes = rel.Attributes Or dbRelationUpdateCascade
    End If
    Case 6
    rel.Attributes = rel.Attributes Or dbRelationLeft
    End Select 'Case intLoop
    End If
    Next intloop ' = 1 To 6</pre>

    Charlotte

Posting Permissions

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