[AccessD] VBA Unbound data entry / update form

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




More information about the AccessD mailing list