I have inherited an ACC2003 (.mdb)/SQL Server 2005 application and so far I
have identified 83 parent_tablename.primary_key/child_tablename.foreign_key
pairs for which I would like to pre-define the Join Property
(Left/Right/Inner) programmatically so that when the user drags/drops tables
into query design grid the preferred relationship and join type is there.
(Right now there are many automatic links of ID to ID where chilld record's
pk isn't ID - yech! - I'm working on that as I find them.

Google search found some “you can do it with DAO” responses but no
details/examples. Some links pointed to MS KBs but they only showed how to
use the GUI Relationships window. I’d rather not have to do all of that
clicking if I can whip up something to use my tables. Anybody know how?

If it is just simple
RS_MSysRel.AddNew and
RS_MSysRel("column") = value and
RS_MSysRel.Update etc.
then great, but what are those numeric values and what's the other stuff
like "UW" etc. Worst case is reverse engineer, but hope I don't have to.

Thanks for looking.

Oops - typo - make that "child tables FOREIGN key isn't ID - but you probably
figured that out anyway.

"NKTower" wrote:

> I have inherited an ACC2003 (.mdb)/SQL Server 2005 application and so far I
> have identified 83 parent_tablename.primary_key/child_tablename.foreign_key
> pairs for which I would like to pre-define the Join Property
> (Left/Right/Inner) programmatically so that when the user drags/drops tables
> into query design grid the preferred relationship and join type is there.
> (Right now there are many automatic links of ID to ID where chilld record's
> pk isn't ID - yech! - I'm working on that as I find them.
>
> Google search found some “you can do it with DAO” responses but no
> details/examples. Some links pointed to MS KBs but they only showed how to
> use the GUI Relationships window. I’d rather not have to do all of that
> clicking if I can whip up something to use my tables. Anybody know how?
>
> If it is just simple
> RS_MSysRel.AddNew and
> RS_MSysRel("column") = value and
> RS_MSysRel.Update etc.
> then great, but what are those numeric values and what's the other stuff
> like "UW" etc. Worst case is reverse engineer, but hope I don't have to.
>
> Thanks for looking.

I would strongly advise against updating system tables directly.

Use the CreateRelation method, or work with Properties of the Relation
object.

http://msdn.microsoft.com/en-us/library/bb221012.aspx

http://msdn.microsoft.com/en-us/library/bb177503.aspx

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"NKTower" <(E-Mail Removed)> wrote in message
news:AF68FB18-618D-46BD-9B5C-(E-Mail Removed)...
>I have inherited an ACC2003 (.mdb)/SQL Server 2005 application and so far I
> have identified 83
> parent_tablename.primary_key/child_tablename.foreign_key
> pairs for which I would like to pre-define the Join Property
> (Left/Right/Inner) programmatically so that when the user drags/drops
> tables
> into query design grid the preferred relationship and join type is there.
> (Right now there are many automatic links of ID to ID where chilld
> record's
> pk isn't ID - yech! - I'm working on that as I find them.
>
> Google search found some "you can do it with DAO" responses but no
> details/examples. Some links pointed to MS KBs but they only showed how
> to
> use the GUI Relationships window. I'd rather not have to do all of that
> clicking if I can whip up something to use my tables. Anybody know how?
>
> If it is just simple
> RS_MSysRel.AddNew and
> RS_MSysRel("column") = value and
> RS_MSysRel.Update etc.
> then great, but what are those numeric values and what's the other stuff
> like "UW" etc. Worst case is reverse engineer, but hope I don't have to.
>
> Thanks for looking.

Excellent! Thank you.

"Douglas J. Steele" wrote:

> I would strongly advise against updating system tables directly.
>
> Use the CreateRelation method, or work with Properties of the Relation
> object.
>
> http://msdn.microsoft.com/en-us/library/bb221012.aspx
>
> http://msdn.microsoft.com/en-us/library/bb177503.aspx
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>