MartyConnelly
martyconnelly at shaw.ca
Wed Jul 14 21:22:58 CDT 2004
You could run a Replica farm on the server. I seem to remember Arthur advocating something like this. So that the first 20 user who logon get directed to one replica mdb, the next 20 to another etc. Just keep one backend mdb that tracks a user table of who is on. Then shovel them to the appropriate replica backend. Then consolidate the replicas once a day to the master. It might be faster to convert to SQL than create the replicas if you are not familar with it.. Christopher Hawkins wrote: >Connections might not have been the best word to use. Everything is >being done with bound forms; the hobbyist hwo built it does not know >how to use recordsets. I'm trying to figure out how many people are >actually doing CRUD operations against the database at a given time. > >-C- > >---- Original Message ---- >From: cfoust at infostatsystems.com >To: accessd at databaseadvisors.com, >Subject: RE: [AccessD] Monitoring concurrent connactions to the BE? >Date: Wed, 14 Jul 2004 13:12:43 -0700 > > > >>Define connections. Are you using ADO or DAO recordsets, and are you >>talking about concurrent users or concurrent connections (not the >>same >>thing). You can get info on current users by using the ADO >>OpenSchema >>method to get a Jet User Roster. It simply reports on the specific >>machines connected to the database and will also return logins if >>Access >>security is being used. I think Susan did an article on this for >>some >>publication, and I've used an elaboration of it in a utility we built >>for our customers. >> >>Charlotte Foust >> >>-----Original Message----- >>From: Christopher Hawkins [mailto:clh at christopherhawkins.com] >>Sent: Wednesday, July 14, 2004 11:32 AM >>To: accessd at databaseadvisors.com >>Subject: [AccessD] Monitoring concurrent connactions to the BE? >> >> >>Hello, all. I've got a client who's running a homegrown Access app. >>I probably don't even need to continue, you KNOW it's going to end up >>badly. ;) >> >>I'll ask my main question up front: is there a utility that will >>allow >>me to track the number of concurrent connections that are being made >>to >>a .mdb back-end? I need to know what the peak number of concurrent >>connections is for a given file, and I need to know what time frame >>that >>peak takes place in. >> >>Now, those of you who enjoy case studies can read the rest. >> >>THE PROBLEM: The FE's are locking up, forcing users to exit and >>re-enter >>the app. Records that were being viewed, added or edited at the >>time of >>the lock-up sometimes disappear and have to be re-keyed. >>Sometimes a record that was keyed in successfully will turn up >>missing >>later. >> >>THE SETUP: The back-end is an Access 2003 .mdb file about 500MB in >>size. >>Yes, even after compressing. The front-end is an Access 2003 .mde >>file >>with links directly to the back-end. >> >>THE INSTALL BASE: The FE is installed on 40-ish desktops locally, >>with >>another 40-ish users accessing the app via Terminal Services. >>All in all, there are 80-ish potential connections to the back-end. >>And frankly, this is where I think the problem is. >> >>THE USAGE PATTERN: The proscribed method of use is to add or update >>records as one works during the day. >> >>What is actually happening is that nobody uses the app at all until >>about 4pm, when EVERYONE logs in to do all their CRUD operations for >>the >>day. On Friday, it is 4pm all day long as people hammer the system >>to >>get things into the db that they blew off during the week. >> >>MY TAKE: The idea that 80 concurrent connections would slow or >>outright >>freeze an Access app makes sense. The idea that if you'd lose your >>record if Access froze in the middle of keying it makes sense, if >>less >>so; they're using bound forms, so I'd expect that whatever portion of >>the record was keyed pre-freeze would be saved. >>The idea that successfully keyed-in records would disappear at some >>unknown time between now and (for example) next week makes NO sense >>to >>me, however. If it's in, it's in, right? Even 255 concurrent >>connections won't cause data to be deleted. Someone has to >>explicitly >>delete it (even if they don't know they're deleting it), correct? >> >>WHAT I'M CHECKING: For the 'records get keyed in then disappear' >>issue >>I have made sure that warnings are turned on, and that there isn't >>any >>code turning them off without turning them back on. That eliminates >>the >>possibility that people are fat-fingering the Del key and killing >>records without knowing it. I have also checked their settings; >>Default >>Record Locking is set to No Locks. >> >>WHAT I WANT: I want a utility that will let me track the number of >>concurrent connections that are being made to the back-end at any >>given >>time. In addition to knowing what the peak number of connections >>is, I >>want a way to know at what time that peak is hit. >>I want to see the connection count increasing, up the peak, and >>decreasing as people log off. I need to pinpoint the period of >>heaviest >>load. >> >>Now, before anyone suggests it, I have already counseled the client >>to >>move to SQL Server and to their credit, that project is on the >>schedule! >>My immediate concern is to keep their Access app alive and useful >>until >>the transition to SQL takes place - the app is absolutely >>mission-critical. >> >>NOTE: a discussion of why my client deployed a hobbyist's first app >>in >>a mission-critical role is best left for a different day (although I >>suspect it's a matter of stepping over dollars to pick up dimes). >> >>-Christopher Hawkins- >>www.christopherhawkins.com >> >> >> >> >> Marty Connelly Victoria, B.C. Canada