[AccessD] Is it over for desktops part 2

[John Colby]

Arthur Fuller
6:47 PM (2 hours ago)

Hi JWC! Have you tested this with N users at once? where N > 10, say. N >
10



No, in fact it was mostly a couple of users, myself and my friend Mike
Waters.  We used it to check prisoners out of the local prison to take to
AA meetings.  I had to fill out paperwork which was a PITA, dozens of
fields, mostly repetitive.  So I built a db to hold the prisoner info and a
little report to fill out, then emailed the report to the prison. It was
decidedly low tech and low impact.

It was dog slow, though I never determined where the slowness came from.
It went over the internet to a server in my home office. The server was
decidedly upscale for the day (2010-12), 12 physical cores, 90 gb ram, 20tb
raid 6 etc. all built for my client Stan for whom I did large scale SQL
Server stuff.  So it wasn't the server itself.  It came into my home office
over a cable internet, not particularly speedy but not slow for the day,
probably about 40-60 mbit / sec down.

Access itself did all the connection stuff.  However again it was pretty
simple data as well, probably a handful of tables / connections.  AFAICR it
was ADO doing the lifting, and it pretty much opened a connection, moved
the data, then closed the connection for every single transaction.  It was
all a long time ago but I remember being impressed that I could actually
hit my server from my laptop reliably.  Remember that DAO corrupts an
Access BE over the internet.

I actually used the same technology inside of IBM in 2013.  That job was
migrating incredibly complex financial queries which I did not write, nor
even understand.  Some accountant in a loan company had developed the db to
do his job, and when IBM bought Seterus (the loan company) they needed to
get a handle on such apps scattered around the desktops in said company.  I
was hired into a team of Access developers to figure out which apps had to
remain and which needed to be retired or replaced.  So... I was handed this
app which nobody understood and which ran incredibly slowly because, while
it pulled the data out of SQL Server over a wan, it had up to 12 layers
deep queries.  The data was pulled into Access and the joins done there.

I rewrote the queries to run in tsql and just return the finished dataset.
It went from an hour to run a report to 30 seconds or so.  Again however,
the tech was links to SQL Server which used ADO.

All of which doesn't truly address your question, which I believe really
wants to know about locks and simultaneous updates and edits.  Whenever you
do such you pretty much have to handle that yourself.  Access will notify
you that a record that you pulled in and are looking at or updating has
been updated by another user.  You as the designer have to decide a
strategy for what to do with that notification.  Typically notify the user
and ask them, or throw away changes and force a redo.  Or place a lock and
not allow updates while you have a record open.  Notice there is no 'one
size fits all' solution.

One of the reasons I liked bound forms is that Access/dao handled all that
pretty darned well.  Those brave souls who used unbound have to handle all
that themselves, although it isn't clear that most of us have the expertise
to do that particularly well.  Microsoft OTOH could (and did) spend lots of
money to hire that expertise for the design of DAO and bound forms.

In the end, I didn't handle that at all for my prisoner db as it was never
truly multi-user in the normal sense.  The IBM / Seterus db was read only
data and so also didn't need handling.
-- 
John W. Colby
Colby Consulting