Rocky Smolin at Beach Access Software
rockysmolin at bchacc.com
Tue May 27 17:19:44 CDT 2008
Isn't that whole issue taken care of by dbOptomistic: "Uses optimistic locking to determine how changes are made to the Recordset in a multiuser environment. The page containing the record is not locked until the Update method is executed." Re: " if the record no longer exists then the record has been deleted and then what...? Inform the record that the record was deleted? Refuse to save the changes?" Yes and yes. Inform the record was deleted and don't save the changes. Re: " If not then the user has to be told that the data changed and... Ask the user if they want to overwrite the changes in the table with the changes they just made? Refuse to save their changes?" Fielder's choice depending on their application. If the business rule is that the second is allowed to overwrite the first then ask and do what they say. Otherwise inform that the record has been changed and don't allow the change. Although I think the first case should probably be the rule since if a user 1 is allowed to change the record in the morning and user 2 in the afternoon, then why not user 1 at 10:15 and user 2 at 10:16? It was unclear to me, however, that you're insisting on disconnected recordsets. Nevertheless, I still don't see a deal breaker. Open the recordset, get the record close the recordset, save the initial values in an array. When updating, open the recordset (lock the page), fetch the record, check to see if the original values (in your array) are still the same. Rocky Smolin Beach Access Software 858-259-4334 www.e-z-mrp.com www.bchacc.com -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Tuesday, May 27, 2008 2:54 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] VBA Unbound data entry / update form Rocky, The whole point of the unbound exercise is to open a single record, populate the form with the values, then close the recordset. If I am going to hold the record open then I might as well just bind the form and be done with it. When the user saves the data in the unbound form, the form needs to check if anything is dirty, if so then it needs to grab the record again, if the record no longer exists then the record has been deleted and then what...? Inform the record that the record was deleted? Refuse to save the changes? Assuming the record has not been deleted, then the updated fields have to check the same fields in the underlying record to see if the "old data" from the form (the data at the time the form opened) matches the data in the corresponding field in the record. If so then it can just be written back. If not then the user has to be told that the data changed and... Ask the user if they want to overwrite the changes in the table with the changes they just made? Refuse to save their changes? As you can see, disconnected records cause a lot of headaches that bound forms "just take care of". "Disconnected" means that no lock is placed, no recordset is held open, and dbPessimistic and dbSeeChanges are irrelevant. I am not an "unbound" kinda guy so I have not answered these questions, I am attempting to get answers for "best practices" on how to handle these and any other similar issues. The only reason I am even getting into this is that I have a couple of "choke points" where for some unknown, unexplained reason, I am getting "record locked" issues as the user tries to edit records. AFAICT these are not even records that other users are editing or even viewing. In fact I get "locked" records when trying to ADD A NEW RECORD... which indicates that the issue is way down in the guts of Access / JET in things like Index or memo pages. My THEORY is that if I make these forms unbound then these memo and index page locks will go away since there are no longer dozens of users trying to lock memo pages or index pages, except very briefly as they save new / changed records. Unfortunately, the "art of unboundedness" is arcane (at least to me) and I am not finding good solid references as to how to handle these things, nor what the results will be at the "jet guts" level. I tried to convince my clients to just move to SQL Server, which doesn't have these memo and index page lock issues, and let me go back to my peaceful bound form world. They are not doing that so here we are. John W. Colby www.ColbyConsulting.com Rocky Smolin at Beach Access Software wrote: > John: > > Won't dbPessimistic in the LockEdits parameter of the OpenRecordset do > what you need to have done with regard to multiple users attempting to > edit the same record? > > dbSeeChanges may also bee what you need. From the help: > > Use the dbSeeChanges constant in a Microsoft Jet workspace if you want > to trap changes while two or more users are editing or deleting the > same record. For example, if two users start editing the same record, > the first user to execute the Update method succeeds. When the second > user invokes the Update method, a run-time error occurs. Similarly, if > the second user tries to use the Delete method to delete the record, > and the first user has already changed it, a run-time error occurs. > > Typically, if the user gets this error while updating a record, your > code should refresh the contents of the fields and retrieve the newly > modified values. If the error occurs while deleting a record, your > code could display the new record data to the user and a message > indicating that the data has recently changed. At this point, your > code can request a confirmation that the user still wants to delete the record. > > HTH > > > Rocky Smolin > Beach Access Software > 858-259-4334 > www.e-z-mrp.com > www.bchacc.com > > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby > Sent: Tuesday, May 27, 2008 1:05 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] VBA Unbound data entry / update form > > LOL, I do want to do a lot with an unbound form. It should open a > record, display that record, allow it to be edited, and save the > record back again once edited, all without corrupting data, honoring > data saves before it saves etc. > Bound forms do all of that, and the unholy alliance of unbounders have > always indicated that they could also do all of this stuff. > > Now I need to do all that stuff and just thought those UAU members > would have their expertise to share. > > 8-0 > > John W. Colby > www.ColbyConsulting.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com No virus found in this incoming message. Checked by AVG. Version: 8.0.100 / Virus Database: 269.24.1/1469 - Release Date: 27/5/2008 13:25