[AccessD] Upsizing (was: Desperately Seeking!)

Dan Waters dwaters at usinternet.com
Fri May 2 15:17:59 CDT 2003


Arthur,

The databases I design typically handle some type of QA process in a
manufacturing environment.  One of the advantages of using an electronic
system over a paper form system is the ability for people to work
(almost) in parallel - which leads to the real occurrence of
simultaneous updates.  So, I always use pessimistic locking, and on the
one process where people felt slowed down, I installed a timer on that
form so that it closed after 5 minutes with no activity, releasing the
locks on the table (that was in A97).

I am starting to look at upsizing this db to SQL 2000.  From what I
understand, I may not be able to use pessimistic locking the same way
that I did in AXP.  

If this is the case, what is the best way to duplicate the pessimistic
locking behavior of AXP in SQL 2K?

Thanks,
Dan Waters


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Friday, May 02, 2003 12:27 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Desperately Seeking!


<rant>
I don't feel like rekindling any bound/unbound wars, so instead I'll try
another tack. The problem is disconnected recordsets, which are very
cool if the liklihood of simultaneous updates of a row is remote. In
fact, it's not so much a database problem as a business practices
problem, IMO. What the hell are two people updating the same row for?
There's a problem here and it doesn't concern the database; it concerns
the workflow, which by definition is outside the specifications. OTOH,
excellent arguments from the db folk occasionally persuade management
that the problem is indeed outside the db, and should be addressed by
someone other than you. </rant>

<reality>
Given that you must prevent simultaneous updates of a set of rows, and
given that you have taken the unbound path, without a massive rewrite I
think your quickest option is to revisit all the recordset declarations,
setting Pessimism TRUE in your rs.open() arguments. This effectively
offloads the problem to the db, which is good, since it won't allow your
users to overwrite each other even if your app in theory does. (That's
why I always put all the smarts into sprocs and rules and such, rather
than coding them in Access. Then, even if your app is buggy, it doesn't
matter:-) Lock it all down with pessimistic recordsets and go from
there. You can probably visit every occurenece with a project-wide
search, and paste the revised string in. </reality>

<fantasy>
I'm having difficulty coming up with an example where multiple users
would want to update the same rows. A wife and husband are both phoning
to complain about a VISA charge? Three employees are calling from Client
X to revise the purchase quantities in the details of OrderID 2345?

Multiple people updating identical rows = a workflow problem, IMO. In a
well-designed workflow, this should never occur.

Making this point at your next meeting, you are given a 100% salary
boost, several opportunities for sex with strangers, and the pleasure of
watching the entire organization reconfigure itself to your insights.
Suddenly they appreciate how brilliant you are and shape their
enterprise around you. Billions at stake, all on your mind. </fantasy>

Arthur

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Marcus
Tewksbury
Sent: May 2, 2003 10:16 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Desperately Seeking!


Can Anyone Help Me?

I have built a client server application using .adp front end and SQL
Server back end.  Within the application itself I use unbound forms and
retrieve records using ADO recordsets at run time.  The way I have
initially deployed the application is to copy an instance of the .adp to
each desktop and run it locally.  The problem has been that people keep
overwriting each other's updates - and changes are not reflected fast
enough.

I have a couple of different thoughts on how to tackle this - either
ratchet down the ODBC refresh rate, or run a single, centralized copy of
the .adp (which throws up some non-updateable warning every time it
starts which I don't know how to suppress).  Of course, I acknowledge
that I am a total newbie, and both of these options may be flawed.

Thanks a bunch,

- Sherri





_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail

_______________________________________________
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