A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

trying to minimize Write Conflicts in a multi-user database



 
 
Thread Tools Display Modes
  #51  
Old January 21st, 2010, 07:49 AM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Tom Wickerath
external usenet poster
 
Posts: 3,914
Default 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  
Old January 22nd, 2010, 09:07 AM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Paul
external usenet poster
 
Posts: 68
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.