[AccessD] Monitoring concurrent connactions to the BE?

Charlotte Foust cfoust at infostatsystems.com
Wed Jul 14 15:12:43 CDT 2004


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



More information about the AccessD mailing list