[RESOLVED -FINALLY :-) ] could not update; currently locked

[Muddy]

I have this intermittent error (could not update; currently locked). Occuring intermittently in a database write loop at random locations.

This has me pulling my hair out as it only happens intermittently (more on larger databases) and at RANDOM locations in the loop ... even when nothing changes from run to run.

This happens on many different computers and many different database files.

Any help greatly appreciated!

The database is an mdb file on the same drive as the app.
error occurs at random update statements.
There is NO multiuser access ... only the single access.
PSUEDOCODE follows:

Dim rst As New ADODB.Recordset
    Dim rst2 As New ADODB.Recordset
    Dim rst3 As New ADODB.Recordset
    Dim rst4 As New ADODB.Recordset
 
        strTemp = "SELECT * FROM aa WHERE bb = 1"
        rst.Open strTemp, cnn, adOpenDynamic, adLockOptimistic
        
        
        strTemp = "SELECT * FROM cc WHERE dd = 1"
        rst2.Open strTemp, cnn, adOpenDynamic, adLockOptimistic
        
 
        strTemp = "SELECT * FROM ee WHERE ff = 1"
        rst3.Open strTemp, cnn, adOpenDynamic, adLockOptimistic
        
 
        strTemp = "SELECT * FROM gg WHERE hh = 1"
        rst4.Open strTemp, cnn, adOpenDynamic, adLockOptimistic
 
for i = 1 to 1000
    rst2.AddNew
    rst2!aaaa = bbbb 
    rst2.Update
 
    rst3.AddNew
    rst3.Fields("cccc") = dddd
    rst3.Update
 
    rst4.AddNew
    rst4.Fields("eeee") = ffff
    rst4.Update
 
    rst.AddNew
    rst.Fields("gggg") = hhhh
    rst.Update
next i
 
    rst.Close
    rst2.Close
    rst3.Close
    rst4.Close
    
    Set rst = Nothing
    Set rst2 = Nothing
    Set rst3 = Nothing
    Set rst4 = Nothing

[si_the_geek]

One method to avoid it would be to wait for the update to complete, eg;

rst2.Update
Do While rst2.adStateExecuting
  DoEvents
Loop

..alternatively, you could use a different method to add the data to the database - see the "how do I add a record" article in the database FAQ's.

[Muddy]

One method to avoid it would be to wait for the update to complete, eg;

rst2.Update
Do While rst2.adStateExecuting
  DoEvents
Loop

..alternatively, you could use a different method to add the data to the database - see the "how do I add a record" article in the database FAQ's.

Thanks Si, but that doesnt pop up on intellisense for me. I typed it in anyway and it did correct the case of the letters in adStateExecuting, but when I try to run it I get "Method or Data Member not found"

[Muddy]

OK changed your code to :
rst2.Update
Do While rst2.status = adStateExecuting
DoEvents
Loop

and it runs, but I still get the error. The value of rst2.status when the problem occurs is 16 ... I dont want to just hardwire the 16 in without understanding what that means .... I assume it is adStateSomething AND adStateSomethingElse.

almost there ..... any ideas?

[Muddy]

OK, strange development. It seems to be working now (thought its fooled me before). I changed the code to the following .... but WHY does it work? isnt status=0 closed? what's going on?

heres what I replaced my updates with:

Do While rst2.Status > 0 
                    DoEvents
                Loop
                rst2.Update
                Do While rst2.Status > 0 
                    DoEvents
                Loop
                frmStatus.Caption = "Saving"

[Muddy]

ok, it fooled me again ... its still broke.

here's the deal:

I dont call rst.update until rst.status <= 0
on calling update I get the "locked" error
on debugging the error the rst.status value is 16

help .... please ....

[Muddy]

OK, assuming it's not fooling me again, I think Ive got it. This is pretty old code that was behaving well on older hardware and smaller databases. On a larger database on a Core Duo it consistently crashed as described, though.

I noticed that the reference was set to "Microsoft Data Access Components 2.7" .... I cleared this reference and set it to "Microsoft Data Access Components 2.8" and so far it is behaving perfectly.

Ill give it some time and post back here for the benefit of others who may run into this issue.

Thanks!

[si_the_geek]

Doh! Yep, I made a big blunder on my copy & paste (that'll teach me to use VB to verify code I post!!), you almost got what I meant tho.

Rather than Status (which is per record, with different values than adStateExecuting etc) you should be using State (which is for the whole recordset).


The switch to MDAC 2.8 may well be a valid solution - I'm afraid I dont know what changed from 2.7 to 2.8, but support for multi-core processors is certainly a possibility.

[Muddy]

errors are back even trying everything mentioned so far in this thread ...

[si_the_geek]

Oh dear...

Have you tried one of the other methods in the "how do I add a record" FAQ?

[Muddy]

Oh dear...

Have you tried one of the other methods in the "how do I add a record" FAQ?

Thanks ... Ill try that (didnt notice it before).

Right now I have switched from Early binding to Late binding and it seems to be working ... for now ...

[Muddy]

problem still there no matter early or late bound

[Muddy]

It appears that I have finally resolved this issue with the following open method (for all 4 open statements):

rst.Open strTemp, cnn, adOpenDynamic, adLockPessimistic, adCmdTableDirect

I won't claim to understand exactly why ... but is does seem to be working. I post for the benefit of future searchers.

Thanks to everyone who replied!!