Problems with DELETE action

  • Thread starter Thread starter PSI
  • Start date Start date
P

PSI

I have the following (simplfied) Access database structure:

table Entities
============
ID - autonumber, PK
<various other fields here>

table Users
============
EntityID - number, PK, FK to Entities
<various other fields here>

table Items
===========
ID - autonumber, PK
EntityID - number, FK to Entities
<various other fields here>

RFI is turned on for all: (1 to many), cascade deletes, cascade
updates.


In the app I get a users recordset as :
recordset->Open("SELECT * FROM Users", connection,

adOpenKeyset,adLockOptimistic,adCmdText);


I display the data ( from the first record) and when a user presses
delete button I issue the following actions:

recordset->Delete(adAffectCurrent);

_bstr_ t bstrQuery="DELETE FROM Items WHERE EntityID =
<Users.EntityID ( of the current record )>"

VARIANT vtRecordsAffected;
vtRecordsAffected.vt = VT_I4;

connection->Execute(bstrQuery, &vtRecordsAffected,
adExecuteNoRecords);


This works fine. Now the problem:

If I move off the first record with MoveNext ( or MoveLast etc )
then the DELETE action on Items causes a "Cannot update: Currently
locked " error. Furthermore if I move back to the first record the
DELETE action also fails. ( ie. only works if I never move off the
record). Its got me twisted because the deletion of the User record
always succeeds and I don't see how the Items table gets locked.

( Note: no other users)

Can anyone shed some light on this?

(Note: In practice I really wanted to just delete the Entity record
and the corresponding User and Items would all get cascadde deleted
but I had some problems with this as well - somehow the Entities
autonumber field got reset and then duplicate key errors started
popping up - but thats another story ).

Environment: Win2K, Jet4 sp7, MDAC 2.7, VC++ 6.0 SP6

Thanks
Frank
 
PSI,

Did you write it correctly, that EntityID is the Primary Key field in
Users table? If so, the relationship between Entities and Users can't
be one-to-many. Also, as a side issue, Cascade Updates set in these
relationships can't be correct, as Autonumber data can't be updated.

Not a direct answer to your question, I know, but perhaps helpful
anyway.

- Steve Schapel, Microsoft Access MVP
 
Hello Steve:

EntityID is the PK in users but you are right that I erroneously
stated the relations are 1 to many. Entity to Users is 1-1, Entity to
Items is 1 - many. ( eg its essentially a subclass scheme where the
intent is that a User can have many Items. )

I had tried turning off cascade updates but the problem still
persists.

Any other thoughts would be welcome.

Thanks for responding.
Frank
 
Back
Top