If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#51
|
|||
|
|||
trying to minimize Write Conflicts in a multi-user database
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 OLEDBatabase 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" wrote in : 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/ . |
#52
|
|||
|
|||
trying to minimize Write Conflicts in a multi-user database
My thanks to all the luminaries who contributed to this conversation, for
their help and suggestions. Some of the more advanced topics discussed were over my head, but you've given me some great ideas for eliminating the write conflicts I had originally asked about. And thanks again for that handy navigation bar, John. Paul |
Thread Tools | |
Display Modes | |
|
|