Error 2501 while trying to delete a record

Question

I have an Access database front-end and I'm trying to put a button on a form to delete the current record. I used the following VBA code to delete the record:

If Me.NewRecord Then
    Me.Undo
    Exit Sub
End If

DoCmd.RunCommand acCmdDeleteRecord

Me.Requery
Me.Refresh

When I run this on records that I inserted into the database with the form, It returns Run-time error '2501' on the DoCmd. However, if I run it on a record that had already existed in the database then the code completes as intended.

Additionally, no one else is accessing this database table yet and I only had the one form open.

When I went to delete them from the linked table manually in access I got the same error but I was able to delete them from the database using SQL Server Management Studio.

What would cause this to happen?

EDIT

I did some more investigating and found that I am unable to edit the new records in in the base table using access either. I get an error about the records being changed by another user.

Have you got a unique key? If you delete and recreate the link do you have the same problem? — Fionnuala, Jan 10 '11 at 20:00
The table is fully defined with a good primary key. I tried relinking the table and I still have the same problem. — Rister, Jan 11 '11 at 14:02

score 1 · Accepted Answer · answered Jan 11 '11 at 22:32

Other than the recommendation to have a timestamp field in the table (SSMA assistant adds this to all tables when you use it to upsize from Access, and it's definitely something I'd recommend), I have some criticism of your code. I'd write it this way:

  If Me.NewRecord Then
     Me.Undo
  Else
     DoCmd.RunCommand acCmdDeleteRecord
     Me.Requery
  End If

The refresh is redundant after a requery, as you already have the most recent data.

Using Exit Sub is helpful for guard clauses on things that aren't mutually exclusive, but in this case you have an either/or -- either your going to delete an existing record or undo a new record. That can be handled within a single If/Then/Else block and then you have a single exit point for your subroutine, which is very helpful in case the code grows more complex in the future.

I added a timestamp and it fixed the problem. Why haven't I ever run into this problem before? — Rister, Jan 12 '11 at 15:20
As long as I've been upsizing Access/Jet/ACE tables to SQL Server, it's been part of the recommendations for the upsizing. It's a necessity because of bound forms, since that's the only way Jet/ACE can keep track of what's been updated and what hasn't (short of comparing all fields, which is not reliable when there are Nulls). — David-W-Fenton, Jan 13 '11 at 02:04

MarredCheese · Answer 2 · 2020-03-22T03:28:33.557

This is not the answer to your specific problem, but regarding the question title of getting Error 2501 while trying to delete a record, another situation where that happens is this:

You try to delete a row in one table that would orphan rows in another table (because the two tables are linked via a foreign key). SQL Server rejects the deletion and Access returns that vague 2501 error code.

In my case, I solved the issue by dropping and recreating each foreign key with ON DELETE CASCADE so that when a row in the "main" table is deleted via Access, SQL Server automatically deletes the corresponding rows in the each "detail" table.

See these questions for more detail on cascade delete:

Error 2501 while trying to delete a record

2 Answers2