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 15 Jan 2010 14:14 Tom, It was your excellent article at http://www.accessmvp.com/TWickerath/articles/multiuser.htm that launched me on this path in the first place. I've got two tables with a memo field, and I plan to isolate them into separate 1 to 1 tables in the coming weeks. I already took a run at it and quickly realized that I'll be spending several weeks debugging all the changes required by the new table structure. I thought that in the meantime, I'd take the step of loading one record at a time into the main form, instead of multiple records. I found your suggestion to set the RecordSource in the form's Load event to > SELECT * FROM table WHERE 1=0 to be very helpful, because now when the form loads all of the controls and subforms are visible. However, they, along with my combo box, are also empty. Is there a way I could get both the combo box and the form to display the first record in the recordset when the form first opens? (If it requires DAO or ADO code, a dumbded-down answer in the form of the actual code would be most welcome). Thanks Paul
From: Paul on 15 Jan 2010 14:22 > If you need help building that code, post back. John, YES, PLEASE! I'd never be able to figure it out on my own. I've taken several runs at trying to understand DAO and ADO coding, but I still struggle with it. The only MS Access book I ever found that explained it in a way I could understand it was the manual for Access 2.0, which I only borrowed temporarily. But I didn't use it right away, and didn't manage to retain what I understood from it. If you know of any Web sites that explain the basics of DAO and ADO, I'd love to check them out. And thanks for your help with this. Paul
From: Paul on 15 Jan 2010 15:07 Jack, I tried using Me.Combo = Me.Combo.Column(Me.Combo.ListIndex + 1) and the combo box and form still open with no records. I also tried assigning a number to Me.Combo.ListIndex, Me.Combo.ListIndex = 0 but I got and error message saying I've "Used the ListIndex property incorrectly. As I've said in other replies in this thread, I've got the form opening with no records initially, and as soon as I make the selection in the combo box, the selected record appears in both the combo box and the form. What I'm trying to accomplish is for both the combo box and the form to be populated with the "first" record in the recordset (ListIndex = 0) when the form loads. Please let me know if you have any ideas about how to accomplish this. I've asked the same question in my reply to Tom's last message, and I'm not sure if it's proper newsgroup ettiquette to repeat the same question to another participant in the conversation, but if not, I apologize for the redundancy. Paul
From: GBA on 15 Jan 2010 19:11 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 > > > . >
From: Paul on 16 Jan 2010 06:42
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 |