Error "File sharing lock count exceeded" using ADO in Access 2003

[AndyJS]

I am processing an Access table with over 100,000 records. When I run the following module (from a seperate Access database) I get the following error message:Run-time error '2147217887 (80040e21).

Anywhere from 9,000 to over 17,000 records get processed but the rest are missed.

The module looks like the following:
Any help would be appreciated.
****************************************************
Option Compare Database

Sub Update_Test2Jan24()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim strConn As String
Dim Position As Long

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = " & _
"C:\AccessTest\2003Jan24tests.mdb"

Set conn = New ADODB.Connection
conn.Open strConn
Set rst = New ADODB.Recordset
With rst

.Open "Select * from V2Test2Jan24", _
strConn, adOpenForwardOnly, adLockOptimistic

Do While Not rst.EOF

If .Fields("Chgs").Value > 0 Then
.Fields("GrossUnits").Value = .Fields("Units").Value
.Fields("GrossChgs").Value = .Fields("Chgs").Value
ElseIf .Fields("Chgs").Value < 0 Then
.Fields("VoidUnits").Value = .Fields("Units").Value * -1
.Fields("VoidChgs").Value = .Fields("Chgs").Value * -1
End If

rst.MoveNext

Loop

rst.Close

End With
Set rst = Nothing
conn.Close
Set conn = Nothing


End Sub

 

[Remou]

This may help:

http://support.microsoft.com/kb/815281

 

[cmmrfrds]

Instead of doing the update in code which is quite inefficient, write an update query. This will be more efficient and get around any ADO limitations.

 

[AndyJS]

cmmrfrds:
I hadn't tried doing much in the update query. I will take a look at that.
Thanks for the suggestion.