Jim Lawrence (AccessD)
accessd at shaw.ca
Fri May 2 21:05:59 CDT 2003
Hi Athur: I am sure not going to change your opinion or will I try. From my experience, judging by our years in the business, are equivalent; plus 25 years. My first experience with ADO and Access started with Access97. One client was having weird data problems and about two weeks of data was lost before panic set in. The problem was discovered to be a bound Access MDB, always left on by some of the office personnel, the remote system support staff doing fixes and forcing auto-server reboots and a backup over-lapping once a week... We made the bold move to go to an ADO-OLE middle tier and a SQL7 BE. This is where I discovered how to use recordsets. For the last five years, I have been using the same model. A client has never lost a record to over-write, line connection drop, system crashes using the design model I have been using. I would not be able to say that if I was using the persistent connection model. No web application would ever be able to use the persistent connection method. The standard method is to store the 'session' id with the selected record and there fore marking the records as locked. By default, if 15 minutes goes by and the transaction is not finished, the session id is cleared from the record along with the session. If a bound model could even be implemented on the web, too much resources would be consumed considering hit levels could exceed a thousand per hour. The bound model may be a perfect choice for a smaller local site but unbound model for medium and large WAN sites is not an option. MS are not always wrong. IMO Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Arthur Fuller Sent: Friday, May 02, 2003 6:08 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Upsizing (was: Desperately Seeking!) Well here we go into uncharted waters. Certain publications by MS suggest that unbound forms are the way to go. I vigourously dispute this. I think that bound forms tied to sprocs are the way to go. Then you can sidestep a zillion unbound-form problems. But here I go proselitizing bound forms when that was not my original intention. IMO, and that's all it is, I don't claim expertise only experience, bind your forms to sprocs. Recently I learned a cool trick which lets me pass arguments like "All", "All" to various forms, reports &c. Everything works from sprocs. Recordsets open their stuff pessismistically. This is a simple argument to the rs.Open method. Let intelliSense do your typing :-) IME you're far better off to go with boound forms and pessimistic logic than unbound disconnected recordsets, given the logic of your scenario. Which I would question, but that's a side issue. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters Sent: May 2, 2003 4:18 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Upsizing (was: Desperately Seeking!) 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 _______________________________________________ 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