relationship deleted - how did that happen? 1

[losthorizon]

Posted this earlier under 'Other Topics,' then found this subject heading; felt it was more appropriate posted here, instead!:

When I create a relationship between any two tables in the relationship window, enforcing referential integrity and cascading deletes, it is created without any error messages.

When I go back in, the two tables are still there, but the line showing the relationship is now deleted. The tables then behave as if there isn't any cascading delete relationship.

I am running Access 2000 and have linked tables that also have relationships. These linked tables appear to be fine and are working okay. Any ideas?

 

[lespaul]

Do you save the relationship before you exit the relationship window?

Leslie

 

[losthorizon]

Yes, of course!

 

[lespaul]

Well, you never know! There are some people around here who would have said "You have to save it too?"



Leslie

 

[losthorizon]

I'm still reading / searching the posts to see if anyone else has experienced this problem. Have found a couple of "deleted relationship" posts, so far, but I'm still looking for an answer.

 

[losthorizon]

I should add that everything was working okay when we were using 'Access 97,' but this has only happened since we converted it over, into an 'Access 2000' database. There are many linked tables in our (insurance-related) database!

 

[losthorizon]

At first, we thought our problem was a "relationship" problem, but that now seems okay, after doing some more checking.

We were having a problem with #deleted showing up in bound controls, though we didn't have this problem with Access 97, using the exact same code. (The code does, in fact, delete a record from the one side of a '1 to many relationship.' The controls are bound to the table on the 'many' side.

The two tables have 'referential integrity' set, 'cascade delete,' and they are related by 'autonumber primary to foreign').

We eventually solved that problem by using a series of me.requery's, dbengine.idle's and doevents. (According to another newsgroup posting, perhaps another method would have been to NOT depend on the autonumber field, but, instead, create a "next Id" table).

But the main issue, and our root problem, STILL goes unresolved:

Every time we attempted to save the database while the
code was running, we received a "You do not have exclusive
access to this database and your changes will not be saved"
message. It pops up, and the #deleted appears in the boxes.

If we type: ? dbengine.systemdb, in the 'immediate' window,
'System.mdb' displays.

If, on the other hand, the design changes were saved without
any errors with the code running, ?dbengine.systemdb
displays: "c:\program files\common files\system\system.mdw.
The #deleted does NOT appear in the boxes -- & everything
works fine!

We have both 'Access 97' and 'Access 2000' on our machines. We already downloaded all the latest patches and updates from the 'Windows Update' site.

The 'Win 2000' Access program is intended to have
NO security, of any sort. From what the 'immediate' window says, I have a strong suspicion that sometimes the
program reads the Access 97 workgroup file (and fails!),
but also sometimes reads the Win 2000 workgroup file
(and succeeds!)

Are the Access 97 and Access 2000 .mdw files located in their correct locations?

How can we make the Win 2000 Access program always read
the Win 2000 .mdw file, ignoring the Access 97 files?
i. e. (C:\Program Files\Common Files\SYSTEM\system.mdw?)

Could it be a registry problem? Thanks in advance.