trying to minimize Write Conflicts in a multi-user database
in [General]
|
Prev: não acho meu orkut
Next: maike yordano pirela vera
From: Banana on 16 Jan 2010 11:46 Paul wrote: > No, this is a real issue for me. I've spent the last year working on a very > real project management database in Access 2003. At the moment, I have 40 > users, and a week from Monday, I'm going to have about 100. Just within the > past three weeks, my users have started to encounter the Write Conflict > error I described in my first post. Other posters has given you many excellent advices. I just want to rule out one more cause of write conflict: It is possible that the write conflict errors are bogus because of VBA coding stepping on itself or on the Access. A good way to do this is to have VBA execute a separate query that modifies the same record that is being edited in the form. This comes out as two separate connection and of course the software (whether it's Access or the backend RDBMS) has no idea that the two separate connection are actually the same application/user and perceive it as deadlock for this reason. Therefore, if your VBA code behind the forms does use queries that update the same record or maybe different record on the same table that could be on the same page, this will cause write conflicts. If your code doesn't have that, then you're probably good and want to look at others' excellent solutions. Best of luck.
From: Paul on 16 Jan 2010 19:07 Interesting point, Banana. I am running several queries in the AfterUpdate event of several controls that append or upate other records in the same table. I wonder if the problem occurs when those records are on the same page. Howevr, there are no memo fields in those tables, but then if I understand it right, in those cases my option settings should enable Acces to only lock the edited record, not a group (page) of records. "Banana" <Banana(a)Republic> wrote in message news:4B51ED80.5080706(a)Republic... > Paul wrote: >> No, this is a real issue for me. I've spent the last year working on a >> very real project management database in Access 2003. At the moment, I >> have 40 users, and a week from Monday, I'm going to have about 100. Just >> within the past three weeks, my users have started to encounter the Write >> Conflict error I described in my first post. > > Other posters has given you many excellent advices. I just want to rule > out one more cause of write conflict: It is possible that the write > conflict errors are bogus because of VBA coding stepping on itself or on > the Access. A good way to do this is to have VBA execute a separate query > that modifies the same record that is being edited in the form. This comes > out as two separate connection and of course the software (whether it's > Access or the backend RDBMS) has no idea that the two separate connection > are actually the same application/user and perceive it as deadlock for > this reason. > > Therefore, if your VBA code behind the forms does use queries that update > the same record or maybe different record on the same table that could be > on the same page, this will cause write conflicts. > > If your code doesn't have that, then you're probably good and want to look > at others' excellent solutions. > > Best of luck.
From: Banana on 16 Jan 2010 20:36 Paul wrote: > Interesting point, Banana. I am running several queries in the AfterUpdate > event of several controls that append or upate other records in the same > table. I wonder if the problem occurs when those records are on the same > page. Howevr, there are no memo fields in those tables, but then if I > understand it right, in those cases my option settings should enable Acces > to only lock the edited record, not a group (page) of records. Well, depends on the context. For example, if you are using a continuous view or datasheet view, then you are certainly locking a group of records. As for the locking behavior, few points. 1) According to this: http://office.microsoft.com/en-us/access/HP052408601033.aspx "If you use record-level locking, Access locks only the record you are editing. This becomes the default behavior for access to data through a form, a datasheet, and code that uses a recordset object to loop through records, but not through action queries, nor through code that performs bulk operations using SQL statements." IOW, record-level locking is only available on forms and not via queries, so queries could then conflict with the forms' locking. 2) I also want to say that the setting you specify is a merely request and not a request so there is always a chance that the locking could get escalated by Access. However I was unable to find the supporting article for this, so take the second assertion with a huge grain of salt. Either way, I can tell you that I've been personally bitten by running action queries against the same table in middle of editing. But by being aware of how this works, we can certainly work with it. One posssible example is to avoid running queries while a record is dirty. Assuming no locks (or rather, more formally known as optimistic locking), running queries in say, form's AfterUpdate is usually fine (but a control's AfterUpdate may be insufficient because the record may be still dirty). Be aware, though, that this is not a complete solution in fact that you are just changing around the timeframe of when updates occurs and actually may be increasing the risk of contention in a high-concurrency scenario. Alternatively, working through the form's model is probably the best way to eliminate locking problems because that means you are now re-using the same 'connection' to do updates on other stuff. You have the Recordset property, RecordsetClone property, Bookmark property and several methods for DAO.Recordset objects to accomplish all this, and indeed, I do this for where I want to edit a related record on the same table. For bulk operations, I prefer action queries anyway, so that's back to the first suggestion. HTH.
From: Paul on 17 Jan 2010 02:27 Based on what you've said, and what I read in that Microsoft article you referenced, it would probably be a good idea to replace some of my action queries was DAO code, because I am performing action queries on continuous (tabular) view subforms. One approach I've already tried is to have the Timer event of the form save the form's record every few minutes to prevent against people leaving an edited record unsaved for long periods of time. Thanks for the explanations, Banana. P
From: Tom Wickerath AOS168b AT comcast DOT on 17 Jan 2010 03:44
Hi Paul, Thanks for the nice compliment on my Multiuser Applications paper! > However, they, along with my combo box, are also empty. Is there a way I > could get both the combo box and the form to display the first record in the > recordset when the form first opens? Sure, you just need to uniquely identify the record in question, and use the appropriate WHERE clause. So, instead of using WHERE 1=0, use something like: WHERE [PrimaryKeyFieldName] = NumericValue (for a numeric PK field) or WHERE [PrimaryKeyFieldName] = 'TextValue' (for a text-based PK field) Notes: Use the quotes surrounding the TextValue for a text-based value. You can specify a different field as well, as long as the field has a unique index, in order to retrieve just one record. Tom Wickerath Microsoft Access MVP http://www.accessmvp.com/TWickerath/ __________________________________________ "Paul" wrote: > Tom, > > It was your excellent article at > > http://www.accessmvp.com/TWickerath/articles/multiuser.htm > > that launched me on this path in the first place. I've got two tables with > a memo field, and I plan to isolate them into separate 1 to 1 tables in the > coming weeks. I already took a run at it and quickly realized that I'll be > spending several weeks debugging all the changes required by the new table > structure. I thought that in the meantime, I'd take the step of loading one > record at a time into the main form, instead of multiple records. > > I found your suggestion to set the RecordSource in the form's Load event to > > > SELECT * FROM table WHERE 1=0 > > to be very helpful, because now when the form loads all of the controls and > subforms are visible. > > However, they, along with my combo box, are also empty. Is there a way I > could get both the combo box and the form to display the first record in the > recordset when the form first opens? > > (If it requires DAO or ADO code, a dumbded-down answer in the form of the > actual code would be most welcome). > > Thanks > > Paul |