Record Locks - Server vs. Client

[CindyK]

I have a very small Access 2000 database sitting on a windows 2000 server - it has just one small table in it, maybe 300 records, only 6 or 7 fields. We're using dao code to step through the records and update one field in this table.

If the database is sitting on the client's hard drive, the code runs just fine. Move the database to the server, however, and we get record locking errors - the code hangs right at rs.edit, returning error 3218 - "Could not update; currently locked".

No-one else is in the database. It will step through a handful of records before it decides to lock - locking at different places along the way if we try to run it multiple times.

The code is straightforward (and remember, runs fine on the client machine when the database is on the local hard drive).

Any thoughts? Code follows.
Thanks in advance.

Dim db As DAO.database
Dim rs As DAO.Recordset
Dim i As Long
Dim sRep As String
Dim sRepCompare As String

Set db = CurrentDb
Set rs = db.OpenRecordset("select * FROM Sales order by SalesNo, Amnt DESC")
rs.MoveFirst

'initialize the first record
sRep = rs.Fields("salesNo")

Do While Not rs.EOF
'initialize the rank count for a new rep
i = 1

sRepCompare = rs.Fields("salesNo")
Do While Not rs.EOF And sRepCompare = sRep
rs.Edit
rs.Fields("Rank").Value = i
rs.Update
rs.MoveNext
i = i + 1
If Not rs.EOF Then sRep = rs.Fields("salesNo")
Loop
Loop

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

 

[Ed2020]

Does this work:

    Dim ws As DAO.Workspace
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Long
    Dim sRep As String
    Dim sRepCompare As String
    
    Set ws = CreateWorkspace("NewWrk", "Admin", "", dbUseJet)
    Set db = ws.OpenDatabase(CurrentDb.Name)
    Set rs = db.OpenRecordset("select * FROM Sales order by SalesNo, Amnt DESC")
    rs.MoveFirst
    
    ws.BeginTrans
        'initialize the first record
        sRep = rs.Fields("salesNo")
        
        Do While Not rs.EOF
        'initialize the rank count for a new rep
        i = 1
        
        sRepCompare = rs.Fields("salesNo")
            Do While Not rs.EOF And sRepCompare = sRep
                rs.Edit
                rs.Fields("Rank").Value = i
                    rs.Update
                rs.MoveNext
                i = i + 1
                If Not rs.EOF Then sRep = rs.Fields("salesNo")
            Loop
        Loop
    ws.CommitTrans
    
    rs.Close
    db.Close
    ws.Close
    Set rs = Nothing
    Set db = Nothing
    Set ws = Nothing

If your database is secured you will need to change the username/password parameters passed to the CreateWorkspace function.

Ed Metcalfe.

Please do not feed the trolls.....

 

[CindyK]

Hi Ed -

Sorry for the delay - I don't have access to this database from home. We finally got to try your code this morning, and adding the workspace code and transaction unfortunately didn't make any difference.

Any other thoughts? Thanks.

Cindy