lock violations

I am performing an update SQL through an ADO command object on an Access database table. The commandtext is something like: UPDATE table SET field TO TRUE WHERE othertable!otherfield IS TRUE .... And it gets about 2/3 through the update when I get error: 'could not update; currently locked'. I run it again, and it locks at the same place.

Then I ran it as an update query in Access and it locks up again, only a little further on. I get the error: Could not update due to lock violations.

The database is on a remote server, but I am the only one using it. The default record locking is set to no locks, and the open mode is shared.

The update must be locking itself out, but is that possible? And what do I do about it?

[pardede]

do you have relationships in your database with integrity enforcement and cascade update/delete? i have had experience that this created problems...

I do have integrity enforced, but not on the field being updated. The field being updated is a true/false flag set to whether it should go into a report. It is being set on a relationship to another table via primary = referential keys in a (sql) WHERE clause.

It seems like a simple update. That's what's got me buggered.

[Gerald]

I've got some good news and some bad news for you. The bad news is that I suspect database corruption. :( The good news is that I could be wrong! :)

I've encountered the same problem myself with a large database (Access '95) that was on a Novell NetWare server. Try running your update query from within Access while having exclusive use of the database. My guess is you will still get the lock violation and if that is the case, then I strongly suspect your problem is due to database corruption. Compacting and repairing the database doesn't seem to clear the problem, but you could try it for yourself. Access databases are notoriously unstable when running on NT servers and, although they do much better on Novell NetWare servers, they are still vulnerable when you have several users beating on your database.

In order to get around the problem I had to write code that "scraped" the good records from the corrupted database and wrote them out to a brand new database. You could try writing a routine that opens a recordset on the table and updates it using the Update and MoveNext methods. Be sure to place an error handler in the procedure that will write the record's ID out to a file--you'll need to know 'em later when you try to repair your database--then skip past the offending record.


Gerald

Well the good news - your words - is that you are wrong, Gerald. But I don't think that's good, really. I tried the same update in Access in exclusive mode and it worked fine and quickly. I was stunned by how quickly. I'm running it again in shared mode, and it's gone off into orbit as it often does leaving me no clue as to what it is doing or if it is doing anything at all. But eventually I expect to get the same lock error.

When it has run off like this in the past I have had to break out of it with ctrl-alt-delete, which may be one reason why I'm having trouble now. But I when I let it run, it continues to late into the night when it is finally broken off at the server.

Is this peculiar to NT and/or Access? Are these two phenomena (ie., lock errors and lost in space) related?

[Gerald]

I'm glad to hear I was wrong--the alternative would have been no fun at all! :)

I'm doubting the problem is related to NT or having an Access database on an NT server. In fact I may have been just a little out of line when slamming NT server--but only a little out of line! ;)

Maybe at this point you could show us the code that is giving you the trouble. Maybe someone can see a problem with it.

Gerald

Here's the sql statement:

"UPDATE EFS_PROD SET
EFS_PROD.EFSPROD_REPORT = True
WHERE (((EFS_PROD.EFSPROD_CURRENT)= True ) AND ((EFS_PROD.PROD_CODE) = "
& rstRegistration!PROD_CODE & "));"

It is executed with a ado command:

WITH cmmUpdate
.ActiveConnection = adoConnection
.CommandText = strSql
.Execute
END WITH

If there is a problem, it is in the SQL or Access, because it locks in both VB in Access query. But, like I say, it didn't lock in exclusive mode but did in shared, even when there was nothing sharing it.

No, I take that back. It works in Access in exclusive mode, but not in VB in exclusive mode. Does this indicate anything?

[Gerald]

Your SQL UPDATE statement looks good and I really can't see anything wrong with it. How many records are affected by this update? Could there be some sort of maximum page/record lock threshold that is being exceeded on your server? Is it possible that another recordset/connection is locking your update command? Can you show the code that establishes your "adoConnection" and "rstRegistration" objects? See if they are locking out your update command.

Gerald

[JohnAtWork]

This might be of no help, but you don't have a form open that may be locking the table?

You've probably checked this, but it may be possible.

How about shifting gears into DAO and see if it works?

Sorry about the delay on the response, Gerald & John. We had a snow day yesterday. In answer to you John, there are several forms open - it is a MDI - but none have an active connection to these tables.

There are two recordsets already open in this procedure, both are adOpenForwardOnly and adLockReadOnly. The rstRegistration has this structure
sqlProducts = "SELECT BUY_REG.*, BUY_REG.REG_CODE
FROM REGISTRATION INNER JOIN BUY_REG
ON REGISTRATION.REG_CODE = BUY_REG.REG_CODE WHERE (((BUY_REG.REG_CODE)= " & rstReport!REG_CODE & "));"

With rstRegistration
If .State = adStateOpen Then
.Close
End If
.ActiveConnection = adoConnection
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open sqlProducts, Options:=adCmdText
End With

sqlReport = "SELECT BUYER.*, BUYER.BUY_CODE, REGISTRATION.REG_XDATE, REGISTRATION.REG_CODE, REGISTRATION.REG_NOTES FROM BUYER INNER JOIN (REPORT INNER JOIN REGISTRATION ON REPORT.REPORT_CODE = REGISTRATION.REPORT_CODE) ON BUYER.BUY_CODE = REGISTRATION.BUY_CODE Where (((REGISTRATION.REG_TERMINATED) = No) And ((REPORT.REPORT_CODE) = " & lngReportCode & ")) ORDER BY BUYER.BUY_COMPANY;"

With rstReport
If .State = adStateOpen Then
.Close
End If
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.ActiveConnection = adoConnection
.Open sqlReport, Options:=adCmdText
End With

My adoConnection connectstring is : "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strAppPath & "Database.mdb;Persist Security Info=False"

I will test John's idea of closing my other forms after I finish rebuilding my database as suggested by Gerald. I am now suspecting that it is a corrupted database, because I have an old copy in Access 97 which works ok - at least through this update. Time will tell.

I appreciate the mental exertion you guys have given. Thanks.

ok. Here's the deal. The update works off the server - that is, on a local disk. So its not the code and not the database. It's the server connection. How can I check the maximum page/record lock threshold you mentioned, Gerald? Can it be expanded?

[Gerald]

On a Netware server you would use the SET command to change any one of the following settings:

SET Maximum record locks per connection = ?
SET Maximum file locks per connection = ?
SET Maximum record locks = ?
SET Maximum file locks = ?

I don't have much experience with Windows NT server, so I can't help you there.

Gerald

We use NT with Netware. I'll ask the system admin for help. But, at least, you steared me in the right direction.

Thanks.