trying to minimize Write Conflicts in a multi-user database
in [General]
|
Prev: não acho meu orkut
Next: maike yordano pirela vera
From: David W. Fenton on 20 Jan 2010 23:12 "Paul" <begone(a)spam.com> wrote in news:#roQjdZmKHA.5040(a)TK2MSFTNGP06.phx.gbl: > Great information, Dave. Er, my name is not "Dave", it's DAVID. Pet peeve of mine: when someone uniformly identifies themselves as "David" why would anyone presume to call them "Dave"? I wouldn't think to call you "Paulie," unless that is what you signed your posts with. In actuality, I'm the least Dave-like person you're ever likely to encounter! <end of peevish rant> [quoting me:] >> if you're running SQL updates in code against a table that is >> also loaded in a form/forms, save the form/forms *before* running >> the SQL update. > > This could be a problem, because haven't been doing that. I am > running action queries without first saving the record to enforce > some of our business rules. This almost guarantees write conflicts, particularly if the updates are to records loaded in the form and locked for write. > Armen and Banana have pointed out that such queries can > cause write conflicts. However, Tom Wickerath's page on > performance includes a link to a MS Web page that talks about how > DAO 3.6 can produce write conflicts. Any data interface can produce write conflicts if you're trying to update a record in two different places (or the same data page if you're not using record locking, which you're not when you use DAO for executing SQL updates) > So does that mean it's best to use ADO in place of those > update queries? (And is that something that can be done with > ADO?) It's best to do all your updates in the form itself and keep SQL updates to a minimum (although it's OK if you're updating different tables than are loaded in your form). >> if you're updating a record in a different subform, save any >> edits to the first subform before navigating to the other >> subform. This is the only way to avoid write conflicts > > That's something else I'm not doing. I just assumed that when you > leave a subform, the record would be saved automatically. Are you > saying that you need to write a line of code to save a record when > you click another tab or close the form? Whether or not the form updates or not depends on a number of things, but since you can't necessarily control how you user departs your subform, you really have to save the edits. Frankly, I don't understand the design you're talking about. I have never encountered a situation where I thought it was a good idea to have the same form open in more than one editable subform at a time. On the other hand, I do have an app where there's an abbreviated subform on the opening summary tab, and then larger, more detailed subforms on other tabs. But in my tab's OnChange event, if I'm going to the detail tab, I will check the summary tab's subform's .Dirty property and save it if it's dirty, so by the time the user gets to the detailed subform, it's free of any write locks from the other suform on the same table. But that's actually a fairly rare situation for my apps. I tend to make subforms read-only if I'm displaying it in multiple locations on the same parent form. And I can't think of a case where I'd use subforms to display data from the same table(s) as the parent form, read-only or not. >> It is never a good idea to have the same table loaded in more >> than one editable table simultaneously. > > My main form has a single field, the PK ProjectID from tblProject, > but it's Locked, so it can't be edited. I keep the editable data > from tblProject in a subform. Why? > I originally had all the data from that table in the Main form, > but I ran into a problem which, at the moment, I can't remember > what it was, that was solved by moving it into a subform. If > users can't edit that single field in the main form, then it > shouln't create a write conflict problem. Am I right about that? Not between the parent and child forms, but if you've got more than one child form, you can have write conflicts between those. But I don't see any justification at all for the architecture you've described. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 20 Jan 2010 23:16 Armen Stein <ArmenStein(a)removethisgmail.com> wrote in news:uetel51eb3s43ivreeeeo8vld4jbr8uq3r(a)4ax.com: > On Tue, 19 Jan 2010 22:11:17 -0800, "Paul" <begone(a)spam.com> > wrote: > >>> if you're updating a record in a different subform, save any >>> edits to the first subform before navigating to the other >>> subform. This is the only way to avoid write conflicts >> >>That's something else I'm not doing. I just assumed that when you >>leave a subform, the record would be saved automatically. Are you >>saying that you need to write a line of code to save a record when >>you click another tab or close the form? > > Paul, your assumption is correct. When your focus moves between a > main form and subform, or from one subform to another subform on > the same main form, Access implicitly saves the dirty record of > the form you are leaving. In other words, within a main form and > its subforms, only one record can be dirty at a time. No code > extra code is needed for this. I'm not certain this is correct. I would never leave it to chance -- if I know I have a possibility of two subforms based on the same table and both are editable in different tabs, I would use the tab control's OnChange event to make sure none of the subform's are dirty. I have seen too many problems with implicit saves that I always like to have a line of code whose purpose is saving the data so that if an error occurs in the process of saving, I know exactly what line of code produced the error. Relying on an implicit save will cause some other line of code to produce the error, one that is not explicitly about saving the data. > However, the explicit save *is* necessary if your focus is not > leaving the form, but running an update operation in code on the > same records as that form. One should avoid updating the same table in code when it is open in an editable form -- that is not the Access way -- do all your editing in the form itself, and save SQL updates for updating data that's not loaded in your form(s). -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Tom Wickerath AOS168b AT comcast DOT on 21 Jan 2010 01:49 David, > Second, even if you use the tricks cited downthread to make sure > you're using record-level locking, if you then use DAO to update, > you're back to page-level locking for the DAO update, .... What evidence can you point to in support of the above statement? KB 306435 (http://support.microsoft.com/kb/306435) includes the following quotes: Resolution Section: "To resolve this problem, use ActiveX Data Objects (ADO) to enable row-level locking on an Access database, and then open DAO connections to the database. All subsequent attempts to open DAO connections to the database will respect the locking mode that you set." More Information Section: <Begin Quote> To enforce DAO to use the row-level locking that ADO sets, follow these steps: Use row-level locking to open an ADO Connection to the database as follows: Set the ADO Connection object's Provider property to Microsoft.JET.OLEDB.4.0. Set the Connection object's dynamic Properties("Jet OLEDB:Database Locking Mode") to 1. Open the ADO Connection. Use the OpenDatabase method to open the same database from DAO. Because the locking mode is reset when you close and reopen the database, use a DAO database that remains open as long as you need row-level locking. For example, use Form or Module level scope in Visual Basic for the DAO database. Close the ADO Connection. <End Quote> Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "David W. Fenton" wrote: > [BTW, I completely missed this interesting thread, because I have my > newsreader configured to kill crossposts to more than 2 newsgroups. > It's really unwise to crosspost excessively as it makes your post > look like spam; in this case, I can't see that anything other than > m.p.acess and m.p.formscoding was really appropriate, and my > philosophy is that if you post in m.p.access, i.e., the general > Access newsgrsoup, you shouldn't crosspost the same article to the > specific newsgroups -- instead, *don't* post it in the general > newsgroup and crosspost to 1 or more groups with specific > non-overlapping topics that are appropriate; but definitely keep the > crossposts to a minimum] > > "Paul" <BegoneSpam(a)forever.com> wrote in > news:ONN76tUlKHA.1648(a)TK2MSFTNGP05.phx.gbl: > > > I have been told by several developers that one way to minimize > > the occurrence of the Write Conflict is to put the main form's > > controls into a subform and remove the Record Source from the main > > form. You then set Child and Master Field links in the subforms > > to the value returned by the record selection combo box on the > > main form (stored in a text box on the main form). > > I would suggest that you've perhaps misunderstood the > recommendation. It is never a good idea to have the same table > loaded in more than one editable table simultaneously. If you do > that, you're definitely setting yourself up for write conflicts, as > opposed to ameliorating write conflict errors. > > My first question for you is to wonder if you're using optimistic or > pessimistic locking -- the first choice in all case is OPTIMISTIC, > but it sounds to me like you're using pessimistic. > > Secondly, if you're updating a record in a different subform, save > any edits to the first subform before navigating to the other > subform. This is the only way to avoid write conflicts > > Third, if you're running SQL updates in code against a table that is > also loaded in a form/forms, save the form/forms *before* running > the SQL update. Failure to do so guarantees write conflicts. > > Last of all, further down the thread there's substantial discussion > of record-level locking. I've never used it and I've never had > issues. > > Second, even if you use the tricks cited downthread to make sure > you're using record-level locking, if you then use DAO to update, > you're back to page-level locking for the DAO update, as DAO was > never updated by MS to be able to use record-level locking (because > of the stupid anti-DAO/pro-ADO campaign, which caused a whole bunch > of the Jet 4 innovations to be supported in ADO but not in DAO -- we > are still living with the after-effects of that bloody stupid > decision on MS's part). > > -- > David W. Fenton http://www.dfenton.com/ > usenet at dfenton dot com http://www.dfenton.com/DFA/ > . >
From: Armen Stein on 21 Jan 2010 11:47 On 21 Jan 2010 04:16:03 GMT, "David W. Fenton" <XXXusenet(a)dfenton.com.invalid> wrote: >I'm not certain this is correct. I would never leave it to chance -- >if I know I have a possibility of two subforms based on the same >table and both are editable in different tabs, I would use the tab >control's OnChange event to make sure none of the subform's are >dirty. Well, I am. :) It isn't chance - it's normal Access behavior. That said, we don't normally have two *editable* subforms on the same table on the same form anyway. If that's the only time you're doing the explicit save, then I think that's fine to control the saves more precisely. I just wouldn't recommend them as a standard practice - only when we need them for some other reason. Basically we always try for the simplest code possible. Armen Stein Microsoft Access MVP www.JStreetTech.com
From: David W. Fenton on 21 Jan 2010 13:59
Armen Stein <ArmenStein(a)removethisgmail.com> wrote in news:0nvgl51frb40fqdg1k3u9vvds0gmujn482(a)4ax.com: > On 21 Jan 2010 04:16:03 GMT, "David W. Fenton" ><XXXusenet(a)dfenton.com.invalid> wrote: > >>I'm not certain this is correct. I would never leave it to chance >>-- if I know I have a possibility of two subforms based on the >>same table and both are editable in different tabs, I would use >>the tab control's OnChange event to make sure none of the >>subform's are dirty. > > Well, I am. :) It isn't chance - it's normal Access behavior. What about the point of having a line of code that explicitly saves as the point of origin of any failure in the save process? That seems pretty compelling to me. > That said, we don't normally have two *editable* subforms on the > same table on the same form anyway. If that's the only time > you're doing the explicit save, then I think that's fine to > control the saves more precisely. I just wouldn't recommend them > as a standard practice - only when we need them for some other > reason. Basically we always try for the simplest code possible. I try for the most reliably code. The explicit save seems absolutely required to me in order to troubleshoot save errors. I was badly burned by an unheralded aspect of the bookmark bug, i.e., that setting the bookmark to move the record pointer implicitly saves any edits to the departed record, but errors in that save were getting eaten by Access. Thus, this code was dangerous: With Me.RecordsetClone .FindFirst "[PK]=" & Me!cmbComboBox If Not .NoMatch Then Me.Bookmark = .Bookmark End If End With The line where the bookmarks are set (which does the navigation) implicitly saves any edits, and the bug was that in certain circumstances I could never identify, those edits would *not* be saved, and no error would be reported. This code is safe: With Me.RecordsetClone .FindFirst "[PK]=" & Me!cmbComboBox If Not .NoMatch Then If Me.Dirty Then Me.Dirty = False End If Me.Bookmark = .Bookmark End If End With ....and the reason it's safe is because you're not relying on the implicit save -- you're explicitly telling Jet to save the edit, and if any errors happen in that save, they will be reported. This is the same reason I'd always explicitly save dirty subforms when changing a tab, because it's essential to do the save EXPLICITLY, rather than relying on an implicit save which historically has been buggy in other circumstances. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |