[AccessD] Monitoring concurrent connactions to the BE?

StaRKeY starkey at wanadoo.nl
Wed Jul 14 15:49:27 CDT 2004


Are you sure Dan? KB 810415 refers to A2K2 not A2K3....  

Thnx, 
Eric Starkenburg 

-----Oorspronkelijk bericht----- 
Van: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] Namens Dan Waters 
Verzonden: woensdag 14 juli 2004 22:36 
Aan: 'Access Developers discussion and problem solving' 
Onderwerp: RE: [AccessD] Monitoring concurrent connactions to the BE? 

Ouch!  What fun! 

A couple of thoughts: 

1)  You can look at KB 285822 to read how to programmatically determine how
many users are logged in.  I've used the information to limit the number of
users to a set quantity (part of a seat licensing scheme).  If you're set
for 20 users, user 21 logs in, gets a 'too many people' message, and
immediately gets kicked out!  Of course, you'll have to set expectations
about the 4:00 pm rush. ;-)  You could also set up a routine that runs every
few minutes and records the number of users into a table along with the
current time.  You can also record who gets automatically booted out and
when - this gives you objective information when people start complaining
and management wants to know the real info.

2)  Is your 2003 BE in 2003 Format?  If so, compacting is probably not
reducing the size of the BE very much.  Change the BE to 2000 format and it
will compact correctly.  You can read about this in KB 810415.

Best of Luck! 

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

-- 
_______________________________________________ 
AccessD mailing list 
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com 



  _____  

avast! Antivirus <http://www.avast.com> : Uitgaande bericht is niet besmet. 


Virus Gegevensbestand (VPS): 0429-0, 12-07-2004
Getest op: 14-7-2004 22:49:27
avast! auteursrecht (c) 2000-2004 ALWIL Software.






More information about the AccessD mailing list