[AccessD] To a man with a hammer

Thu Feb 16 11:24:38 CST 2023

in 2013 I was hired by IBM as a consultant to work with an existing team of
(mostly) Access developers / consultants to examine all of the access apps
that IBM found on user machines when it bought a mortgage processing
company called Seterus.  They had found literally a couple of hundred
mostly tiny access apps developed by non developers just trying to do their
jobs.  Of course IBM had no clue what any of them did, how they worked or
whether they were even needed.

So they assembled a team of about 10 developers with heavy duty chops in
Access to help them wade through the mess.

I ended up being assigned to one app which had to be kept.  It did some
thing which I didn't understand, math stuff on loans for reporting
purposes.  The app pulled data out of SQL Server and through multi-layered
queries came up with a result set.  The top level query had a dozen second
level queries which had third level queries...

You get the picture.  Each query used had a link to a matching table or
view back in SQL Server, but each subquery would pull its data, and then
Access would assemble all of the joins between the various
queries/sub/subsub etc and (eventually) spit out a result.

The query (and thus the report) took literally hours to complete.

I ended up taking each subquery and turning it into TSQL, and had an entire
tsql module which built back up the thing that used to be done directly
inside of Access.  It ended up taking about 3 minutes to run.  Of course
SQL Server was doing all of the lifting by then.

That kept me busy for months, and was a lot of fun.  Although it was at
times a somewhat daunting task.

On Thu, Feb 16, 2023 at 11:56 AM Daniel Waters <df.waters at outlook.com>
wrote:

> YES!  This is Access' Achilles heel.
>
> People get started with Access with a good vision, but there's nothing in
> Access to tell them that they need more training.  So when the App get
> bigger there are often problems.  Then IT gets tasked with fixing a 'small'
> job which should only take a few hours but takes two weeks.  Over and over
> again.  This is why IT hates Access, or just won't support it.
>
> I got lucky when I started.  I found a book titled, 'Database Design for
> Mere Mortals' in the late 90's.  I devoured that book.  But at that time it
> got deeply into 'normal forms' which I'd guess most of us now just use some
> common sense and practicality when designing tables.
>
> Dan
>
> -----Original Message-----
> From: AccessD <accessd-bounces+df.waters=outlook.com at databaseadvisors.com>
> On Behalf Of John Colby
> Sent: 16 February, 2023 10:36
> To: Access Developers discussion and problem solving <
> accessd at databaseadvisors.com>
> Subject: Re: [AccessD] To a man with a hammer
>
> Understood.  And so when I simply make a statement "you  might want to use
> a class..." it is not my intention to put anyone down.  On the other hand
> to be told "when all you have is a hammer..."
>
> The sad reality is that Microsoft built Access to allow the beginner to
> get the objects on a form working right away, with little helpers that
> install the hooks etc.  Cool stuff but... The sad part is that Microsoft
> didn't ever really give us instructions on how to move beyond that.  I
> would guess that 98% of VBA developers have never used a class.
>
> And so, whenever a developer discusses inserting code into a code behind
> form I am going to suggest looking beyond that.  And I am forced to put up
> with the "man with a hammer" crap when I do so.
>
> On Thu, Feb 16, 2023 at 11:11 AM Charlotte Foust <
> charlotte.foust at gmail.com>
> wrote:
>
> > John,
> > I understand what you are saying and took much the same approach when
> > I was still building Access apps, it feels unkind to put down
> > developers who are still learning or who are working under constraints
> > that don't allow them to use our approach.
> >
> > Charlotte Foust
> > (916) 206-4336
> >
> >
> > On Thu, Feb 16, 2023 at 5:22 AM John Colby <jwcolby at gmail.com> wrote:
> >
> > > In my opinion, the user interface is an absolutely critical part of
> > > any app.  Too often the programmer treats the UI as an afterthought,
> > > which
> > ends
> > > up causing the entire application to be a disjointed mess.  I have
> > designed
> > > dozens of applications for dozens of companies over the 25 years I
> > > have worked in Access and over that time I figured out UI things
> > > which should just always be the same, everywhere, in every form.
> > >
> > > Notice that in Windows, every single application has a set of three
> > > controls in the upper right corner, a 'close', 'restore down', and a
> > > 'minimize' button.  Always there.  Not sometimes there and sometimes
> > > somewhere else, and sometimes not present.  In fact if we as
> > > developers want that to not appear in our forms we have to jump
> > > through hoops to
> > make
> > > it not appear.
> > >
> > > So whenever I see a developer describing shoving UI kinds of things
> > > into
> > a
> > > form class, my mind cringes.  As soon as I place anything into a
> > > form
> > class
> > > that has any chance of being used twice I have started a UI
> > > disjointed mess.  I have taken over other developer's projects, and
> > > understanding
> > all
> > > the crap in the typical form class is one of the major undertakings.
> > >
> > > As for a class being a hammer... in my view even saying such a thing
> > speaks
> > > volumes about the person saying it.  They either don't understand
> > > classes or they have no imagination.  And yes, I get tired of being
> > > told I have
> > no
> > > imagination when in fact it is the person saying such things which
> > > has no imagination.
> > >
> > > I have a class which wraps every single data aware UI object in
> > > Access, think textbox, combo, radio button etc.  I have a pair of
> > > classes which collect errors as they occur.  I have a class which
> > > logs anything.  The error handler class uses the log class to log
> > > errors collected.  I have
> > an
> > > FTP class, not as useful as it once was.  A class to do MD5 hashes.
> > > A
> > pair
> > > of classes to parse and deal with OpenArgs for when a form opens.  A
> > > pair of classes which read Sysvars out of my SysVar table and caches
> > > them into memory so the app doesn't have to hit a table every time.
> > > A timer class
> > of
> > > course.  A zip class, with a matching unzip class.  And many more
> > > classes as well.  All, BTW, placed out in a framework library which
> > > can be referenced from any app I desire.
> > >
> > > What about those many varied purposes describes using a hammer to you?
> > > Well maybe, if you have no imagination I suppose.
> > > --
> > > John W. Colby
> > > Colby Consulting
> > > --
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > > https://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> John W. Colby
> Colby Consulting
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

-- 
John W. Colby
Colby Consulting