VBA Creating Relationships

[Flank]

Greetings all, first time on these boards.

I have been desperately trying to figure out a way to create relationships using VBA as I have a load of them to do.

I have a base code built that I need to replicate, but I am getting hung up on a small error that is occurring.

I have a main table called Project that has a primary key field called "ID".

This table is linked to roughly 70 other tables. In each of the other tables the primary key field is called "ID".

So I want my relationships to be as such:
Project:-->Civil
Project:-->CivilDesign
Project:-->Electrical
Project:-->ElectricalDesign
Project:-->Mechanical
Project:-->MechanicalDesign
etc...

Below is the code I have gotten so far.

Option Compare Database
Option Explicit
Sub ProjtoBCivilMH()
Dim dbs As DAO.Database
Dim tdf1 As DAO.TableDef
Dim tdf2 As DAO.TableDef
Dim Rel As DAO.Relation
    Set dbs = CurrentDb
    With dbs
        Set tdf1 = .TableDefs!Project
        Set tdf2 = .TableDefs!BCivilMH
        Set Rel = .CreateRelation("ID", tdf1.Name, tdf2.Name, dbRelationDontEnforce)
        Rel.Fields.Append Rel.CreateField("ID")
        Rel.Fields!ID.ForeignName = "ID"
        .Relations.Append Rel
        .Close
    End With
End Sub

This builds the relationship just as I want it to, but but when I go to execute the code again but changing the foreign table, I get an error stating: "Object 'ID' already exists.

This is the code for the next section:

Sub ProjtoBArchitectMH()
Dim dbs As DAO.Database
Dim tdf1 As DAO.TableDef
Dim tdf2 As DAO.TableDef
Dim Rel As DAO.Relation
    Set dbs = CurrentDb
    With dbs
        Set tdf1 = .TableDefs!Project
        Set tdf2 = .TableDefs!BArchitectMH
        Set Rel = .CreateRelation("ID", tdf1.Name, tdf2.Name, dbRelationDontEnforce)
        Rel.Fields.Append Rel.CreateField("ID")
        Rel.Fields!ID.ForeignName = "ID"
        .Relations.Append Rel
        '.Relations.Refresh
        .Close
    End With
End Sub

As you can see the code is almost identical except at Set tdf2.

Can anyone lead me in the right directions on how to fix this so I can run this accross all my tables?

 

[VilaRestal]

I think that would be the name of the relationship CreateRelation("ID" that it's complaining about (the szRelationship field in the MSysRelationships table). I think every relationship needs to have a unique name.

You'll need to change ID to something like:


Set Rel = .CreateRelation(tdf1.Name & tdf2.Name, tdf1.Name, tdf2.Name, dbRelationDontEnforce)

And you may still need to check that name doesn't already exist first

By the way, as ID is the primary key in the other tables, this will be creating lots of one-to-one relationships with those other tables. Are you sure that's right? It's an unusual structure. Normally most relationships are one-to-many. (Just because two tables have a field called ID doesn't mean they should be related.)

But perhaps you're sure - forgive me for checking.

Also, any reason not to enforce them? Unenforced relationships are hardly worth this sort of effort imo.

 

[VilaRestal]

And are you going to have one sub for each relationship?

(Sub ProjtoBArchitectMH())

A: That's hardly easier than drawing the relationships by hand.
B: Much better to reuse the code and just loop through a list of table names and change
Set tdf2 = .TableDefs!BArchitectMH
each loop

 

[VilaRestal]

And lastly don't .Close dbs, at least not within your loop and there's no need to at all.

 

[VilaRestal]

Something like:

Sub ProjtoAll()
    Dim dbs As DAO.Database
    Dim tdf1 As DAO.TableDef
    Dim tdf2 As DAO.TableDef
    Dim Rel As DAO.Relation
    Dim i As Long
    Set dbs = CurrentDb
    With dbs
        For i = 1 To 100
            Select Case i
                Case 1
                    Set tdf2 = .TableDefs!BCivilMH
                Case 2
                    Set tdf2 = .TableDefs!BArchitectMH
                Case Else
                    Exit For
            End Select
            Set tdf1 = .TableDefs!Project
            Set Rel = .CreateRelation(tdf1.Name & tdf2.Name, tdf1.Name, tdf2.Name, dbRelationDontEnforce)
            Rel.Fields.Append Rel.CreateField("ID")
            Rel.Fields!ID.ForeignName = "ID"
            .Relations.Append Rel
        Next i
    End With
End Sub

Just add a new Case for each table.

But all that's assuming my misgivings about these one-to-one unenforced relationships are unfounded.

 

[Flank]

Thank you for the reply, I greatly appreciate it.

The reason for so many 1 to 1 relationships, and I could be wrong in my way of thinking is because of the amount of fields in each table.

For Example, my main form is Project, this contains the main record number ID (auto-generated). It also has project specific details such as location, name, date type. The other tables are all related to engineering disciplines. So there is Mechanical, electrical, Architect, structural, Design etc... Each one of these tables has 61 fields. The first being the ID (auto-generated) that relates it to the project and the next 60 representing 1 months worth of manhours.

In essence, all the engineering tables with an ID of 1 are related to the Project table with the ID of 1 hence the reason I built it with so many 1 to 1 relationships. As I said, I could be wrong in my way of thinking, so please correct me if there is a more proper way to arrange it.

In a previous database, I was running into a problem with subforms not keeping up with the primary key when a new record was created in the main form.

For example.

I filled in the data on the main form and there were 3 subforms. on 2 of the subforms I entered data, on the third I may not have anything to put in there so it was left blank.

Then when I started a new record, and finally went to enter data into that 3rd subform, it was behind on the the ID primary field.

So I wrote a little code that after some data was put in the first box on the main form, it then populated the subforms with zero's to maintain the the primary key integrity across the forms.


Anyhow, I tried you piece of code and that worked for creating the next relationship.

I misquoted myself earlier, there are actually 231 total tables. I had built out a little formula in excel that would generate each each case and then wrote another section that built all Call() arguments, but a loop would be much easier, I am not very strong in VBA so I tend to go about things the long way sometimes.

Since I have so many tables, I suppose I would write the code to say:
For i = 1 to 100
and change it to
For i = 1 to 231

 

[VilaRestal]

OK, working backwards:

Yes you would change it to For i = 1 to 231 (or more, the Case Else will just exit the loop when it gets to a number you haven't assigned a table to)

Using Excel to build repetative code like this is quite a good method. However, it's probably indicative of something bad in the design that you need to look at.

231 tables is quite a lot to be related to this one table.

Are all the fields in these 231 tables different? Could not some or all of them be consolidated into one table, which would then have a many-to-one relationship to the Projects table? It sounds like they're not well normalised. It's not impossible for complex databases to have so many tables with one-to-one relationships but it's far more likely that there is something awry in the table design.

And if you're going to be making 231 relationships programmatically like this you want to be sure you don't want to enforce them before you start. To change your mind after will require more code to remove and recreate them or modify them. As I said, unenforced relationships are of little practical benefit.

 

[spikepl]

You narrative sets off all possible and even impossible alarms. Resolving a user interface problem by rearranging the data to fit the user interface is reverse of what should take place.

What is 60 columns doing in one table? Time-in time-out for each day?
What are 231 tables doing recording the same type of data? And presumably remade after 1 month?

Is this a basic hour-keeping application? Then each record of one and the same table should just contain one number giving hours, and then date, and to which discipline/unit/project the records belongs. Or something like that.

Any specific reason why all this is laid out as you describe?
I think you can get a much simpler data structure - just say what data you store. Creating separate containers for same kind of data is a severe normalization error, that will make it very hard to do anything with the data - process/accumulate/aggregate/analyze/output.

Update: A wild stab in the dark - is all this determined by receiving a monthly Excel sheet containing a tally of hours from each unit? If so, then just because you get data served in a certain way does not mean you should store them in a database in the same fashion. You can easily import a sheet to a staging table and from that store the data the way it ought to be stored.

 

[VilaRestal]

Indeed, Spike is right: I was being overly polite. It sounds like a terrible structure and you're starting to reap the (what's the opposite of rewards?) of it.

In all probability all you need is one table other than Project that would store all the manhours for all the disciplines. Et voila: you can draw the single one-to-many relationship by hand.

Even if you stick with the 60 fields for each day (not good but I can understand someone doing that to make life easier when creating a front end for it) all you need is another field to show which discipline the record is for.

And if more disciplines ever get added - that's nothing. But with your current design you'll have to create another table for it and another relationship and possibly queries that include it.

 

[Flank]

Well its a little complicated, and I am about to get sucked into a meeting.

I will respond later this evening or in the morning and try to state what I am trying to accomplish a little more clearly.

I certainly want to follow the most proper route to prevent myself from building some monster that barely can function.

I greatly appreciate all the help and feedback you have given so far from both of you.

Cheers.

 

[Flank]

Ok here goes...

The model that I am making is for the scheduling of manhours over a project.

It starts in the project table where basic information is filled out such as project name date of start, project type, client, total hours, and projected hours.

The user will then go to a set of tabs where they add options that will increase or decrease the total hours in the project. Depending on the item they select it will increase or decrease the total hours in each separate discipline.

So there are initially 23 disciplines. In each one of the discipline tables, there is an ID field and then fields titled Month1, Month2, Month3....Month60. The 60 was chosen as that is the longest a project will run for.

I have another table called ProjectCurve that has an ID field, discipline and Month 1-60. The values stored in the months are percents.

When the user is done selecting options, they click a button that then takes the total hours for each discipline and multiplies it times the corresponding monthly percentage in the ProjectCurve table and stores the value in the appropriate discipline table.

Now there are 5 different locations that these disciplines can be so each location has 23 discipline tables, that brings the total tables to 115. Then based on the number of hours in a month, it calculates the number of FTE's (Full Time Employee) so that doubles the tables to 230.

I hope that makes sense. This is a link to an image of the form.
recrovita. com/images/M_images/manhour.jpg

 

[spikepl]

You can make the user interface in whichever way you desire. But that does not dictate which way the data should be stored. To equate what is displayed in a form with what is stored in a table will in this case lead you into untold problems. To process all this data in queries or reports will be a selfinflicted nightmare.

Attach your picture here. Read the sticky at the top of the forum.

 

[VilaRestal]

Yep, you definitely do not want a separate table for each discipline. One table with a field showing to which discipline the record relates. You can then emulate those 23 tables just as queries of this table filtered for each discipline.

Whether Project Curve needs to be a separate table is debatable. It looks like it follows the same structure too. So another field in your one table to say whether the record is a curve record might do it. Again, a filter on that field would then emulate the project curve table.

And another field to show which location the record relates to. From 230 tables down to one or maybe two.

You don't need 60 fields for the months. A record per month and field to show which month the record is for. However, you may want to stick with 60 fields to keep the form design simpler: The simplified and improved table design would be weighed against a more complex form design (code) that would translate records into what look like fields.

So, I think leave the 60 fields for months for now but certainly consolidate the 230 tables into one or maybe two if Curve needs to be separate.

 

[VilaRestal]

You can make the user interface in whichever way you desire. But that does not dictate which way the data should be stored. To equate what is displayed in a form with what is stored in a table will in this case lead you into untold problems. To process all this data in queries or reports will be a selfinflicted nightmare.

That is indeed true but it's very easy for someone who is expert in designing and coding forms to say and not so easy for the less experienced to achieve.

That is why I think, at least at first, just concentrate on consolidating the tables into one or two tables (plus the Projects table) and leave the field per month structure.

For consolidating the tables, the change in design of the form will be quite straightforward: you'll just be replacing recordsources with filtered versions of this new table.

The same cannot be said for consolidating the month fields. That will require some pretty complex and sophisticated code in the form.

I think take it one step at a time.

 

[Flank]

What you are explaining to me now makes a lot more sense. I will work on rolling up all the tables into a few simplified ones using the method you described.

I also think I will leave out the long vertical columns in the form and just stick with discipline totals then use a query later on to generate a report of how it looks monthly.

This is definitely going to make things a lot easier. Sometimes it just helps to get a second set of eyes on your project.

I greatly appreciate your input on this topic, it has been most informative and will definitely help me building future database structures.

 

[Pat Hartman]

I'm going to date myself with this and I'm not sure if the non-US folks will even recoginze the quote but here it is anyway.

"Hi, this is my brother Darryl, and this is my other brother Darryl."

Naming all your PKs "ID" makes about as much sense as naming all your children "Darryl".

I'm glad you are going to work on consolidating the tables. You'll find everything so much easier. I hope you are also going to remove the 60 columns for hours. It may look easier to create an interface but do you really want to keep writing calculations that add those columns? When you work with Excel, your data and presentation are totally interdependent. That is not the case for a relational database. Optimize the data so it is easy to work with and then create crosstab reports to make it look just like the old spreadsheet.

 

[gemma-the-husky]

have you sorted this, flank?

i think this is the error

when you create a relation, access builds a hidden index in the many table, for the FK. (attribute of foreign)

now if you already have an index with the name that access wants to use, it cannot create the index (and therefore cannot create the relation). i think that is happening.

delete your manual index, and try again. then it should work.

 

[twoplustwo]

Hi Flank, read up on data normalisation. This will help give context to the salient advice offered above.