Can't delete more than 500 records at once

[rorymurray]

When trying to delete all records in a table, either manually or by using a macro, if there is more than about 500 records in the table I get the message "Could not update; currently locked".

Is there some kind of setting I can adjust? Or is there another problem?

It happens on more than one table.

I'm using Access 2000.

 

[Betsvigi9]

I've had the same problem and I suspect that it's a memory issue, but I'm not absolutely sure. I solved it by creating a module to execute an SQL statement. It's much faster and I've not had any locking problems since.

For example:

Sub Delete()
Set db = CurrentDb()

db.Execute "DELETE * FROM [YourTable] WHERE [YourField]='YourCriteria'"

End Sub

Then call the sub for example by creating a button and using the OnClick Event.

 

[dschomburg]

I know this is an old message, but I've run into this same issue ... but only with db's that are on a network. If it's local - there's no problem with the amount of records.
I have also noticed that if the DB is on a network, I can't run UPDATE queries .... What's up with that!?!

 

[Maquis]

I've encountered the same problem. I was told by our LAN guys that the issue concerns the number of locks you are allowed to hold on the network. (Maximum 500 locks per user). When deleting records, Access first locks them all and then deletes them all.

I got around the problem by simply creating a query which deletes the records rather than manually highlighting them in a table to delete. Maq
<insert witty signature here>