Susan Harkins
ssharkins at gmail.com
Tue May 27 17:02:22 CDT 2008
John, are you asking how to use disconnected recordsets or are you trying to talk yourself into writing a class instead? I've got a good basic article on using disconnected recordsets on techrepublic.com, if you're interested. There's also a much more involved article on the subject in an old issue of IMA. I could look up the issue, but unless you have it... none of their stuff's assessable online. Susan H. > 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