By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

How to Create Compound Relationship from Code

P: 3
I have a database which is defined in a MIL standard. This consists of 104 tables and has a key that consists every time of three fields at minimum. I do not want to have all relations in the database because of stability reasons. Secondly, the data is delivered in a single DAT file and has to be spread over the tables through an import. I want to load the data and then run code to check on duplicates and orphan records, so it will not be during the import.
However, it would make life much easier if I can establish the relationships at runtime only for those tables that I need and remove them afterwards. I have found the CreateRelationships for DAO, but this is only for one key field. It is possible to create a relationship with multiple keyfields manually via the drop down menu.
Does anybody know what to run in VBA to create a relationship on multiple fields?
E.g.
Table: Master
Fields: Fld1, Fld2 and Fld3

Table: Slave
Fields: Fld1, Fld2 and Fld504

Create relationship between tables Master and Slave on fields Fld1 and Fld2
Apr 23 '12 #1

✓ answered by NeoPa

DAO.Database.CreateRelation() allows you to create the relationship. It is important that all Fields are added to this new object before the newly created Relation object is added to the Relations collection of the DAO.Database.

Let us know how you get on with this.

NB. Accuracy is very important. Please remember to post only accurate information to avoid wasting others' and your time.

Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,417
ADils:
I have found the CreateRelationships for DAO
Strange, I found no reference to such a name in my DAO. If you were to include what you had found, I would look into it further for you and see if I can determine how to use it for compound indices (multi-field keys).
Apr 23 '12 #2

NeoPa
Expert Mod 15k+
P: 31,417
DAO.Database.CreateRelation() allows you to create the relationship. It is important that all Fields are added to this new object before the newly created Relation object is added to the Relations collection of the DAO.Database.

Let us know how you get on with this.

NB. Accuracy is very important. Please remember to post only accurate information to avoid wasting others' and your time.
Apr 23 '12 #3

P: 3
Hello NeoPa,
Thanks for the reply! It has taken me quite some time to figure out what exactly you meant.

I used the code from CodeProject
and modified this a bit:

Expand|Select|Wrap|Line Numbers
  1. Sub AddCompositeKey()
  2.  
  3.     Dim Db As DAO.Database
  4.     Dim newRelation As DAO.Relation
  5.     Dim relatingField As DAO.Field
  6.  
  7.     Set Db = CurrentDb()
  8.  
  9.     Set newRelation = Db.CreateRelation("REF1", "Master", "Slave" _
  10.     , dbRelationDontEnforce)
  11.     'Fld from Prim table (Master)
  12.     Set relatingField = newRelation.CreateField("Fld1")
  13.     'Fld from Slave
  14.     relatingField.ForeignName = "Fld1"
  15.     'Add the field to the relation's Fields collection.
  16.     newRelation.Fields.Append relatingField
  17.  
  18.     'Fld from Prim table (Master)
  19.     Set relatingField = newRelation.CreateField("Fld2")
  20.     'Fld from Slave
  21.     relatingField.ForeignName = "Fld2"
  22.     'Add the field to the relation's Fields collection.
  23.     newRelation.Fields.Append relatingField
  24.  
  25.     'Add the relation to the database.
  26.     Db.Relations.Append newRelation
  27.     Db.Relations.Refresh
  28.     Set Db = Nothing
  29.  
  30. End Sub
It is up and running!

I am not sure if your last remark about accuracy is a general statement. If not, can you explain it? My intro may be a bit long, but a lot of discussions that I have read are questions why someone would choose this route. I tried to avoid this.

Thank you very much for your help!
This question can be closed.
Apr 23 '12 #4

NeoPa
Expert Mod 15k+
P: 31,417
ADils, I removed the link as we don't allow hyperlinks to competing forum sites. It's perfectly acceptable to refer to them, especially as your reference was simply to explain where you got the code from. All good, but we don't allow the hyperlink.

ADils:
I have found the CreateRelationships for DAO
As for the comment about accuracy. This was a reference to the quoted part of your question. The correct reference was CreateRelation. I was merely trying to get you, and others that may read these comments, to understand the importance of reporting your questions accurately and clearly. It would have saved me a bit of time and effort, which I'm sure you would have intended had you realised how such a small carelessness can waste effort expended on your behalf. In many cases, such effort can also avoid wasting of the OP's time too, if they are dependent on the expert for an answer.

That said, in direct response to your question, you clearly gave consideration to how you expressed the question, which I noticed, and I have a lot of respect for that, particularly in a first-time poster. The comment was not intended to put you off posting by any means, but simply to bring the issue to your attention.

BTW. From what I can see of the code it is a good illustration of the requirements for handling this issue. You only include the first two fields in the code posted, but I'm sure that's for brevity and it illustrates the situation just as well as for three.
Apr 23 '12 #5

P: 3
Hello NeoPa, thank you for the explanation. I will take care of that.

I included only the first two fields because I included those fields in the question that I asked, but three works just as fine (just tested).

Note to other users: Close and open the relationships window to see changes after code execution.
Note 2:
Expand|Select|Wrap|Line Numbers
  1. Set relatingField = Nothing
to be added for cleaning up.
Apr 24 '12 #6

NeoPa
Expert Mod 15k+
P: 31,417
My bad. I read the table layouts as index layouts and missed the next line.

Nice responses BTW. I can see that you're someone who's easy to work with and thinks before they post. Always a welcome sight.
Apr 24 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.