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 |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
trying to minimize Write Conflicts in a multi-user database
just a sanity check as to whether this string is purely theoretical or not.
Have done many many multi user applications with nary a write conflict....Access out of the box works really well in this area and so I wonder if the issue is theoretical or real.... "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 . |
#9
|
|||
|
|||
trying to minimize Write Conflicts in a multi-user database
No, this is a real issue for me. I've spent the last year working on a very
real project management database in Access 2003. At the moment, I have 40 users, and a week from Monday, I'm going to have about 100. Just within the past three weeks, my users have started to encounter the Write Conflict error I described in my first post. I work in a very real state government agency. My colleagues are using it to manage their projects, and I'll list (hey - you asked if it was real) just a few of the features that make it more than a card filing system: * Every night my VBA code runs 42 queries that append and update data in our application from an Oracle database, SQL Server and another Access database. They also upload different data to that other Access database. * In addition to projects, it also manages leases, contacts, activity nd documents. * It uses the OS login name to distinguish between editable and read only records, depending on whether the user is a team member of the project * there are 5 user classes - user, admin, admin User, read only and a 5th one that I can't recall at the moment - and depending on which class the user belongs, different forms and different form controls will be presented to the user. * It's also a document processing file manager. My users process lots of contracts and documents, and my application enables them to select from hundreds of documents in Word, Excel, PDF and html from a shortcut menu sysem, and it populates fields in the documents with data in the database. It also saves the files into the project folder on the network drive, so the user doesn't have to navigate through Windows explorer to find the project folder. We have conservatively estimated that the file management module I just described is saving our agency and the taxpayer the time equivalent of over $500,000 per year. Maybe you don't have a problem because you designed your database better than I did. I do have memo fields in two tables, and Tom Wickerath has pointed out that could be the problem. But yes, it's a real database, and I'm dealing with a very real problem. Paul |
#10
|
|||
|
|||
trying to minimize Write Conflicts in a multi-user database
Paul wrote:
No, this is a real issue for me. I've spent the last year working on a very real project management database in Access 2003. At the moment, I have 40 users, and a week from Monday, I'm going to have about 100. Just within the past three weeks, my users have started to encounter the Write Conflict error I described in my first post. Other posters has given you many excellent advices. I just want to rule out one more cause of write conflict: It is possible that the write conflict errors are bogus because of VBA coding stepping on itself or on the Access. A good way to do this is to have VBA execute a separate query that modifies the same record that is being edited in the form. This comes out as two separate connection and of course the software (whether it's Access or the backend RDBMS) has no idea that the two separate connection are actually the same application/user and perceive it as deadlock for this reason. Therefore, if your VBA code behind the forms does use queries that update the same record or maybe different record on the same table that could be on the same page, this will cause write conflicts. If your code doesn't have that, then you're probably good and want to look at others' excellent solutions. Best of luck. |
|
Thread Tools | |
Display Modes | |
|
|