469,319 Members | 2,423 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,319 developers. It's quick & easy.

Creating table relationships with code

Hello

Does anyone know if it is possible with Access 2000 to
create relationships between tables using code?

Thanks
G.Gerard
Nov 13 '05 #1
3 10560
Yes, here is an example:

Sub CreateRelationDAO()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

'Initialize
Set db = CurrentDb()

'Create a new relation.
Set rel = db.CreateRelation("MyMainTableMyRelatedTable")

'Define its properties.
With rel
'Specify the primary table.
.Table = "MyMainTable"
'Specify the related table.
.ForeignTable = "MyRelatedTable"
'Specify attributes for cascading updates and deletes.
.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade

'Add the fields to the relation.
'Field name in primary table.
Set fld = .CreateField("MyMainTableID")
'Field name in related table.
fld.ForeignName = "MyForeignKeyID"
'Append the field.
.Fields.Append fld

'Repeat for other fields if a multi-field relation.

End With

'Save the newly defined relation to the Relations collection.
db.Relations.Append rel

'Clean up
Set fld = Nothing
Set rel = Nothing
Set db = Nothing
Debug.Print "Relation created."
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"GGerard" <gg*****@nbnet.nb.ca> wrote in message
news:UG**********************@ursa-nb00s0.nbnet.nb.ca...
Hello

Does anyone know if it is possible with Access 2000 to
create relationships between tables using code?

Thanks
G.Gerard

Nov 13 '05 #2
Hello and thank you Allen Browne for your reply

As a follow up question can relationships be set between tables that reside
in two different mdb?

example : can a relationship be set between Table1 in First.mdb and Table2
in Second.mdb?
-how about if they are linked to a third mdb; can relationships(cascade
update and delete) be set between
Table1 and Table2 in this third mdb?

Thanks
G.Gerard

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...
Yes, here is an example:

Sub CreateRelationDAO()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

'Initialize
Set db = CurrentDb()

'Create a new relation.
Set rel = db.CreateRelation("MyMainTableMyRelatedTable")

'Define its properties.
With rel
'Specify the primary table.
.Table = "MyMainTable"
'Specify the related table.
.ForeignTable = "MyRelatedTable"
'Specify attributes for cascading updates and deletes.
.Attributes = dbRelationUpdateCascade + dbRelationDeleteCascade

'Add the fields to the relation.
'Field name in primary table.
Set fld = .CreateField("MyMainTableID")
'Field name in related table.
fld.ForeignName = "MyForeignKeyID"
'Append the field.
.Fields.Append fld

'Repeat for other fields if a multi-field relation.

End With

'Save the newly defined relation to the Relations collection.
db.Relations.Append rel

'Clean up
Set fld = Nothing
Set rel = Nothing
Set db = Nothing
Debug.Print "Relation created."
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"GGerard" <gg*****@nbnet.nb.ca> wrote in message
news:UG**********************@ursa-nb00s0.nbnet.nb.ca...
Hello

Does anyone know if it is possible with Access 2000 to
create relationships between tables using code?

Thanks
G.Gerard


Nov 13 '05 #3
GGerard wrote:
Hello and thank you Allen Browne for your reply

As a follow up question can relationships be set between tables that reside
in two different mdb?

example : can a relationship be set between Table1 in First.mdb and Table2
in Second.mdb?
-how about if they are linked to a third mdb; can relationships(cascade
update and delete) be set between
Table1 and Table2 in this third mdb?


No. Even if it did do this it is not enforcable and would be easily
circumvented.

--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Jeremy Weiss | last post: by
4 posts views Thread by Ronnie | last post: by
2 posts views Thread by Gandalf | last post: by
117 posts views Thread by phil-news-nospam | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.