Trying to do something really similar to an old post, but can't make it work (2 Viewers)

[Papa_Bear1]

So,
I'm trying to do exactly the same thing as this post...
https://www.access-programmers.co.u...s-from-an-external-database.86740/post-387504

I'm trying to create relationships in an external database, based on data I've previously saved in a file.
The data saved looks fine... and I'm sure it is syntactically correct since I can create relationships from it - but - when it comes to creating a relationship with more than one field pair, it seems to always create an extra instance (alias) of the particular table.

In particular - looking at their code segment in the post referenced above:

For Each imField In .Fields
  fName = imField.name
  fFName = imField.ForeignName
  taRel.Fields.Append taRel.CreateField(fName)
  taRel.Fields(fName).ForeignName = fFName
Next

I get error 3219 (Invalid operation) on the .Append command.

I don't understand why I can't create an extra field in an already established "relation".

Also, maybe someone can clarify if I'm understanding the mechanism/terminology correctly.
It seems like when they say "relation" it is actually a table (that has relationships), and then the actual relationships (lines between tables) are modeled when you add the fields.

This seems to be at the crux of it because, if that interpretation is right, this would mean I want to create one relation, and then two sets of field pairs, but it won't let me create anything beyond the first set of fields. I can loop through all relations, and treat each one separately, it seems completely unaware when the SAME table is used more than once and creates an alias. Thus, the resulting relationships do not reflect the original.

What am I missing?
Why did their code work, looping through fields and adding, where mine does not?

thanks!!

 

[GPGeorge]

How about a supporting screenshot showing us the tables involved and the fields involved.

 

[Papa_Bear1]

I'm noticing that the code that exports this information does indeed loop through multiple 'fields' for a given 'relation'. So, it seems to be able to read them out, but for some reason, trying to write multiples back is fussing at me.

 

[Papa_Bear1]

Here is the 'original' screenshot of the relationships, and then the one after trying to reconstruct from saved data...
I thought I'd try to simplify and highlight the problem areas... probably not needed, but...

One other thing I'm pondering.
I saw (somewhere) that the 'name' for the 'relation' had to be unique.
I think I overstepped on that by making it unique throughout the output rows - by including info from the fields in that name. I did this because the examples I found also did this - so I figured that must mean it needs to be unique in that scope.

However, after thinking about it a bit, I'm realizing that the original did NOT have this happening. The name of any given 'relation' in the original was only governed by the tables paired. So, I'm going to see if renaming them that way solves this. (I don't think it will as I ignored that name anyway and let the table govern the rebuild process, not the stored relation name, but anyway... will try anything at this point.)

 

[Papa_Bear1]

OK -
So, it looks like it was nothing more than me accidentally closing out the relation too early by appending it before visiting the next set of fields.
Due to this, it was no longer available to add more fields to.

Ugh.

I would add though, that in MS parlance, the "relation" is a table pair, and the uniqueness of that name need only cover the pair of table names. I'm not sure why the sample code I happened upon had gone through some extra work to include fields in that name - it led me down the garden path - ha. So, now I know that a in MS Access Relationships - the 'relation' is a table pair, and then the fields define lines going out from that table.

Exactly which lines go 'with' any given table, I'm still not entirely sure, but I don't care about that at the moment. It is great that they have ensured that direction matters, so that there is no concern with happening upon the same line twice.

Sorry for the distraction.

 

[isladogs]

You might find it worth reading my series of three articles on how relationships work and different ways of creating them
This is the first in the series:

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

isladogs.co.uk

 

[Papa_Bear1]

You might find it worth reading my series of three articles on how relationships work and different ways of creating them
This is the first in the series:

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

isladogs.co.uk

Excellent!
Yes - thank you ---- will do!