Delete Query doesn't work in ACCDE version (1 Viewer)

[cnstarz]

I have a front-end database that I deploy as an ACCDE for the end-users. When a user deletes a record within a subform, it'll prompt and ask if they want to just "unlink" the child record from the parent record or if they want to permanently delete the child record. If they choose to delete the child record entirely, VBA will construct the delete query and run it via CurrentDB.Execute method.

This works perfectly in the master ACCDB database, however, in the ACCDE version, the delete query throws an error saying that the record cannot be deleted due a lock violation. I've been banging my head trying to figure out why the ACCDE version would create any kind of record lock when the ACCDB version doesn't, and I can't figure it out! I've tried using the DoCmd.RunSQL command instead -- same error. I've decompiled the ACCDB version, recompiled it, and republished an ACCDE version -- same exact thing. I've debug.printed every step of the sub, and the delete query's SQL statement that is created w/VBA in the ACCDE version matches the ACCDB's version perfectly. I've taken that delete query SQL and plugged it into an actual query, and it runs perfectly in the ACCDB version. I've checked the parent form and subform to ensure they don't lock any records, and they don't. I've also ensured record locking is disabled in Access. :banghead:

My next experiment will be to create and save a delete query that gets published with the ACCDE version to see if that errors out too.

Has anyone ever experienced this where an exact ACCDE copy of an ACCDB database creates a record lock that prevents records from being deleted/edited? I am stumped!

 

[Ranman256]

is the user ON the record, when they delete it?
ACCDE may not be as forgiving.

 

[cnstarz]

They're on the related record on the subform. Example:

Parent Form is on record called "Shapes"
Subform (in datasheet view) shows the following records: Square, Circle, Triangle, Rectangle

User selects the "Square" record (highlighting the entire row) and hits the delete key on the keyboard. A custom message box opens asking if the user wants to delete the link between the Shape and Square records, or if they want to delete the entire Square record. User selects "Permanently Delete" (the entire Square record). This is where the VBA delete query runs that deletes Square's record (with cascade delete).

When they choose "Permanently Delete" it first deletes Square's subform record (from the join table that joins Main form's recordset to the Subform's recordset), then it deletes Square's the main record from it's main table.

 

[arnelgp]

do you have Link Master fields/Link Child fields on your subform?
if you have, you cannot delete the "Shapes" when at least a record is still not deleted from subform.

 

[cnstarz]

Yep, they are linked, but it works in ACCDB version, just not ACCDE version.

Following up on my saved Delete Query experiment... I'm using a tempvar variable to store the record's Primary Key, but when VBA executes the query, I get a "Too few parameters" error. :banghead:

Delete Query looks like:

DELETE *, tbl_Targets.Target_EID
FROM tbl_Targets
WHERE (((tbl_Targes.Target_EID)=[TempVars]![Delete_Target_EID]));

If I run the code up to the point where it saves the TempVars value, then run the delete query manually, the delete query works perfectly. But if I have VBA run it (db.Execute "qry_Delete_Target_EID", dbfailonerror), it'll give me an error saying "Too few parameters". Gahhhh, I can't win!

Edit: I ditched the tempvars idea in favor of a function. I still get the lock violation error with only the ACCDE database! :banghead:

Why would the ACCDE version create a record lock but not the ACCDB version?

 

[cnstarz]

I've also tried deleting via recordset.delete method, and I still cannot delete the record in the ACCDE version while I can in the ACCDB.

 

[jdraw]

Perhaps there is a misunderstanding.

If you have a Parent record with child records and you have a relationship between them (eg 1 to M), then you can not delete the Parent record before you delete all of the child records.

Access will not allow orphan children.

 

[cnstarz]

You can delete parent records provided you have cascade delete enabled in the relationships. It would delete the would-be child records automatically. That's why cascade delete exists, right?

The thing is, the delete query works in the ACCDB version. It works. Just not in the ACCDE version for some reason -- due to record lock violation. It says it cannot delete the record due to a record lock violation. In the ACCDB version, it does not do this. I'm not sure why.

 

[JHB]

What is your backend database - SQL-Server/MS-Access or ??
Can't you create a test database with:

• Only some two (or few) tables and some test data. Keep it in the same test database.
• A form with a button with the "Delete" code.

Convert it to an ACCDE version, test if it runs okay or not.
If okay - then split it in a Frontend-/Backend database, test if it runs okay or not.
For both cases, if it not runs okay, post the databases.