How to Create Compound Relationship from Code

[ADils]

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

[NeoPa]

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).

[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.

[ADils]

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:

Sub AddCompositeKey()
 
    Dim Db As DAO.Database
    Dim newRelation As DAO.Relation
    Dim relatingField As DAO.Field
 
    Set Db = CurrentDb()
 
    Set newRelation = Db.CreateRelation("REF1", "Master", "Slave" _
    , dbRelationDontEnforce)
    'Fld from Prim table (Master)
    Set relatingField = newRelation.CreateField("Fld1")
    'Fld from Slave
    relatingField.ForeignName = "Fld1"
    'Add the field to the relation's Fields collection.
    newRelation.Fields.Append relatingField
 
    'Fld from Prim table (Master)
    Set relatingField = newRelation.CreateField("Fld2")
    'Fld from Slave
    relatingField.ForeignName = "Fld2"
    'Add the field to the relation's Fields collection.
    newRelation.Fields.Append relatingField
 
    'Add the relation to the database.
    Db.Relations.Append newRelation
    Db.Relations.Refresh
    Set Db = Nothing
 
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.

[NeoPa]

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.

[ADils]

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:

Set relatingField = Nothing

to be added for cleaning up.

[NeoPa]

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.