Discussion Article

Subject:
How to delete 50000 records from an Access table using ADO ?
Name:
Helge V. Larsen
Email (spam proof):

Email the originator of this post
Date:
Monday, June 07, 2004
Hits:
58
Discussion (Terms and Conditions):

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 ????

Sponsored by the best FTP application on the internet: