[AccessD] Monitoring concurrent connactions to the BE?

Jim Dettman jimdettman at earthlink.net
Wed Jul 14 15:45:56 CDT 2004


Christopher,

<<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.>>

  As Charlotte pointed out, you can used the JET roster to get the list of
users, or if DAO, there are the functions built into MSLDBUSR.DLL lib, or
you can monitor the number of connections to the MDB through the server

<<MY TAKE:  The idea that 80 concurrent connections would slow or
outright freeze an Access app makes sense. >>

  That is pushing it for a JET based app, mostly for stability reasons, but
a well written app running on a solid network would not freeze.

<<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.>>

  If that's going on, then the MDB is corrupt.  You should create a new
backend.

<<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.>>

1. Make sure everyone is using JET SP 8.0
2. Set the FE to maintain an open connection to the backend for the life of
the app (ie. open a hidden form bound to a table at startup and close at
exit)
3. Turn off sub datasheets on all tables.
4. Turn off name autocorrect
5. If NT/Win 2K server for the BE, turn off opportunistic locking.
6. Make sure the FE's and BE are not being virus scanned.

  Those are the biggies.  See:

HOW TO: Keep a Jet 4.0 Database in Top Working Condition
http://support.microsoft.com/default.aspx?scid=kb;EN-US;300216

  for additional pointers.  Note however that using ADO is *slower* then DAO
on a JET MDB.  The article implies that your better off, but what it really
means is that your better to use the OLEDB provider instead of ODBC if using
ADO.

Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Christopher
Hawkins
Sent: Wednesday, July 14, 2004 3:32 PM
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