trying to minimize Write Conflicts in a multi-user database
in [General]
|
Prev: não acho meu orkut
Next: maike yordano pirela vera
From: Paul on 14 Jan 2010 13:57 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
From: John Spencer on 14 Jan 2010 14:13 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 > >
From: Paul on 14 Jan 2010 14:36 Then it sounds like it's well worth the time to make the changes. Thanks, John. "John Spencer" <spencer(a)chpdm.edu> wrote in message news:egZFs2UlKHA.4872(a)TK2MSFTNGP05.phx.gbl... >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
From: Paul on 14 Jan 2010 15:27 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" <spencer(a)chpdm.edu> wrote in message news:egZFs2UlKHA.4872(a)TK2MSFTNGP05.phx.gbl... >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
From: Paul on 14 Jan 2010 18:21
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 |