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
  #1  
Old January 14th, 2010, 07:57 PM posted to microsoft.public.access,microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries
Paul
external usenet poster
 
Posts: 126
Default trying to minimize Write Conflicts in a multi-user database

I have a multi-user Access 2003 database and lately a number of our users
are running into the Write Conflict message gives them the choice to either
Save Record, Copy to Clipboard or Drop Changes.

The problem occurs in a tabbed form with subforms on the different pages.

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).

In effect, you'd only have one record open at a time from the parent table
instead of loading multiple records into the the main form at once.

Would this in fact help reduce the number of Write Conflicts? I ask because
there are a number of events in the main form the various subforms what
would have to be modified, and I'd like to confirm that it will accomplish
something before I spend the time making those changes.

Thanks in advance,

Paul


  #2  
Old January 14th, 2010, 08:13 PM posted to microsoft.public.access,microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default trying to minimize Write Conflicts in a multi-user database

I have a similar set up, but I only have one record open on the parent form at
any time. The user selects from a combobox (or by a search that presents them
with a limited number of records). I use this information to identify which
main record they want. Then I execute a query that returns JUST that one
record to the main form.

It is very fast and works nicely and almost no conflicts ever occur.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Paul wrote:
I have a multi-user Access 2003 database and lately a number of our users
are running into the Write Conflict message gives them the choice to either
Save Record, Copy to Clipboard or Drop Changes.

The problem occurs in a tabbed form with subforms on the different pages.

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).

In effect, you'd only have one record open at a time from the parent table
instead of loading multiple records into the the main form at once.

Would this in fact help reduce the number of Write Conflicts? I ask because
there are a number of events in the main form the various subforms what
would have to be modified, and I'd like to confirm that it will accomplish
something before I spend the time making those changes.

Thanks in advance,

Paul


  #3  
Old January 14th, 2010, 08:36 PM posted to microsoft.public.access,microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries
Paul
external usenet poster
 
Posts: 126
Default trying to minimize Write Conflicts in a multi-user database

Then it sounds like it's well worth the time to make the changes.

Thanks, John.


"John Spencer" wrote in message
...
I have a similar set up, but I only have one record open on the parent form
at any time. The user selects from a combobox (or by a search that
presents them with a limited number of records). I use this information to
identify which main record they want. Then I execute a query that returns
JUST that one record to the main form.

It is very fast and works nicely and almost no conflicts ever occur.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Paul wrote:
I have a multi-user Access 2003 database and lately a number of our users
are running into the Write Conflict message gives them the choice to
either Save Record, Copy to Clipboard or Drop Changes.

The problem occurs in a tabbed form with subforms on the different pages.

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).

In effect, you'd only have one record open at a time from the parent
table instead of loading multiple records into the the main form at once.

Would this in fact help reduce the number of Write Conflicts? I ask
because there are a number of events in the main form the various
subforms what would have to be modified, and I'd like to confirm that it
will accomplish something before I spend the time making those changes.

Thanks in advance,

Paul



  #4  
Old January 14th, 2010, 09:27 PM posted to microsoft.public.access,microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries
Paul
external usenet poster
 
Posts: 126
Default trying to minimize Write Conflicts in a multi-user database

John,

After reading your message again, it occurred to me I would simplify things
a lot if I kept the main form data there in the main form like you said you
were doing it instead of moving it into a subform like I had planned. If I
did that, what's the best way to load a single record into the main form
from the After Update event of a combo box?

Would it be:

DoCmd.OpenForm stDocName, , , stLinkCriteria

or is there a better way to do it?

Thanks again in advance,

Paul



"John Spencer" wrote in message
...
I have a similar set up, but I only have one record open on the parent form
at any time. The user selects from a combobox (or by a search that
presents them with a limited number of records). I use this information to
identify which main record they want. Then I execute a query that returns
JUST that one record to the main form.

It is very fast and works nicely and almost no conflicts ever occur.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Paul wrote:
I have a multi-user Access 2003 database and lately a number of our users
are running into the Write Conflict message gives them the choice to
either Save Record, Copy to Clipboard or Drop Changes.

The problem occurs in a tabbed form with subforms on the different pages.

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).

In effect, you'd only have one record open at a time from the parent
table instead of loading multiple records into the the main form at once.

Would this in fact help reduce the number of Write Conflicts? I ask
because there are a number of events in the main form the various
subforms what would have to be modified, and I'd like to confirm that it
will accomplish something before I spend the time making those changes.

Thanks in advance,

Paul



  #5  
Old January 15th, 2010, 12:21 AM posted to microsoft.public.access,microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries
Paul
external usenet poster
 
Posts: 126
Default trying to minimize Write Conflicts in a multi-user database

I'm working on implementing John Spencer's description of a parent form that
has only one record open at a time, and three questions occur to me:

1. Do you use DoCmd.OpenForm stDocName, , , stLinkCriteria in the
AfterUpdate event of a combo box to move between records?

2. You would be using a SQL SELECT statement to populate the combo box used
to navigate between records once the form is open, but how do you determine
which record to open when you initially load the form? That is, how do you
extract the value of the first record (or for that matter, any record) in
that SQL statement to use as the stLinkCriteria when you first open the
form?

3. Since the main form has only one record loaded at any time, the normal
navigation buttons won't be able to do anything. Are there any Web sites
that address building custom navigation buttons for single-record forms?

Thanks in advance,

Paul


  #6  
Old January 15th, 2010, 02:15 AM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Jack Leach
external usenet poster
 
Posts: 33
Default trying to minimize Write Conflicts in a multi-user database

I'm not sure about question 3 (I haven't done this before, but I will
certainly keep it in mind, it seems an excellent idea).

For question 1), you wouldn't be using the boilerplate DoCmd.OpenForm method
and criteria... you presumably already have the form open, you just need to
give it a recordsource...

Private Sub Me.cboRecords AfterUpdate()
Me.Recordsource = "SELECT * FROM table WHERE [ID] = " & Me.cboRecords
Me.Requery
End Sub

Ideally, this would be a saved query name that pulls the value direct from
the combo on the form... performance would be better like that, I believe.

As for Q2, it depends what you want your first record to be... but what I
would do is move the above code into its own procedure rather than the
afterupdate, then you can call the procedure from both the Open event and the
combo AfterUpdate event:


Private Sub psGoToRec(lRecID As Long)
Me.Rowsource = "SELECT * FROM Table WHERE [ID] = " & lRecID
Me.Requery
End If

Private Sub cboRecord_AfterUpdate()
psGoToRec(lRecID)
End Sub

Private Sub Form_Open(Cancel As Integer)
psGoToRec(your default opening id here)
End Sub



I would be curious to see a reply for the navigation (and new records)
portion of the question... no lights are shining in my head on that at the
moment...
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



"Paul" wrote:

I'm working on implementing John Spencer's description of a parent form that
has only one record open at a time, and three questions occur to me:

1. Do you use DoCmd.OpenForm stDocName, , , stLinkCriteria in the
AfterUpdate event of a combo box to move between records?

2. You would be using a SQL SELECT statement to populate the combo box used
to navigate between records once the form is open, but how do you determine
which record to open when you initially load the form? That is, how do you
extract the value of the first record (or for that matter, any record) in
that SQL statement to use as the stLinkCriteria when you first open the
form?

3. Since the main form has only one record loaded at any time, the normal
navigation buttons won't be able to do anything. Are there any Web sites
that address building custom navigation buttons for single-record forms?

Thanks in advance,

Paul


.

  #7  
Old January 15th, 2010, 05:22 AM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Paul
external usenet poster
 
Posts: 126
Default trying to minimize Write Conflicts in a multi-user database

Thanks for the help with this, Jack. The code you suggested:

Me.Recordsource = "SELECT * FROM table WHERE [ID] = " & Me.cboRecords

works great in the combo box's AfterUpdate event because it retrieves the
desired record instantly, and leaves only the single record in the form,
which is exactly what I want.

However, I'm having trouble getting it to work in the form's Load event
because the combo box is null, and the assignment statement above results in
an error since Me.cboRecords is null. Similarly, if I try to put the value
of the combo box in the criteria field of the form's query, the form is
blank because the value in the combo box is null. Is there any way I can
force the combo box to retrieve one of its own records (the first one would
be ok) as soon as the form loads, so it can provide a non-null value for the
ID in the assignment statement and the criteria of the form's query?

I've tried to Requery the combo box before setting the RecordSource of the
form, but that didn't seem make a difference.

Any suggestions how I can overcome problem of the empty combo box when the
form loads?

Paul


  #8  
Old January 15th, 2010, 08:23 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

Hi Paul,

Try setting the Recordsource for the form to this SQL statement (or to a
saved query with this SQL statement):

SELECT * FROM table WHERE 1=0

This query is guaranteed to return zero records. Remove the code that you
had in the Form_Load event, which was apparently running the query that
grabbed criteria from the combo box.

To address your original question about minimizing Write Conflict errors,
Are there any tables that include memo, hyperlink, OLE Object, or the new
multi-value field (Access 2007 .accdb only)? The reason I ask is that these
fields can cause page locking to be invoked, when you might otherwise assume
that record locking is being used. For JET databases, I have gotten into the
habit of breaking memo fields out to a separate table, with a 1:1
relationship, instead of including the memo field in the same table with
other fields. This way, when a user clicks into the memo field on a form,
they have immediately committed any changes to the parent record and
vice-versa, clicking out of the memo field on the form to any other field
commits changes to the memo data. I base this on the following quote from
Microsoft:

"Also, record-level locking is not enabled for Memo data types."

Source: http://support.microsoft.com/kb/275561, under the title: "Record-level
locking".


Another reason for moving memo fields to their own table is so that I never
run
into this situation:
http://support.microsoft.com/kb/296389

Keep in mind that hyperlink and OLE Object fields involve the same pointer
mechanism that memo fields do, so the above discussion applies to these data
types equally well.

The other thing that I've been doing in all my released applications for the
past couple of years is running code at startup, via an Autoexec macro, that
uses ADO to establish record level locking for the first person to open the BE
database. Subsequent users will connect to the BE database with the same
locking
that the initial user establishes:

http://support.microsoft.com/?id=306435

Michael Kaplan points out on his blog site that when a user selects the option
to use Record Level locking, that this is only a request, not a demand. So, by
using the ADO code in the above article, you are essentially demanding record
level locking.

This KB article clearly states this for the new MVF data type in Access 2007
causes page locking:

http://support.microsoft.com/kb/918578


Hope this helps some.

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Paul" wrote:

Thanks for the help with this, Jack. The code you suggested:

Me.Recordsource = "SELECT * FROM table WHERE [ID] = " & Me.cboRecords

works great in the combo box's AfterUpdate event because it retrieves the
desired record instantly, and leaves only the single record in the form,
which is exactly what I want.

However, I'm having trouble getting it to work in the form's Load event
because the combo box is null, and the assignment statement above results in
an error since Me.cboRecords is null. Similarly, if I try to put the value
of the combo box in the criteria field of the form's query, the form is
blank because the value in the combo box is null. Is there any way I can
force the combo box to retrieve one of its own records (the first one would
be ok) as soon as the form loads, so it can provide a non-null value for the
ID in the assignment statement and the criteria of the form's query?

I've tried to Requery the combo box before setting the RecordSource of the
form, but that didn't seem make a difference.

Any suggestions how I can overcome problem of the empty combo box when the
form loads?

Paul

  #9  
Old January 15th, 2010, 02:12 PM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
John Spencer
external usenet poster
 
Posts: 7,815
Default trying to minimize Write Conflicts in a multi-user database

As for question 3

3. Since the main form has only one record loaded at any time, the normal
navigation buttons won't be able to do anything. Are there any Web sites
that address building custom navigation buttons for single-record forms?

You can build code to move next or move previous based on the combobox you are
using to get the record. You just need to know which record you are on and
where that is in the combobox. Then get the value of the previous/next row in
the combobox, set the combobox to that row and execute the code to select the
record based on that value.

If you need help building that code, post back. If I have some free time
later today, I will try to help.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
  #10  
Old January 15th, 2010, 04:08 PM posted to microsoft.public.access.formscoding,microsoft.public.access.multiuser,microsoft.public.access.queries,microsoft.public.access
Jack Leach
external usenet poster
 
Posts: 33
Default trying to minimize Write Conflicts in a multi-user database

For a combo...

The .ListIndex property (0 based) tells what item you have selected (or -1
for no selection). Use this and the .Column(.ListIndex) to get the the
currently selected record.

The .ListCount (1 based) tells how many list items there are in the source
of the combo. Use this to determine if the current selection is the last
one. If .ListIndex = .ListCount - 1 Then LastRecord = True

You should be able to "select" a record using the Column property:

Me.Combo = Me.Combo.Column(Me.Combo.ListIndex + 1)


This should give you an idea where to start.

I haven't tried yet, but am still a bit curious about how to handle a
recordsource of the main form for a new record... I *think* that, per Tom's
suggestion, you can set the SQL to WHERE 1 = 0 (which will display no
records), and be able to enter a new record this way. I think that's the
case, but haven't tested yet.

This really is a great idea, I will also begin to do some redesigning
(haven't had an issue with write conflicts as of yet, but that's not to say I
won't...) And many thanks to Tom for making the points on Memo, OLE and MVF
fields... I was not aware of this either.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)



"John Spencer" wrote:

As for question 3

3. Since the main form has only one record loaded at any time, the normal
navigation buttons won't be able to do anything. Are there any Web sites
that address building custom navigation buttons for single-record forms?

You can build code to move next or move previous based on the combobox you are
using to get the record. You just need to know which record you are on and
where that is in the combobox. Then get the value of the previous/next row in
the combobox, set the combobox to that row and execute the code to select the
record based on that value.

If you need help building that code, post back. If I have some free time
later today, I will try to help.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
.

 




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 12:26 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.