Christopher Hawkins
clh at christopherhawkins.com
Wed Jul 14 15:52:36 CDT 2004
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 > > > > >-- >_______________________________________________ >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 >