Problem editing a relationship

  • Thread starter Thread starter Chris O''Neill
  • Start date Start date
C

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
 
Chris said:
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?
Click to expand...


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.
 
Marshall Barton said:
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.
Click to expand...

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
 
Chris said:
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?
Click to expand...


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.
 
Back
Top