DAO Row-level Locking with Linked Tables

I can't really see how that work around would work with *bound* forms and linked tables. But, maybe someone has a solution.

mx

Hopefully... I don't see why Microsoft would only put row-level locking on ADO when DAO is the default, I guess its the same reason they made transactions such a pain in the ass to implement.

Transactions are not that hard, I use them for many things.

I will ping Leigh Purvis ... if anyone knows a workaround, it will be him. He is in the UK ... so ... standby ..

mx

Well they are not that hard, I just think they could have been simpler, and better incorporated with bound forms. But that's just me.

And thanks for pinging another expert for me, much appreciated.

*Ideally* ... (single) Row Level Locking (ie, only the edited record) would be locked ... and would apply to all cases.  It's easy to trap the Write Conflict errors in the Form Error event ... and display a nice friendly message to users as to what's going on. Plus, the stop sign symbol would be VERY nice.

Maybe we/I and missing something ... it's a complex subject.  Again, Leigh IS the expert on this subject.

mx

>> Hopefully... I don't see why Microsoft would only put row-level locking on ADO when DAO is the default, I guess its the same reason they made transactions such a pain in the ass to implement. <<

DAO is indeed the default object model that Access objects use ... HOWEVER, the Access UI is NOT limited to interacting with a JET database ONLY through DAO, or ADO for that matter.  The JET database format has its own set of API calls that, I am sure, MS Access is quite familiar with.  So ... even if a form's recordset is returned to you (VBA) as a DAO.Recordset, that does NOT mean the Access UI, actaully uses DAO (or ADO) to acheive its record locking capability, or to perform some other type of operation on the db.

When we (us developers and power users) manipulate a JET database via VBA, we are the ones limited to the DAO and ADO object models, so we are faced the locking limitations placed upon us by those object models.  One of those limitations is the fact that the database's locking mode that is in effect when the db is FIRST opened is the locking mode that is used for subsequent connections (whether ADO or DAO or another instance of Access).  So, if someone has their Access UI set to open databases with Page Level locking, and they opens the db first, each subsequent connection is forced to follow suit, thus ignore the locking mode attempted by any susequent connection to the db.  The 'work around' that MS has published (and the OP links to), tries to accomplish the goal of "opening the data first" by opening the db with record level locking via a direct ADO connection (thus ByPassing your FE's JET instance) BEFORE your FE Access UI opens the db via a linked table.  The subsequent opening/connecting (and persisting) via DAO (or other Access objects like linked tables) will be forced to adhere to record level locking of the initial opening via ADO.

The work around proposed will NOT work if someone has the db opened in page level locking mode prior to the record-level locking code being initially executed by your FE (assuming your FE executes some variant of the MS 'work around' code).  Also, do take note, that with linked tables, if you do not have ANY data bound objects opened in the Front End, the back end database is closed, thus "reseting" the locking mode, that is why the 'workaround' code persists the DAO database connection.  So, with a 'work around' in place, and the assumption that your FE is the only way to get to the Back End, you can be reasonbly sure that some instance of your FE opens the database with record-level locking via ADO (thus ignoring the Access UI setting), then to ensure the db remains at record level locking (even via DAO), a persistent connection to the database is maintained.

There are a some exceptions to the rule stated above.  One such exception is SQL Commands (ie: Query Ojects or Executed SQL Statements) whether executed through an Access UI or DAO Object Model PAGE level locking is used by JET, regardless of how the db was opened.  This is done for speed.  I believe if you use ADO to execute your SQL Command, you can control the record locking in the connection object with the "Jet OLEDB:database Locking Mode" property. Then in an ADO recordset by setting the "Jet OLEDB:Locking Granularity" property to 2.  Another situation that forces page-level locking is the editing of memo datatyped fields and indexed fields.  And one more, Jet 4.0 introduced level locking, so if your db is not JET 4.0 formated, then you will never achieve record-level locking.

...........

Now ... after all that ... the OP has asked ....
>> So how would I go about applying this workaround in my situation? I've tried opening the ADO connection before starting the refresh and closing it after (hoping that this would open the implicit DAO connection made during the refresh as row-locking) but this didn't work. <<

Which brings out two points mentioned in my diatribe ... Open the ADO connection PRIOR to refreshing your links, AND keep a connection (Either DAO or ADO) alive (persistent) during your entire Access session.

Out of curiousity, what situations are you in to require row level record locking?

datAdrenaline: Thanks, you make good diatribe ;-)

Thank :) ...

As a point of note ... I used the word diatribe ... but I must admit my assumed definition of the word was formed through its use in previous conversations ... so, I decided to look up the official definition ...

One source had:
a bitter and abusive speech or writing

and another had the above as well as:
a prolonged discourse

.....

I hope it is understood from my text that my use of the word was definately in the "a prolonged discourse" category ... :)

Thanks again GRayL!

Oh ... just reread my prolonged discourse ...

This comment:
"One such exception is SQL Commands (ie: Query Ojects or Executed SQL Statements) whether executed through an Access UI or DAO Object Model PAGE level locking is used by JET, regardless of how the db was opened."

This is for Action type queries (INSERTs and UPDATEs) ...

If you really need row level locking, then I would use the old work around of padding a record with filled text fields so that a single record will be larger then half a page. By doing this, JET can't place another record on the page. Then no matter what, you'll always have row level locking regardless of settings.
JimD.

datAdrenaline: I believe you are correct about keeping the ADO connection open the entire time. So simple, I don't know why I never tried that. I suppose I just misunderstood the workaround. I assumed that after the tables were refreshed they would open a persistent DAO connection, which actually could not be true because I read somewhere that a persistent connection has to be held manually otherwise Access will reopen the connection each time a linked table is opened.
Also thanks for the great explanation about the underworkings of MS Access, brough alot of clarity for me.

I'm just going to keep the question open a bit longer to make sure that the solution I have tried actually works.

JDettman: I was going to try that if I couldn't get a solution here.

Okay, I guess only part of my problem was page-level locking. Opening of an ADO connection and keeping it open until application closes forces record-level locking perfectly.

Now I suppose the next part of my problem is transactions. Once I implement transactions I start getting alot of "Could not update. Record locked." errors, which are a pain (I don't mind the stop signs because then user isn't even allowed to edit the record, with these its just a mess).

Does anyone know how to counter-act this? Below is some basic code I am trying to implement transactions with, I use a more complete code in my real applications.

I will try to find some reproduceable situations then post the FE and BE so you guys can see exactly what I mean.

I am glad to here a persistent ADO connection worked for you as described! ....

With respect to Transactions, it would seem that transactions would ultimately be committed via an action type query, and action type queries use page-level locking.

>> Below is some basic code I am trying to implement  <<
:-s ... no code look at ... :) ....

Oops, forgot to attach the code. Sorry.

>>With respect to Transactions, it would seem that transactions would ultimately be committed via an action type query, and action type queries use page-level locking.

I don't mind page-level locking as much as I mind the "Could not update. Record locked." errors. If I could get only page-level locking but only stop signs show no update errors, I would be happy with that.

Option Compare Database
Option Explicit
Dim blnDirtied As Boolean
 
Private Sub Form_Load()
Dim rs As DAO.Recordset
 
    'Set the recordset so that transactions can be performed (i.e. rollback/committrans)
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblItems", dbOpenDynaset)
    Set Me.Recordset = rs
    Set rs = Nothing
End Sub
 
Private Sub Form_Delete(Cancel As Integer)
    If Not blnDirtied Then DBEngine.BeginTrans
 
    blnDirtied = True
End Sub
 
Private Sub Form_Dirty(Cancel As Integer)
    If Not blnDirtied Then DBEngine.BeginTrans
 
    blnDirtied = True
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
    If MsgBox("Save Changes?", vbYesNo) = vbYes Then
        If blnDirtied Then DBEngine.Workspaces(0).CommitTrans
    Else
        If blnDirtied Then DBEngine.Workspaces(0).Rollback
    End If
End Sub

Select allOpen in new window

"it would seem that transactions would ultimately be committed via an action type query, "

Well ... except when you are using BeginTrans, CommitTrans and Rollback ... in vba code.  In that case, based on what has been said ... row level locking could be in effect.

So Brent ... based on what you've said ... can we get row level locking to work with bound forms ... by somehow creating this persistent ADO connection  .... or bejhan ... is that what you have done ?

mx

MX: I took datAdrenaline's suggestion and opened an ADO connection with row-level locking before doing anything, then refreshed my tables as normal. I keep this ADO connection open until the user quits the application. Without transactions implemented this works like a dream. However, once I implement transactions again I start getting those dreaded errors.

In the code you posted above, I'm not sure that's working the way you think.  You really haven't connected

>>> CurrentDb.OpenRecordset("SELECT * FROM tblItems", dbOpenDynaset)

to the Workspace you are referring to elsewhere.  Is this a bound form?  

Also, where are you opening the ADO recordset ?

mx

The form's bound to the same recordset, but when I read how to implement transactions (http://support.microsoft.com/kb/248011) it says you must set the form's recordset. Or would this work just as well using a bound form and ignoring setting the recordset?

When you say I'm not connected to the workspace I refer to later you mean: DBEngine.Workspaces(0) right? Wouldn't that be the same workspace that CurrentDb is in?

Though the implementation may be causing problems for me, the transactions do work. When you cancel all your changes are reverted and when you submit all changes are comitted.

Am I doing something wrong? I just tried to learn how to use transactions using that article.

"Or would this work just as well using a bound form and ignoring setting the recordset?"

Well I don't know ... which is basically what I was asking Brian.

"DBEngine.Workspaces(0) right? Wouldn't that be the same workspace that CurrentDb is in?"
I don't think so.  Look up BeginTrans (or CommitTrans) in the VBA help ... and see how 'relate' a recordset to the Workspace.  The examples are a little bit convoluted, but you will get the idea.

"When you cancel all your changes are reverted and when you submit all changes are comitted."
Are you positive??  How exactly are you 'cancelling' the changes ?  For example, if you are just hitting ESC (or Me.Undo) to cancel ... because all that does is Undo (at the form level) the changes - which would give the same 'appearance' as a Rollback - when you look at the underlying table.

The only example I've seen of using Transaction within a form (at the form level so to speak) was one by Leigh Purvis (I think, or Marcus (harfang) ... where in - i think - he was using disconnected recordset.  Not positive.  Almost positive the form was not bound ... but again, not sure.  I will try to find the mdb example.

mx

By the transactions working I mean I can change a whole bunch of records and when I call RollBack (on my cancel button event) all the changes are discarded, where as when i call Commit (on my submit button event) all the changes are saved. So I'm pretty sure they are functioning.

"So I'm pretty sure they are functioning."

OK ... and sorry ... I missed

Set Me.Recordset = rs

is your code post above ...

mx

Awesome, will check that out when I'm back at work on Friday. Thanks!

Looks like you are basically doing this ( duh ) ... and Leigh's example ... along with dat's explanation above should tie it all together !

mx

bejhan ....

From a brief read ... so I may have missed some details ... but in my opinion, you should simply use a bound form and let Access manage how it commits/modifies a record.  It seems you are circumventing one of the key benefits of using Access to manage your data.  You can use the Form event to accomplish the prompts and such that I see in the code you have presented on a per record basis.

So .. may I ask why the need for a transaction? Are you dealing with a block of records that you "give" to a user? Access Forms, IMO, have a fairly rich event model and letting them work for you can really reduce (again my opinion) the complexity of your code.

If you decide to use transactions, you can do that with either DAO or ADO recordsets.  I have never done it with DAO recordsets but have done so with ADO recordsets, but as I recall I was working with the disconnected recordset and committed all the changes as one batch ... it has been a while, but I could probably find an example ... Also the previously mentioned expert (LPurvis) has a sample on his website... http://www.databasedevelopment.co.uk/examples.htm, but as I recall you seem to be on the right track ... but check out his sample as its often worth seeing other peoples techniques! ...
 

WOW!!! ... I got side tracked for a bit an you guys cranked out some serious text!!! ... I will try to catch up!!! .. LOL!

"So .. may I ask why the need for a transaction? "

Well, I think ... it's so he can get the 'row-level locking' effect ... ?

mx

... gotta run for now ... but I will be back!!! ...

MX ...

>> So Brent ... based on what you've said ... can we get row level locking to work with bound forms ... by somehow creating this persistent ADO connection  .... or bejhan ... is that what you have done ? <<

What ever locking mode is in effect when you access the data that is bound to the form, is the locking you get. The locking mode, once the database is opened, can not be programmatically overridden via DAO; and is not overridden by an Access UI object (ie: a Bound form).  But ADO, with the JET OleDb provider, from what I have read, CAN override the locking mode with a new connection object and the proper settings when creating that connection.  So ... if all installations of Access are set up to open data in row-level locking mode, then you really don't need to create a persistent connection to "hold" your lock mode, since, in theory, your data will only be opened in row level locking mode.

So ... making and ADO connection with the JET OleDb provider via VBA with a row level lock mode, prior to any data access by the Access UI, is merely an attempt to ensure that lock mode set is row level locking.  Any connection made (DAO or ADO or AccessUI) while a locking mode is in effect, will inherit that locking mode.  As long as as connection to the database exists (ie: the .ldb does not get deleted), the locking mode in effect when the .ldb was created will continue to be in effect

I have seen many responders advise folks to create a persistent DAO connection of some sort to the back end in order to improve performance.  Which is good advice, to some extent, HOWEVER, all too often I see the responder create an Autoexect that launches code that sets a global DAO.Database, but most don't realize that the advice just forced page level locking.

It is for this reason, the I personally rely on a bound hidden form, and the Access setting of 'Row Level Locking' to achieve the lock mode.  But ... I have been fortunate to be in a very controlled environment w.r.t. pc configurations.

As an FYI, I generally have had no issues with row level record locking.  It is typical for me to use bound forms, pessimistic locking strategy (pessimistic - locks record on edit; optimistic - lock records on commit) for modifying data, and for deletions I typically use .Execute <some sql statement>.  My "most connected to" Jet database typically has ~25 concurrent connections. I do get complaints every so often (once a month or so) that a user can't edit a record (the locked icon appears in the recordselector) and I just tell my users to wait a bit and try again ... they do ... and it all works out.

To respond to this:

dat: "it would seem that transactions would ultimately be committed via an action type query, "

Mx: Well ... except when you are using BeginTrans, CommitTrans and Rollback ... in vba code.  In that case, based on what has been said ... row level locking could be in effect.

My response:
I don't think so.  Row level locking would be in effect for modification from recordsets and forms, but I believe the commitment of changes in a transaction is done through an INSERT, UPDATE, Or  DELETE SQL command, and thus will use page level locking.  If it were not this way, then the OP would have had success with and with out transactions, however, the OP was only successful with out Transactions in effect.  BUT ... I what to make sure that it is known that I am making my comments on emperical info and some logic based assumptions, I have not read "official" type info supporting my claim, nor has any personal testing confirmed this claim ... so I may be off a bit, but just from the simple results indicated by the OP, it seems my thoughts are on the right track. I have done some cursory searches, but have not found anything definitive on recordlocking in a transaction, but my gut (like thats reliable!) is telling me it is stuck at page-level locking.

The reason I use transactions is incase the user made some serious errors they can easily discard the changes they made. With single-user DBs I used to just copy the table and on a cancel I would restore to the original.

So basically if I want to use transactions I'm stuck with page-level locking. That is fine, but why do I get the "Could not update. Record locked." errors instead of the stop sign symbol to the left of the record?

I need to absorb some of this over the weekend.

mx

Quite a bit of information here isn't there.

Okay, here is my test FE and BE so you can reproduce these errors. First make two copies of the FE then open them both up, for simplicity I will refer to the first FE as 1 and the second FE as 2.

To reproduce errors:

1. In 1 begin editing the first record. Then with 2 try to edit that record and you will get the stop sign. If you move to the next record with the 2 and edit it then try to move off the first record with 1 you will receive the error.

2. In 1 edit the first record then move to a record far down the page. Then with 2 try to edit the first record and you will get the stop sign. If you move to the next record with the 2 and edit it and move off of it you will receive the error.
Database.zip

Sorry, hadn't compacted the BE in a while, its quite large in the file I posted above.


Database.zip

JD ... this from the KB you just posted:

"With record-level locking, however, if a user edits any one record in a table, other users are able to edit any other record, except that one. But this is only true when both pessimistic and record-level locking has been set within the Microsoft Access Options dialog box."

I really don't think that is the case.  Until I started using No Locks everywhere, We would frequently get that same error that bejhan gets ... and I can guarantee you that ... no two users were ever editing the exact same record at the same time.  I really appeared that page level locking was in effect.  

User A would be editing a record; User B editing a completely different record (out of maybe a group of 100 records). When user A tried to save ...  that error would occur!  The only thing I can say is ... the group of 100 records were in 'close proximity' of each other only via their Auto Number ... ie, more a less a sequential group of AN's.

mx

Ah found it.  Old EE thread is here:
http://www.experts-exchange.com/Microsoft/Development/Q_24168353.html with the comment I made second to last.
and the MSKB article in question is:
Description of the new features that are included in Microsoft Jet 4.0
 http://support.microsoft.com/kb/275561
Statement:
"A limitation to record-level locking is that users will not be notified when another user is locking the record. Also, record-level locking is not enabled for Memo data types."
  of course that may have been fixed by now, but who knows.  I've never seen a reference to that again anywhere.
JimD

Just a further note.  Across maybe 16 mdb's since setting the No Locks condition, I don't believe there has been a single instance of that error.  Of course, I am also trapping for the Write Conflict error in the Form Error event.  Plus, when the mdb's load ... I set the "Number of Update Retries" to 10 ... and the Retry Interval to 500.

mx

MX,
<<User A would be editing a record; User B editing a completely different record (out of maybe a group of 100 records). When user A tried to save ... that error would occur! The only thing I can say is ... the group of 100 records were in 'close proximity' of each other only via their Auto Number ... ie, more a less a sequential group of AN's.>>
Other thing to watch out for is memo fields; those pages are shared and Record Level locking is not used for them. Like I said in the other thread comment, it seems to be a lot more trouble then it's worth.
JimD.

>> That is fine, but why do I get the "Could not update. Record locked." errors instead of the stop sign symbol to the left of the record? <<

The my take on it ....

Remember ALL locks remain in effect until the transaction is committed. You probably a differing message because you seem to be hitting the db in different states created by the two transaction encapsulating the same block of records .... in short, there is a lot happening, its no wonder you are different messages!!! ... :)

IMO, If you want to utilize tranactions on your forms, then you need reduce this symptom if you woupd prevent the same record (ideally the same page) from appearing on two instances of a front end.  In addition, this seems much more straight forward with disconnected ADO recordsets, with records that could not appear on two instances of the FE ....

>> The reason I use transactions is incase the user made some serious errors they can easily discard the changes they made. <<

My users are very one record focused ... they are taught to be! ... I teach them, "You move off this record its commited to the database!"

-----------

I have not D/L'd your sample to "play" ... I think I will join MX on the weekend.

Whoa ... another post flurry during my composition of the last post!!! .... gotta catch up again! ...

JD ... "memo fields".  Well, I'll be!!  On one the mdb's where this happened a LOT (the one with the group of 100 recs or so) ... the ONLY field the user updated was in fact ... a memo (notes) field. I have completely forgotten about that.   And I'll bet if I looked into it ... that would be the cases with the other mdb's where this issue occurred!

thanks for that reminder !!

mx

Good summary.  Looks like everything is covered...
JimD.