[AccessD] VBA Unbound data entry / update form

Jim Dettman jimdettman at verizon.net
Wed May 28 18:42:57 CDT 2008


Couple of comments on this:

1. You do not want to use a field within a record to determine if a record
is "busy" or not.  Use a separate table.  The main reason is that in the
case of an abnormal disconnect, you are then forced to scan the entire table
and clear that field for a user or users.

  There is also the issue that you would need to lock the entire table while
placing a lock (two users go after the same record at the same time).  This
will cause major concurrency issues.

2. Any locking scheme should be centralized so it can easily be changed.
For example, suppose rather then a simple busy/no busy flag, you want the
ability for:

A. All users being able to read a record.
B. All users being able to read, but have only one able to update.
C. Give one user exclusive read/write access to a record.

  If you've put a simple "this is the user that has this locked" field in
every table and now need a locking level, you need to change every table.
With a central table, it's one change.

3. Generalize the concept of placing locks.  There are many resources that
require locking; printers, disks, records, tables, a process (ie an
accounting close), maybe a module (keep A/R closed for all but one user), a
company, or an entire system.

  To accomplish this, don't focus on record locking itself, but locking a
resource.  Typically what I do is use a "resource tag".  Ie. For a record,
it might be:

<table Name>PK<primarykey>

Or for an entire table simply <table name>

 I've also done one system where I had a resource type field.  You could
just as easily prefix a resource tag. It really doesn't matter what scheme
you come up with, just that it's consistent.

4. Username in of itself is insufficient for locking.  What if I login at
more then one station?  Or have more then one instance running on a specific
station? Username and station name is better, but if you plan to allow
multiple instances, you need user name, station name, and a window handle to
come up with a unique user key.  You could also use GUID, but would need to
log the GUID's to a table along with user and station (aka LDB locking in
JET).

HTH,
Jim.



   

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Wednesday, May 28, 2008 9:01 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] VBA Unbound data entry / update form

I think you are misunderstand what I mean by Lock the Record.  In your code,
check if the record's new field (call it what you want), "I am locked By
UserName" is empty or not.  If it is empty, put an entry in it and then
export the data to your working temp table.  If it is not empty, then
somebody else is working on the record and the user gets advised of that
fact.  The is no system lock involved.  Only the logical one that you
created and which is handled by your code.  There are no decisions to make
as regards what to write back.  Write the lot back.  You have the record
safeguarded via your code (caveats re sideways access excepted).  Repeat -
there are no system locks involved in this, therefore no other unrelated
records being locked.

HTH
Max
Ps. It doesn't have to be a field that is used to indicate a "locked
Record", you could use a global variable or write to a text file to say "PK
is in use" and then delete that line when no longer required.  As long as
your code checks that before allow edits then it should work.  However,
there is a chance that two users may still collide when trying to do the
"user locking routine", so you could use a temp table with a unique index on
a field called PK.  This would prevent two records being created for the
same PK as the main table and thus allocated to the first user to request
locking and which will prevent new records being added until the previous
record has been destroyed thus preventing duplicate simultaneous attempts -
holds the PK of the record needing to be "locked".  Sorry, bit rushed that
but I need to move on.  Hope it helps anyway.


 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, May 28, 2008 12:17 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] VBA Unbound data entry / update form

Max,

 > Won't work John.

 >>Make the changes.  When done, look up the original record in the real
table, compare data fields from temp to real. 
If there are any changes THEN make the appropriate decisions etc.  This
still requires a fair amount of logic to determine "old values" and the
like...

Lock fields, lock notices, timeouts etc are a "fair amount of logic" as
well.

 > 2. Lock it as soon as record is accessed/edited.

I can't do this.  The lock affects other entirely unrelated records due to
memo and index "page locks".  THAT IS WHAT I AM TRYING TO WORK AROUND!

John W. Colby
www.ColbyConsulting.com


Max Wanadoo wrote:
> Won't work John.
> If you have User 1 making changes, you need to ensure that User 2 is 
> not making simultaneous changes to the same data albeit via a temp table.
> Until you do that, nothing else makes any sense.
> I would:
> 1. Put a field on the table "I am locked By UserName".
> 2. Lock it as soon as record is accessed/edited.
> 3. Warn other users that records is being edited by UserName if they 
> try to access it.  Ask them do they want to be notified when record 
> becomes available.  If so, record PK and User in a temp table or in 
> variable array and check it via an OnTimer event.
> 4. You can also put a Time-Out default if UserName left a record 
> Locked when he/she went off to luch.  Time-out and lock/leave the 
> record thus unlockign it for others.
> 5. KIS works for me.
> Max
> Ps. Also be aware of what you need to do if the record is locked by 
> being reported on via a Report/update batch run, etc.  Remember record 
> will be incomplete.

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list