Record locking woes

[torok]

Hi there,

I use Excel as a frontend to Access, partially because my client *really* wanted to edit the Access database tables in a spreadsheet environment.

My code imports sections of the tables and puts them in Excel worksheets. The client makes changes, and then my VBA code dumps the changed data back into Access. One of the tables is about 15,000 rows, and the update code is choking on it with a MaxLocksPerFile error. I set the maxlockperfile in the registry to 66000, but it made no difference. Here's the code:

Sub updateTable(cn As ADODB.Connection, sname As String, tname As String, ParamArray columns() As Variant)
'Updates a single table in the database
'sname = sheet name
'tname = database table name
'Columns array should be the columns you want to export
'Column A is the ID column

Dim rs As ADODB.Recordset, r As Integer
  
    'The following code makes no difference
    'DAO.DBEngine.SetOption dbMaxLocksPerFile, 66000
    
    Set rs = New ADODB.Recordset
    
    Worksheets(sname).Activate
    rs.Open tname, cn, adOpenDynamic, adLockPessimistic, adCmdTable
    r = 2 ' Start exporting at row 2
    Do While Len(Range("A" & r).Formula) > 0
        With rs
            .Find "ID=" & Range("A" & r).Value
            If Not .EOF Then
                For Each c In columns
                    .Fields(Range(c & "1").Value) = Range(c & r).Value
                Next c
            End If
        End With
        r = r + 1
    Loop
    rs.UpdateBatch
    rs.Close
End Sub

It dies in the "For each c in Columns" loop. Any suggestions??