How to delete 50000 records from an Access table using ADO ?

[HelgeLarsen]

I have a database table in MS Access with many records.
I use some VBA in Excel to delete some of these records.
But when I try to delete many records I get the error :
File sharing lock count exceeded.
Increase MaxLocksPerFile registry entry.

My VBA looks like this :

' Select all records, where Key1 starts with "A".
SQL = "SELECT [MYTABLE].[Key1] FROM [MYTABLE] WHERE ((([MYTABLE].[Key1]) Like ""A%""));"

Set RS = New ADODB.Recordset
' Open recordset
RS.Open SQL, CN, adOpenKeyset, adLockOptimistic, adCmdText
N = RS.RecordCount ' Number of records
Do While Not RS.EOF
RS.Delete adAffectCurrent ' Delete record
RS.MoveNext ' Next record
Loop
RS.Close ' Close recordset
Set RS = Nothing

The recordset is correct. I get 50,000 records as I should.
When I have deleted approx. 9300 records the error occurs.

I do not find it a good solution to increase the indicated registry value.

Could I write the code in some other way to avoid the problem ????

 

[kimtp]

I use this to clear all records:
cn.Execute "UPDATE Table1" & _
" SET Field1 = '', Field2 = Null, Field3 = Null," & _
" Field4 = False"

where the first 3 are strings and the last boolean.

Maybe if you added a WHERE statement such as:

Where Recordnumber > {number} and recordnumber < {number}.

I have not tried the where statement

 

[pweegar]

Why not run a SQL delete query in access????? Use a where clause to delete only those records you want deleted.