Record Locking

[KevinMcGuire]

Hi,

I'm doing maintenance on an app using VB6 and a non-indexed Access 97 database, connecting the two with a bound data control. The following code is being used to reset two fields which are used as switches back to the value "N":

Set tbexprec = DB.OpenTable("EXPENSERECORD")
tbexprec.MoveFirst
Do While Not tbexprec.EOF
tbexprec.Edit
tbexprec("INVOICEREQ") = "N"
tbexprec("MRIINVOICE") = "N"
tbexprec.Update
tbexprec.MoveNext
Loop

The problem I'm having is that during the loop I get error message 3218 - Couldn't update; Currently locked. The error occurs on the same record number each time, even when I shift records around. I assume this is because Access locks 2k pages rather than individual records.

The only explanation I've found for the locking is that the mnuExit routine wasn't really closing the application, and as its one user would exit and reopen the app she was running simultaneous occurences of it. To the best of my knowledge no other method of locking records I've found in any documentation is being employeed (my default record locking for the table is set to No Locks and nothing in the code locks a record).

Could anyone tell me if my guess as to the cause of the problem is reasonable and if there are any other common reasons? Also, even though I've corrected the mnuExit routine, I still get the error when employing the logic listed above, so could anyone tell me how to remove all locks from the database?

I've done tons of research over the last couple days and there doesn't seem to be a lot of useful information on this subject, so I definitely appreciate any advice someone with experience in record locking with VB/Access can give.

Thanks,
Kevin McGuire

[Ianpbaker]

Hi KevinMcGuire

The easiest way to see if your menu exit works is to press ctr alt del and see if it is still running in window's, if the answer is yes, that could cause your problem. alternaively try setting tbexprec = DB.Openrecordset("SELECT * FROM EXPENSERECORD") and play around with the locking options of the recordset this may sort out your problem.

Hope this helps

Ian