Only delete 492 at a time

[Brenda Rueter]

User has a new computer with Access 2002, SP3. He wants to delete up to
several thousand records at one time. We seem to have discovered a limit of
492 that can be deleted at once. If he attempts to delete moe than that, he
gets an error that, "could not update, currently locked."

User also reports that when he had Access 97 he could delete an infinite
number at once. Can anyone confirm that this maximum number of 492 is
really all he can delete at once?

He also mentioned that sometimes he can query out the unwanted data, but not
always. "This always worked in Access 97, so I don't understand why it
doesn't work now." Can anyone offer us any insight? TIA!

 

[Allen Browne]

No. There is no such limit.

If he is deleting from JET (Access) tables, there must be something else
going on, such as another open query, form, report, or recordset that is
using the table in some way. He will need to track down the culprit, or
perhaps restart the database so no other processes are left hung.

To delete all records from Table1 in one go, close any other windows that
are open, and then open the Immediate window. Enter:
dbEngine(0)(0).Execute "DELETE FROM [Table1];", dbFailOnError

All records will be gone, unless there is some other issue that stops the
process, such as a related record that prevents the main record from being
deleted.

 

[Ron2005]

Although the number is not magical, it may be co-incidental. Is there
the possibilty that there is a corrup record.

Possibly do a compact and repair on the tables and try again. I have
had a corrupt record stop processes but not be bad enough to corrupt
the entire DB.

Ron

 

[Allen Browne]

Yes, it is possible that the record is corrupt, or spuriously locked (e.g.
ldb left over after a crash)