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 » General Discussion
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 20th, 2010, 03:14 PM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Marshall Barton
external usenet poster
 
Posts: 5,361
Default trying to minimize Write Conflicts in a multi-user database

Tom Wickerath AOS168b AT comcast DOT net wrote:
I missed where you said which version of Jet and/or Ace you
used in the tests??


Access 2003, SP-2.



Thanks for all your effort Tom, it was very enlightening.

--
Marsh
MVP [MS Access]
  #52  
Old January 20th, 2010, 10:35 PM posted to microsoft.public.access,microsoft.public.access.formscoding
Armen Stein[_2_]
external usenet poster
 
Posts: 157
Default trying to minimize Write Conflicts in a multi-user database

On Tue, 19 Jan 2010 22:11:17 -0800, "Paul" 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.

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.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

  #53  
Old January 21st, 2010, 05:12 AM posted to microsoft.public.access,microsoft.public.access.formscoding
David W. Fenton
external usenet poster
 
Posts: 3,373
Default trying to minimize Write Conflicts in a multi-user database

"Paul" wrote in
:

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/
  #54  
Old January 21st, 2010, 05:16 AM posted to microsoft.public.access,microsoft.public.access.formscoding
David W. Fenton
external usenet poster
 
Posts: 3,373
Default trying to minimize Write Conflicts in a multi-user database

Armen Stein wrote in
:

On Tue, 19 Jan 2010 22:11:17 -0800, "Paul"
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/
  #55  
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/
.

  #56  
Old January 21st, 2010, 05:47 PM posted to microsoft.public.access,microsoft.public.access.formscoding
Armen Stein[_2_]
external usenet poster
 
Posts: 157
Default trying to minimize Write Conflicts in a multi-user database

On 21 Jan 2010 04:16:03 GMT, "David W. Fenton"
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

  #57  
Old January 21st, 2010, 07:59 PM posted to microsoft.public.access,microsoft.public.access.formscoding
David W. Fenton
external usenet poster
 
Posts: 3,373
Default trying to minimize Write Conflicts in a multi-user database

Armen Stein wrote in
:

On 21 Jan 2010 04:16:03 GMT, "David W. Fenton"
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/
  #58  
Old January 21st, 2010, 09:02 PM posted to microsoft.public.access,microsoft.public.access.formscoding
Armen Stein[_2_]
external usenet poster
 
Posts: 157
Default trying to minimize Write Conflicts in a multi-user database

On 21 Jan 2010 18:59:52 GMT, "David W. Fenton"
wrote:

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


Okay, I see your point in this case. I don't think we've ever used
Bookmark navigation on an editable record - we use it a lot on
read-only continuous forms, where this bug wouldn't occur.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

  #59  
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


  #60  
Old January 22nd, 2010, 11:02 PM posted to microsoft.public.access,microsoft.public.access.formscoding
David W. Fenton
external usenet poster
 
Posts: 3,373
Default trying to minimize Write Conflicts in a multi-user database

Armen Stein wrote in
:

On 21 Jan 2010 18:59:52 GMT, "David W. Fenton"
wrote:

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


Okay, I see your point in this case. I don't think we've ever
used Bookmark navigation on an editable record - we use it a lot
on read-only continuous forms, where this bug wouldn't occur.


But I was only using it as a clear case of where you really *must*
force the save explicitly. I believe that navigating out of a
subform is another such situation where you should explicitly save,
rather than depending on the implicit save occuring without problems
(or bugs!).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
 




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 03:36 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.