Best practice to overcome recordset locking? 4

[Du2good]

Backend table has 100K plus records. Five instances of the FE will be editing 50K of the records, 10K for each instance, one record at a time. The problem is that record locking is preventing editing of some of the records. Some how I want to be able to update any record with out running into any locked records. Is that possible without creating different tables for each FE instance? I only need to make one pass thru the recordset. I've always used Set rst = Currentdb.OpenRecordset(qryStatus, dbOpenDynaset). I'd like the process to run without user intervention… once started; it needs to complete one pass through all of the records.

I've experimented with optimistic locking but don’t quite understand if its working or I'm doing something wrong. I don’t want to wait for the record to become unlocked due to "page locking" unless it will be unlocked within a second. If locked records are always going to be present, my last thought is to refresh the query at the end of each pass and restart the cycle automatically, until all of the records are updated.

Previously all 100K records were updated using one recordset and the process lasted several hours. Using five different recordset/instances will cut it to one fifth of the normal time.

Thank you!!

 

[vbajock]

I'd consider moving to SQL server

 

[Du2good]

Thanks for the reply vbajock.
I take it that there isn't much of a 'better' approach to overcome the record lock problems.

We are pretty much stuck with the equipment we have. As long as we can run the process, 'they' don't really care how long it takes. So we may just do as is with the burden on me.

Thank you!!

 

[JoeAtWork]

Can you do this with a set SQL Update statement? Updating through recordsets is best avoided if possible.

 

[Du2good]

Thanks JoeAtWork
I'm familiar with using SQL with VBA and would love to do so, but for this case we are doing a 'screen scrape' off of a mainframe session.

Each record is a different account, and we get the data for each account, one at a time. So we need to update each record from the info on the mainframe session screen for each acc. Hope that makes sense.

Are there additional suggestions as to which type of recordset to use, and or the locking options ? Basically I think I'm reaching the limits of our/my available resources and if this type of request continues to grow it will need to be done by someone with more technology outside of our department.

 

[oharab]

Could you not write the updates into a staging table, then run an update query based on the staging table at intervals?
Loading into the new table won't effect the locking, if the clients are just reading from your main table, the updates won't affect them & it would probably speed the whole system up in general.

hth

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[vbajock]

Sorry it took me a minute to see what you are doing wrong - The code frag you posted is the old DAO model, you need to recode in ADO. ADO has the ability to open row-level locking; DAO does not. Code it as an ADO recordset and I bet your problems go away.

 

[Du2good]

vbajock - After 4 hours of researching ADO on this site, this is what I have so far, but I'm getting an error of "Records cannot be read;No read permission on 'tbltest'".

So it seems the line "rs.Open sql1, cn, adOpenKeyset, adLockOptimistic" is missing something. I've tried different methods of opening the rs but havent gotten it yet.

Another question: Since it looks like the connString identifys the .mdb having the table that I'd like to edit, then there isnt a need to have a 'linked' object to tbltest...is this correct??

Surely this is doable?



Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
Dim sql1 As String

connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\$Music1\test.mdb;" & _
"persist security Info=False"

cn.ConnectionString = connString
cn.Open connString

sql1 = "select * from tbltest"
rs.Open sql1, cn, adOpenKeyset, adLockOptimistic
If rs.BOF = True And rs.EOF = True Then
MsgBox "no records"
Else
Do Until rs.EOF = True
rs.Update "name1", "Bob"
' rs.Update "field name", "value"
rs.MoveNext
Loop
End If

 

[lameid]

JoeAtWork:

Can you do this with a set SQL Update statement? Updating through recordsets is best avoided if possible.

oharab:

Could you not write the updates into a staging table, then run an update query based on the staging table at intervals?
Loading into the new table won't effect the locking, if the clients are just reading from your main table, the updates won't affect them & it would probably speed the whole system up in general.

Combine these two ideas and definitely use a staging system on the local system. When it is done, append everything from the staging table to the backend table... If the append bombs due to locking, all you have to do is run the query later.

 

[lameid]

I hit submit instead of preview <sigh>. I meant table instead of system.

 

[Du2good]

JoeAtWork, oharab,
Sorry I missed the intent of your previous posts. I finally get what a 'staging' table is. <duh!>. Build the recordset, save the updated records to the 'staging' table, and then update the BE table using the 'staging' table as the source. <2xduh!!> I can already see the benefits, the fog has lifted.

Lameid - Thanks for putting it all together for me!

vbajock - since DAO is oldschool, going forward I'll be using ADO, so if there is a fix for the ADO code that I posted I could use it on the next project.

 

[vbajock]

If the table is linked, you don't need to code a connection string, you can just use the CurrentProject.Connection object, which makes everything about the connection simple, for example:

rs.Open "tblSomeTable", CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTable

The only caveat to that is that you cannot perform indexed searches on the table using the .find and .seek methods, if you need to do that, you have to code the entire connection string.

Environments are different, sometimes you have to play with the various .open parameters to find the best set of parameters, as you can see by my example, I code mostly in Terminal Server environments, and opening the table dynamically with an Optimistic lock seems to work best for me, your server set up and environment my differ.

I would recommend you read WROX's ADO 2.6 Programmer's Reference or something similiar, ADO is extremely powerful and you can learn it inside out in a month or so.