Referential Integrity Relationships - in vba (1 Viewer)

M

myrt

Registered User.
Local time
Today, 22:25
Joined
Apr 22, 2015
Messages
34
Hi! Hope you're not still fed up with me :p.
I'd like to appeal to your knowledge.
I'm thinking about creating temporary (while db open) relationships between tables, since transfering tables is possible only if no linked relationships are present. However it's important to preserve in some cases referential integrity.
Looking online, I found this code:

Code: 
Public Function CreateRelation(primaryTableName As String, _
primaryFieldName As String, foreignTableName As String,  _ 
foreignFieldName As String) As Boolean


    On Error GoTo ErrHandler


    Dim db As DAO.Database
    Dim newRelation As DAO.Relation
    Dim relatingField As DAO.Field
    Dim relationUniqueName As String
   
    relationUniqueName = primaryTableName + "_" + primaryFieldName + _
                         "__" + foreignTableName + "_" + foreignFieldName
   
    Set db = CurrentDb()
   
    'Arguments for CreateRelation(): any unique name,
    'primary table, related table, attributes.
    Set newRelation = db.CreateRelation(relationUniqueName, _
                            primaryTableName, foreignTableName)
    'The field from the primary table.
    Set relatingField = newRelation.CreateField(primaryFieldName)
    'Matching field from the related table.
    relatingField.ForeignName = foreignFieldName
    'Add the field to the relation's Fields collection.
    newRelation.Fields.Append relatingField
    'Add the relation to the database.
    db.Relations.Append newRelation
   
    Set db = Nothing
   
    CreateRelation = True

       
Exit Function

Even with the comments I don't really understand what exactly this code does or doesn't do.
I tried the code. It seems (?) to create a relationship. Since the relationship doesn't show up in the relationships table, I'm not really sure if it's there.
It would be nice if someone explained to me:
- why it's not visible among other relashionships (or is it me?? :confused: maybe, it wasn't the code that was working but the query...)
- how to implement referential integrity
- what's the behaviour of this supposed relationship. Is it permanent or not? If not when is it deleted?

If someone has a link to some easy to understand explainations, I'd gladly read them.
Keep in mind that in front of you is a novice. :) Thanks!
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 22:25
Joined
Nov 3, 2010
Messages
6,142
Back up and tell us the story without any technobabble: what is the original issue you are trying to handle using your solution?
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:25
Joined
Sep 12, 2006
Messages
15,640
what the code does is this

- a relation is an object in itself that has a name, and details of the tables involved in the relation, and the relation type (the same choices you get when you create it manually)

- there are then a series of relation-field-pairs if you will that identify the join fields between the two tables


that is what your code is managing.
 
M

myrt

Registered User.
Local time
Today, 22:25
Joined
Apr 22, 2015
Messages
34
gemma-the-husky said:
what the code does is this

- a relation is an object in itself that has a name, and details of the tables involved in the relation, and the relation type (the same choices you get when you create it manually)

- there are then a series of relation-field-pairs if you will that identify the join fields between the two tables


that is what your code is managing.
Click to expand...

That much I had already gatered. Thanks for answering. I will try to read more on relationship objects on the net and be more prepared next time.
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:25
Joined
Sep 12, 2006
Messages
15,640
the relationship you create will show up in the relationships pane. You may need to show the tables to see it graphically, as they do not show by default. That's probably why you can't see it.

The relationship indicates how tables are linked. When you put both tables in a query, the link according to the relationship will be made automatically in the query.

If the link is specified to maintain relational integrity then you cannot add/delete records that would not be consistent with such RI.

It's probably quite unusual to create relationships in code. Much easier in the relationship pane.
 
M

myrt

Registered User.
Local time
Today, 22:25
Joined
Apr 22, 2015
Messages
34
spikepl said:
Back up and tell us the story without any technobabble: what is the original issue you are trying to handle using your solution?
Click to expand...

There are two almost identical databases. On one we make experiments (let's call it AA), on the other data is inserted (BB). When the experiments are successful, the data tables (BB) are transfered into this database (AA) in order to have new functions. A copy is made and on this copy data continue to be inserted.
To overwrite tables in the receiving db (AA), the relashionships are to be deleted and later recreated. It's becoming tiring to do this every time.
So I thought a possible solution could be to have relations that disappear the the db is closed. This could be done through code.

I imagine other solutions possible as well. It's just that I didn't really what to link the two databases since they don't remain the same. Better yet, one is almost always the same (AA), the other (BB) is from time to time a copy of the current one (AA) implemented with the data tables.

Suggestions?
 
M

myrt

Registered User.
Local time
Today, 22:25
Joined
Apr 22, 2015
Messages
34
gemma-the-husky said:
the relationship you create will show up in the relationships pane. You may need to show the tables to see it graphically, as they do not show by default. That's probably why you can't see it.

The relationship indicates how tables are linked. When you put both tables in a query, the link according to the relationship will be made automatically in the query.

If the link is specified to maintain relational integrity then you cannot add/delete records that would not be consistent with such RI.

It's probably quite unusual to create relationships in code. Much easier in the relationship pane.
Click to expand...

That's the problem. The relationship doesn't show up in the relationship pane. I click 'Show All Relationships' and still nothing is visible. I'm not sure if it's normal or the relationship for some reason wasn't created. So I'm looking for information about relationships created through code.
I agree with you. It's significally simplier to manage relationships in the pane.
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 22:25
Joined
Nov 3, 2010
Messages
6,142
You'll probably need to disambiguate objects and preface all db objects by Dao, so fx

Dim X as Database

becomes

Dim X as Dao.Database

and so on
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:25
Joined
Sep 12, 2006
Messages
15,640
myrt said:
That's the problem. The relationship doesn't show up in the relationship pane. I click 'Show All Relationships' and still nothing is visible. I'm not sure if it's normal or the relationship for some reason wasn't created. So I'm looking for information about relationships created through code.
I agree with you. It's significally simplier to manage relationships in the pane.
Click to expand...

Are the tables linked or local. You can't create relationships in a linked table, although that should error, and I can't see any error handling

Have you definitely added the tables affected to the relationship pane. You may need to do this to see the relation.
 
M

myrt

Registered User.
Local time
Today, 22:25
Joined
Apr 22, 2015
Messages
34
gemma-the-husky said:
Are the tables linked or local. You can't create relationships in a linked table, although that should error, and I can't see any error handling

Have you definitely added the tables affected to the relationship pane. You may need to do this to see the relation.
Click to expand...

Just to experiment the code, I went to the relationship pane in the same database where the function posted above was saved and deleted an existing relationship with referential integrity (So the table is definitely added to the pane. However, even if it wasn't added, I believe when 'Show all relationships' is clicked all relationships and therefore connected tables should appear).
Then I called the funtion inside the Form_Load event of a form that used that table, opened the form and went to see if the relationship showed up in the pane. Not sure how else to test the function.

The problem could be the usage I made of the function. However since the full understanding of this function still evades me, I just tired in the most logical way to me.
 
gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:25
Joined
Sep 12, 2006
Messages
15,640
OK

I just noticed you do have a error handler

I would put a breakpoint in there and step through - see if it errors out. Maybe it does, and your error handler just continues without reporting the error.
 
You must log in or register to reply here.

Similar threads

M
Solved A lookup table, attempted referential integrity, and the puzzle of the no unique index error
Replies
6
Views
421
Missomissou
M
C
referential integrity
Replies
14
Views
878
CJ_London
C
K
Referential Integrity
Replies
6
Views
607
gemma-the-husky
gemma-the-husky
N
Solved How to encode some field from Json string into the related tables in MS Access
Replies
17
Views
763
nector
N
B
VBA problem relating to SQL linked table
Replies
6
Views
520
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom