[RESOLVED] "Could not update; currently locked." and MS Access / VB.Net

[Mark@SF]

I have a VB.Net (VS2010 and Framework v4) application that uses the BackgroundMultiWorker (by jmcilhinney http://www.vbforums.com/showthread.p...undMultiWorker) to collect data from various financial websites into a couple of MS Access 2010 tables.

The application begins by creating a collection of datatables and then passes each datatable in the collection to a BackgroundMultiWorker for parallel processing. The BackgroundMultiWorker processes each record in its datatable (in a typical sequence of Webrequest, Webresponse, parse response text, delete records, update records, and insert records, etc.).

I run this application on a couple of different computers (all Win7 Pro 64-bit OS's with Intel I7 CPUs and between 4-12 gB RAM). I occasionally get the "Could not update; currently locked." error (#5) message when the VB.Net application sends an ExecuteNonQuery with an OleDbCommand to Access. Many times the application will complete without error. But when these errors happen, they occur regardless of which PC I'm using and at different records in a BackgroundMultiWorker's datatable.

I have put an Application.DoEvents() statement immediately following each ExecuteNonQuery statement in the VB.Net code. I have set the Access options (via the Access Client Settings) for the "Default open mode" = Shared, the "Default record locking" = Edited record, the "Number of update retries" = 2, and the "Update retry interval (msec)" = 2000. I have also checked the "Open database by using record-level locking" check box in the Client Settings. I had hoped that these would be sufficient to avoid record locking problems, but obviously I'm missing something.

I would really appreciate some advice regarding this record locking error and what I might be able to do to resolve it (e.g., how to avoid the error altogether or how to trap it and work around it). Thanks in advance to anyone who can help me solve this problem.

[Mark@SF]

I found the answer. I didn't have an Application.DoEvents() statement in the procedure that has the WebRequest / WebResponse functions. Putting this statement into the code eliminated the record locking error. Apparently any websites that take a long time to download were impacting the procedures that performed input/output actions in the database. Lesson learned for multi-threaded applications...