Solved

DAO Row-level Locking with Linked Tables

Posted on 2009-07-10
44
992 Views
Last Modified: 2012-05-07
There is a workaround to get row-level locking in DAO (instead of page-level locking).

This workaround is described here: http://support.microsoft.com/kb/306435

I use linked tables so I don't explicitly open a connection to my back-end. I just refresh them with the below procedure. 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.
Private Function RefreshLinkedTables() As Boolean

Dim db As Database

Dim tdf As TableDef

RefreshLinkedTables = True

    

    Set db = CurrentDb

    

    On Error GoTo ErrorHandler

    

    InitializeMinorProgressBar "Refreshing Tables", db.TableDefs.Count

    

    For Each tdf In db.TableDefs

        If Len(tdf.Connect) > 0 Then

            tdf.Connect = ";DATABASE=" & STR_APPLICATION_FOLDER_PATH & "IC_backend.mdb"

            tdf.RefreshLink

        End If

    

        IncrementMinorProgressBar

    Next

    

    Err.Clear

    On Error GoTo 0

    

    db.Close

    Set db = Nothing

    

Exit Function

 

ErrorHandler:

    RefreshLinkedTables = False

    MsgBox "Back-end tables could not be refreshed." & vbNewLine & "Please notify an administrator immediately.", vbCritical + vbOKOnly, "Table Refresh Failure"

End Function

Open in new window

0
Comment
Question by:bejhan
  • 14
  • 12
  • 12
  • +2
44 Comments
 
LVL 75
ID: 24827465
I can't really see how that work around would work with *bound* forms and linked tables. But, maybe someone has a solution.

mx
0
 
LVL 1

Author Comment

by:bejhan
ID: 24827477
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.
0
 
LVL 75
ID: 24827512
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
0
 
LVL 1

Author Comment

by:bejhan
ID: 24827522
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.
0
 
LVL 75
ID: 24827557
*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
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24829312
>> 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?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 24830957
datAdrenaline: Thanks, you make good diatribe ;-)
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24830980
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!
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24831002
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) ...
0
 
LVL 57
ID: 24831947
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.

0
 
LVL 1

Author Comment

by:bejhan
ID: 24861463
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.

0
 
LVL 1

Author Comment

by:bejhan
ID: 24861966
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.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24862400
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 ... :) ....
0
 
LVL 1

Author Comment

by:bejhan
ID: 24862660
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

Open in new window

0
 
LVL 75
ID: 24863386
"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
0
 
LVL 1

Author Comment

by:bejhan
ID: 24863533
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.
0
 
LVL 75
ID: 24864677
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
0
 
LVL 1

Author Comment

by:bejhan
ID: 24864876
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.
0
 
LVL 75
ID: 24864919
"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
0
 
LVL 1

Author Comment

by:bejhan
ID: 24864928
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.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 50 total points
ID: 24864946
OK ... I found it ... and it is Leigh's ... and it comes from here:

http://www.databasedevelopment.co.uk/examples.htm

Attached is the 'Transactions in Forms' example ... very cool.   The form is 'bound' to a recordset ... but uses

me.Recordset  instead of having a RecordSource property or using Me.RecordSource = somesource.


FormTransaction.zip
0
 
LVL 75
ID: 24864953
"So I'm pretty sure they are functioning."

OK ... and sorry ... I missed

Set Me.Recordset = rs

is your code post above ...

mx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:bejhan
ID: 24864955
Awesome, will check that out when I'm back at work on Friday. Thanks!
0
 
LVL 75
ID: 24864983
Looks like you are basically doing this ( duh ) ... and Leigh's example ... along with dat's explanation above should tie it all together !

mx
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24865055
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! ...
 
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24865070
WOW!!! ... I got side tracked for a bit an you guys cranked out some serious text!!! ... I will try to catch up!!! .. LOL!
0
 
LVL 75
ID: 24865116
"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
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24865175
... gotta run for now ... but I will be back!!! ...
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24866016
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.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24866331
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.
0
 
LVL 1

Author Comment

by:bejhan
ID: 24880581
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?
0
 
LVL 75
ID: 24880694
I need to absorb some of this over the weekend.

mx
0
 
LVL 1

Author Comment

by:bejhan
ID: 24880803
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
0
 
LVL 1

Author Comment

by:bejhan
ID: 24880848
Sorry, hadn't compacted the BE in a while, its quite large in the file I posted above.


Database.zip
0
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 25 total points
ID: 24881290
<<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?>>
a. your not using pessimitic locking (Edited record) insteal of opptimistic (No Locks, which is mis-nomer - it means no edit locks).
 b.  There was a bug where the pencil icon would not be properly displayed.  I can't locate the MSKB article that said this and I don't know if it was ever fixed.  I posted it here on EE a while back the last time MX and I were in a thread that involved record locking.  I'll spend a few more minutes looking for it.
Also note that you only get record level locking when you use pessimistic locking:
http://support.microsoft.com/kb/225926
  With all that said, I say if you want record level locking for everything, the easit answer is to pad the records.  Typically you don't need to do that for every table, just the "main" tables in the app.
JimD.
0
 
LVL 75
ID: 24881390
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
0
 
LVL 57
ID: 24881442
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
0
 
LVL 75
ID: 24881444
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
0
 
LVL 57
ID: 24881458
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.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24881595
>> 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.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24881675
Whoa ... another post flurry during my composition of the last post!!! .... gotta catch up again! ...
0
 
LVL 75
ID: 24881713
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
0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 125 total points
ID: 24881906
Summary of the info dispersed.

- Record vs Page level locking that is set upon db open, remains in effect till db is closed.
- DAO can not initiate record level locking.  ADO/OLEDB For JET as well as the Access UI can.
- Action Queries and Transactions use Page level locking REGARDLESS of the db being set to record-level locking.
- Transactions hold on to locks till the transaction is committed.
- Memo fields AND  indexed fields use Page level locking REGARDLESS of the db being set to record-level locking.
- A Forms Record Locks Property: No Locks/Edited Record (aka: Optimistic / Pessimistic Locking)
Pessimistic: lock obtained on record open (ie: upon first edit of any field).  No one else can open the record (or possibly record on the page depending on locking level mode) until the lock is removed.
Optimisistic: lock obtained prior to commit. Data concurrecy can be a issue, but lock is active for a shorter time thus possibly eleviating some lock issues.  Does not support record level locking.
- Padded records is a sure fire to emulate record level locking, no matter the locking mode (Record/Page).

..... I think I got all the major points ... gotta run .....
0
 
LVL 57
ID: 24882488
Good summary.  Looks like everything is covered...
JimD.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now