Problem editing a relationship

[Chris O''Neill]

Hi, folks!

I'm trying to modify a relationship by removing the enforced referential
integrety with cascade updates. Here's my code:

'********** Begin Code *************
Dim db As DAO.Database
Dim rel As DAO.Relation

Set db = DBEngine(0).OpenDatabase("bbms_DATA.mdb", , ,
"Jet;UID=someuser;PWD=password")

Set rel = db.Relations!tblFinancialAccountTypestblFinancialAccounts

With rel
.Attributes = dbRelationDontEnforce
End With

db.Relations.Refresh

Set rel = Nothing
Set db = Nothing

'********** End Code *************

When the code runs, it gets as far as the line ".Attributes =
dbRelationDontEnforce" and then I get an Error #3219 - Invalid Operation.

What am I doing wrong?

Any and all help and suggestions will be greatly appreciated!

Regards,

Chris

 

[Marshall Barton]

Hi, folks!

I'm trying to modify a relationship by removing the enforced referential
integrety with cascade updates. Here's my code:

'********** Begin Code *************
Dim db As DAO.Database
Dim rel As DAO.Relation

Set db = DBEngine(0).OpenDatabase("bbms_DATA.mdb", , ,
"Jet;UID=someuser;PWD=password")

Set rel = db.Relations!tblFinancialAccountTypestblFinancialAccounts

With rel
.Attributes = dbRelationDontEnforce
End With

db.Relations.Refresh

Set rel = Nothing
Set db = Nothing

'********** End Code *************

When the code runs, it gets as far as the line ".Attributes =
dbRelationDontEnforce" and then I get an Error #3219 - Invalid Operation.

What am I doing wrong?

The reason that's an invalid operation is because relations
are read only once they are appended to the relations
collection.

Note: when you modify a relation using the Relationships
window, I'm pretty sure that Access deletes the existing
relation and creates a new relation using the changed
values.

 

[Chris O''Neill]

The reason that's an invalid operation is because relations
are read only once they are appended to the relations
collection.

Note: when you modify a relation using the Relationships
window, I'm pretty sure that Access deletes the existing
relation and creates a new relation using the changed
values.

Hmmm.... I have to do this in code (the database has already been
distributed) so, since I can't edit the relationship, the best way to go
about this would probably be to delete the relationship and then recreate it
with out the enforced integrety. Right? If so, could you please post a code
snippet showing how to delete the relationship?

Thanks, Marshall, for your help...

Regards, Chris

 

[Marshall Barton]

Hmmm.... I have to do this in code (the database has already been
distributed) so, since I can't edit the relationship, the best way to go
about this would probably be to delete the relationship and then recreate it
with out the enforced integrety. Right? If so, could you please post a code
snippet showing how to delete the relationship?

VBA Help is one of your very best friends ;-)

This is just some air code to point you in a resonable
direction:

db.Relations.Delete
"tblFinancialAccountTypestblFinancialAccounts"
Set rel =
db.CreateRelation(tblFinancialAccountTypestblFinancialAccounts,parenttable.foreigntable.attributes)
rel.Fields!FKfieldname.ForeignName = "FKfieldname"
db.Relations.Append rel

Use error trapping in case the relation does not already
exist.

An Alternative would be to use SQL DDL statements.