472,782 Members | 1,061 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,782 software developers and data experts.

How to Create Compound Relationship from Code

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

6 2163
32,534 Expert Mod 16PB
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
32,534 Expert Mod 16PB
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
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()
  3.     Dim Db As DAO.Database
  4.     Dim newRelation As DAO.Relation
  5.     Dim relatingField As DAO.Field
  7.     Set Db = CurrentDb()
  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
  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
  25.     'Add the relation to the database.
  26.     Db.Relations.Append newRelation
  27.     Db.Relations.Refresh
  28.     Set Db = Nothing
  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
32,534 Expert Mod 16PB
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.

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
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
32,534 Expert Mod 16PB
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

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

Similar topics

by: Brian S. Smith | last post by:
Hi gang, Please help. I've been through the Access help, searched the Web, and I can't seem to get a straight answer. As the Subject line suggests, I want to run a fairly simple VB/Access Sub...
by: Dan | last post by:
Hi, I deleted the form created by VWD in the aspx file (because i know there may only be one server-form) and I tried to create in the code-behind file a form like: dim t as form but it...
by: careera | last post by:
Can we create PHP codes for the SQL tables? In MySql there's an option (Create PHP code) what is that for? I tried but got nothing in return!! Can anyone guide me!
by: Galka | last post by:
Hello Why do you think a create table query doesn't create table from the following code? Set myQuery = myDB.QueryDefs("qryCON absent adults previous day log") myQuery.Execute If I execute...
by: jl2886 | last post by:
Hello. I have two tables and some of the fields are the same. I have one primary key for both, and that is case number. One table is what is called Master_Log...this is the main log. I have...
by: Yitzak | last post by:
Hi after adding a field to a table through VBA How do I create a foreign key constraint/relationship between this field and a field in another table - and enforce referential integrity through...
by: chuck gregory | last post by:
In "tools" "relationships," I link identically titled fields in two databases by clicking and dragging one title to the other. The box asks me if I want to create the relationship and specifies both...
by: John Devlon | last post by:
Hi, Does anyone know how to create a new user by code? Using the default control works great but I need a solution using code... something in vb.net not c# Thanx John
by: readnlearn | last post by:
hii i hav a problem with thsi code check it once if (!this.IsPostBack) // Create a random code and store it in the Session object. this.Session = GenerateRandomCode(); else
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.