Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Location
    Atlanta, GA
    Posts
    6

    Unanswered: MSysRelationships and szRelationship

    In the MSysRelationships table, sometimes the contents of the szRelationship field is Table1Table2, which is self-explanatory.

    Other times, the contents of szRelationship is a CLSID. Does anyone know how to parse the CLSID to determine what it is doing, or know of a reference where I can read up on it?

    TIA,

    Far Farley

  2. #2
    Join Date
    Mar 2005
    Posts
    261
    szRelationship is a unique textual identifier for the relationship (although it's not defined as unique).

    If my memory serves me correctly, in Access 97 (and probably lower) it was a GUID stored as text (technically not a CLSID), and from Access 2000 upwards it is the (slightly) more meaningful combination name of the two referenced objects (followed by a unique number if there are multiple relationships from/to the same tables). If you have any Access 2000+ databases that were converted from an old Access 97 database, they will also have the old GUIDs rather than the names.

    The GUID itself is meaningless to you (other than as an ID). What are you trying to achieve?

  3. #3
    Join Date
    Mar 2006
    Location
    Atlanta, GA
    Posts
    6
    Wayne,

    Thanks for the reply.

    What I am trying to achieve is to understand how Access handles the relationships table, and to have full programmatic control in both reading the table and writing to it.

    Based on your response, I was able to do a bit of homework by playing with the object model.

    The table MSysRelationships has the following fields that map as follows to the object model (assuming a currentdb of "db" has been declared, and i is an integer fromt 0 to db.relations.count -1):

    db.relations(i).name <==> szRelationship
    ??? <==> icolumn
    ??? <==> ccolumn
    db.relations.attributes <==> grbit
    db.relations.table <==> szObject
    ??? <==> szColumn
    db.relations.foreigntable <==> szReferencedObject
    ??? <==> szReferencedColumn

    although the table presents the fields in alphabetical order. The table has no PK, but the combination of the szRelationship and icolumn fields uniquely identify a row.

    If the GUID's are from Access 97 and earlier, but aren't used from Access 2000 forward, then that problem is solved.

    My questions now are how to reference the icolumn, ccolumn, szcolumn, and szreferenced column fields through the object model, as opposed to via a recordset (i.e. the items marked "???" above), and how to decode the grbit, each of which will be put in separate posts.

    Thanks again,

    Far

  4. #4
    Join Date
    Mar 2005
    Posts
    261
    The integers are indices that represent the fields... e.g. db.Relations(i).Fields(ccolumn/icolumn).Name but you probably don't need to access them that way, since the table also gives you the name of the fields in the sz fields.

    Also, remember that you can have multiple fields involved in a single relationship, so you can have multiple rows in the table for each relationship.

    grbit is for referential integrity etc. I can't remember the mappings from the top of my head, but I have it written somewhere - I'll try to route it out for you.

    HTH
    Last edited by waynephillips; 04-06-06 at 15:31.

  5. #5
    Join Date
    Mar 2006
    Location
    Atlanta, GA
    Posts
    6

    MSysRelationships and szRelationship

    Wayne,

    Many thanks.

    Far

Posting Permissions

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