How robust is Access 2002?

I'm fairly new to Access (I've worked with Oracle and MySQL in the
past). I was asked to build an application for a small office and told
that I had to use Access 2002. I was VERY uncomfortable with this, but
went ahead anyway. So two weeks ago we rolled out the new system and
I'm still wondering how robust Access is......I know it's not in the
same league as Oracle, but at the same time this isn't a real big
application. But I can't help but worry if it's going to hold up in our
environment over time. So far we haven't loaded the database with many
records -- but that's coming soon and I am worried.

We will have 5-10 concurrent users at any given time, but much of their
activity will simply be reading records. But there will be moderate
updating of records throughout the day. The database is split with
forms on the client machines (running XP Pro) and the backend on a
server (but it's all MS Access 2002, I was told I couldn't use MS Sql
Server). The database is 25 tables with roughly 400 fields spread
across these 25 tables. We will have roughly 10,000 records in the
database at some point.

Does this sound like it's something Access 2002 can handle ok? Thanks
in advance for your help! Reassure me so I can sleep at night :-)

re: How robust is Access 2002?

In a word, "Yes, you can."

For a few more words, with just decent, not superb design and
implementation, you have a lot, a LOT, of room to grow before you'll be
pushing any limits.

You've done the one thing that I consider most important -- split your front
and back ends. Just for reassurance, check the information and links at MVP
Tony Toews' site, http://www.granite.ab.ca/accsmstr.htm. There may be a few
odds or ends you still need to take care of, but you are on the right track.

It's amazing to me that people who are used to working in server
environments take such a condescending view of what they see as "lesser
software". Not every firm needs an enterprise server database like Oracle.
In fact, you'd be doing a small firm a great disservice if you put them into
Oracle, or SQL Server, or DB2 when they did not need it.

Now, they might have a requirement for a server for reliability and
recoverability -- I've worked on a project for a user audiences only about
twice as large as yours that used an Access client to SQL Server. They
needed a server because of the requirement for data integrity and rapid
recoverability; the data in that system was used for options trading and
that is a fast-paced business.

I trust you are aware that Access does not have its own logging, backup, and
recovery and that you have implemented what is necessary to recover --
something that is much easier on server databases because of the builtin
features. I trust you are aware that because of its file-server nature,
Access is more sensitive to power outages, dropped network connections, and
users who power off instead of shutting down. That's when you'll need to be
able to recover.

If you have made such provisions, there's a good possibilty that you won't
need to migrate to a server database until you exceed 60 or 70 concurrent
users or massive increases in your data. And, when you do, you may be able
to continue to use your same front end with only minor changes.

So, breathe easier during the day and sleep easier at night.

Larry Linson
Microsoft Access MVP




"David Kistner" <spamcatcher1@verizon.net> wrote in message
news:vsp2d.9776$xH1.7828@trnddc03...[color=blue]
> I'm fairly new to Access (I've worked with Oracle and MySQL in the
> past). I was asked to build an application for a small office and told
> that I had to use Access 2002. I was VERY uncomfortable with this, but
> went ahead anyway. So two weeks ago we rolled out the new system and
> I'm still wondering how robust Access is......I know it's not in the
> same league as Oracle, but at the same time this isn't a real big
> application. But I can't help but worry if it's going to hold up in our
> environment over time. So far we haven't loaded the database with many
> records -- but that's coming soon and I am worried.
>
> We will have 5-10 concurrent users at any given time, but much of their
> activity will simply be reading records. But there will be moderate
> updating of records throughout the day. The database is split with
> forms on the client machines (running XP Pro) and the backend on a
> server (but it's all MS Access 2002, I was told I couldn't use MS Sql
> Server). The database is 25 tables with roughly 400 fields spread
> across these 25 tables. We will have roughly 10,000 records in the
> database at some point.
>
> Does this sound like it's something Access 2002 can handle ok? Thanks
> in advance for your help! Reassure me so I can sleep at night :-)[/color]

re: How robust is Access 2002?

David Kistner wrote:
[color=blue]
> We will have 5-10 concurrent users at any given time, but much of their
> activity will simply be reading records. But there will be moderate
> updating of records throughout the day. The database is split with
> forms on the client machines (running XP Pro) and the backend on a
> server (but it's all MS Access 2002, I was told I couldn't use MS Sql
> Server). The database is 25 tables with roughly 400 fields spread
> across these 25 tables. We will have roughly 10,000 records in the
> database at some point.[/color]

That's a fairly small database. The results in displaying and reporting
should be instantaneous or near instantaneous. If you set up tables
correctly and use indexes well, you should end up being impressed with
the results.
[color=blue]
> Does this sound like it's something Access 2002 can handle ok?[/color]

Access would never have been a popular desktop database nor would there
be a group of knowledgeable people like this usenet group to help people
with problems if it couldn't handle something like your project easily.

Thanks[color=blue]
> in advance for your help! Reassure me so I can sleep at night :-)[/color]

Programmers work while others sleep.

re: How robust is Access 2002?

I've run an Access 2000 database with 200,000 records with no
problems.

I didn't know that Access 2002 could handle 60-70 users - that's
useful to know. My information (for Access 2000) was that over 10
heavy concurrent users and things might slow down a bit. Over 20
heavy users and you'd start to think about unbound forms (but this is
quite a headache). So with 5-10 users, you should be absolutely fine
in Access 2002.

Here are a few more thoughts:

Have you / do you need to implement user-level security (eg to stop
users modifying database design, unauthorised access, etc)? If you
do, this is complex to get right and needs to be properly documented,
but it is very secure. In a small environment, it might be easier to
keep good backups on- and off-site of original database versions and
restore them if users go haywire. Also, physical and password access
to computers should be restricted.

If users run code that creates temporary objects (eg tables or
queries), are they created locally in their own frontend database to
avoid potential conflicts with other users?

Have you indexed fields used in relationships, criteria and sorting?


Issues for the backup administrator:-

Does the office make a tape backup of the backend every night?

Are they keeping a minimum of three tapes used in rotation (or better
a new tape for every working day in a week)?

Are they permanently archiving data say every week, month, year to CD
on- and off-site?

Are the backups kept in a safe (or safe place)? Fire-proof safes
don't necessarily protect backup media from smoke damage.

Have they got backup software that will create a backup of the backend
even if a user has left a frontend open over night?

Is the backup drive maintained in good order (eg heads cleaned)?

Do they keep a paper trail (eg data-input sheets) at least until the
next tape backup is made? Could they re-enter data lost since the
last backup if the system crashed before the next backup?

Do they have a backup administrator who's responsible for backing up
and who can restore backups in an emergency?

Do they have a deputy backup administrator for when the administrator
is on holiday?

Does the backup administrator keep securely a paper record as to when
backups are made and with which tapes?

If they find this all too much like hard work, too expensive or too
complicated, you might remind them that most organisations that have
catastrophic data loss, don't have the resources to recover if they
don't have good backups.


The answers to the above questions are only for you to think about.
At this stage, I don't think your main concern should be with Access
as a product, but much more with the way the office works. The office
needs to have, enforce and regularly review their procedures at
management level. They can then be confident that they'll be able to
overcome any potential data-loss problems.

Regards.
Geoff

re: How robust is Access 2002?

"Geoff" wrote
[color=blue]
> I didn't know that Access 2002
> could handle 60-70 users - that's
> useful to know.[/color]

There are a number of factors that affect the number of users and
performance in a multiuser environment: hardware, software, and network
environments and the requirements, design, and implementation of the
application. If all those factors are "near perfect", we hear reliable
reports of Access-Jet supporting 100+ concurrent users.

That said, I would never agree to try to implement a project with only
Access and Jet that was _planned_ to have 100, or even 70 concurrent users.
One consistent thing I have observed in a good many years of application
development is that the initial estimate of the user audience _always_, at
least, doubles. I worked on one Access client to Informix server application
that in 1995 the client administrator said, "We have 35 users now, and we
won't ever have more than 50." The last time I worked on that application,
mid-2000, they had between 175 - 200 users.

All those factors play a part, but the ones that I have seen cause most
troubles are "requirements, design, and implementation of the application".
That led me to say "If you do an Access database application poorly
_enough_, it won't even support ONE user." <G> I think you could say the
same about any development tool (you certainly could say that about any
development tool I've used, and over the years, I've used a good many --
many that are merely footnotes to computer history, now.)

In my opinion, when you begin to have to resort to unbound forms and similar
tricks, you are just buying time before you need to move to an Access client
application and a true server database. And, often, the cost is higher to
buy time that way than it would be to just go ahead and do the conversion.

Yet, an application with appropriate requirements (not all users doing
"heavy updating", e.g.), well-designed with the multiuser environment in
mind, and well-implemented can grow to the 60-70 user range, over time.

The best collection of information, tips, hints, tricks, and links on
multiuser performance and avoiding corruption is at MVP Tony Toews' site,
http://www.granite.ab.ca/accsmstr.htm. In my not-so-humble opinion, the
Access community owes Tony a big vote of thanks for that site.

Larry Linson
Microsoft Access MVP

re: How robust is Access 2002?

Larry,
It's interesting to hear your experiences of the real world and to
note your pointers on performance issues. It seems the future remains
uncertain.
Best regards,
Geoff

re: How robust is Access 2002?

"Geoff" <geoff@nospam.com> wrote:
[color=blue]
>I didn't know that Access 2002 could handle 60-70 users - that's
>useful to know. My information (for Access 2000) was that over 10
>heavy concurrent users and things might slow down a bit. Over 20
>heavy users and you'd start to think about unbound forms (but this is
>quite a headache).[/color]

I'm not at all sure that unbound forms gives any performace or corruption advantage
over bound forms. And as you point out it's quite a headache. Also you lose the
record locking.
[color=blue]
>If users run code that creates temporary objects (eg tables or
>queries), are they created locally in their own frontend database to
>avoid potential conflicts with other users?[/color]

Each user should have their own frontend. But if they are creating temporary
objects such as tables then that in turn should use thier own temporary MDB. Or is
that what you meant to say?

BTW I don't use temporary queries. Intead I prefer to create the base queries as
SELECT queries and then in VBA code reference the SELECT query with the delete/update
logic and WHEREBY clause.
[color=blue]
>Are the backups kept in a safe (or safe place)? Fire-proof safes
>don't necessarily protect backup media from smoke damage.[/color]

As I'm sure you're aware but I'll point it out for other folks there is no such thing
as fire-proof. There are fire ratings. One hour, two hour and such. Furthermore
those are usually based on paper. Plastic melts at a much lower temperature.
[color=blue]
>Have they got backup software that will create a backup of the backend
>even if a user has left a frontend open over night?[/color]

I have some logic to kick the users out after an hour of inactivity. Sometimes I
need to make table, relationship or index changes to the backend after hours.
[color=blue]
>Is the backup drive maintained in good order (eg heads cleaned)?[/color]

Is a test restore done once every month or two. And using a different set of tapes
in the backup cycle each time?

True story. I walked into a new client and was preparing to do the work I was
immediately hired for. They didn't have any IT admins and were depending on a big
name, expensive consulting firm. I asked if they minded if I reviewed the backup
tape just to ensure it was working properly. I review the backup logs and discover
that the only good backup was on Thursdays. Turns out the backup software wanted to
initialize the tapes and write it's own information so it could keep track of things.

Monday - Wednesday, Friday and month end tapes hadn't had this step done on it.
Furthermore they had no idea where to look to ensure the backup was happening. They
just inserted the tape at the end of the day, clicked on the backup button and went
home. No one noticed that it took only 30 seconds to run 4 out of 5 days.
[color=blue]
>Do they keep a paper trail (eg data-input sheets) at least until the
>next tape backup is made? Could they re-enter data lost since the
>last backup if the system crashed before the next backup?[/color]

And if they can't re-enter data lost since the last backup they should be using SQL
Server or other database engine.

Excellent posting.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

re: How robust is Access 2002?

Tony,

Thanks for fleshing out.
[color=blue][color=green]
> >If users run code that creates temporary objects (eg tables or
> >queries), are they created locally in their own frontend database[/color][/color]
to[color=blue][color=green]
> >avoid potential conflicts with other users?[/color]
>
> Each user should have their own frontend. But if they are creating[/color]
temporary[color=blue]
> objects such as tables then that in turn should use thier own[/color]
temporary MDB. Or is[color=blue]
> that what you meant to say?[/color]

I was cautioning against the (perhaps bizarre) possibility that a
developer might write code for users to run that would create
temporary objects in the backend (thereby raising the possibility of
conflict).
[color=blue]
> BTW I don't use temporary queries. Intead I prefer to create the[/color]
base queries as[color=blue]
> SELECT queries and then in VBA code reference the SELECT query with[/color]
the delete/update[color=blue]
> logic and WHEREBY clause.[/color]

I've found it expedient to use VBA to create temporary queries to
export data when I've given the user the option to select which fields
to export. Also I've populated a listbox on a form using a temporary
query based on two other temporary queries to collate the data (not
wanting a pile of queries in the database window that the user can
interfere with).
[color=blue]
> I have some logic to kick the users out after an hour of inactivity.[/color]

That could greatly simplify the original poster's backup
possibilities. Very nice to know.
[color=blue]
> Is a test restore done once every month or two. And using a[/color]
different set of tapes[color=blue]
> in the backup cycle each time?[/color]

Yes, good point - the backups must be regularly tested for viability.

My true story: Server hard disc failed. Backup no good! Send hard
disc to Holland. Give them £5000 to recover data. Put system back
together. New disc failed!! Backup still no good!!!! (No one
thought to check the backup system.) Cycle restarts. It was like a
nightmarish Do-Until-They-Learn Loop. They dumped the computer, data
and applications.

Best regards
Geoff

re: How robust is Access 2002?

"Geoff" wrote
[color=blue]
> Yes, good point - the backups must be
> regularly tested for viability.[/color]
[color=blue]
> My true story: Server hard disc failed.
> Backup no good! Send hard disc to
> Holland. Give them £5000 to recover
> data. Put system back together. New
> disc failed!! Backup still no good!!!!
> (No one thought to check the backup
> system.) Cycle restarts. It was like a
> nightmarish Do-Until-They-Learn Loop.
> They dumped the computer, data
> and applications.[/color]

I've been an Access consultant since 1993. In that time, I've been called in
to assist with recovery from tape just twice; in both cases, the tape
backups had been run regularly and without apparent error. In both cases,
the tapes could not be restored, nor could the "father" tapes nor the
"grandfather" tapes.

That certainly means that you need to test whether your backup and restore
scheme actually works. It is not sufficient that they backup software
_appears_ to work just fine. When the crucial time comes, hindsight will
likely show that the cost of a separate system for such testing on a regular
basis _was_, indeed, a justifiable expense. Before then, it may be difficult
to sneak that kind of thing past the bean counters.

Fortunately, in both those cases, there were alternative sources for the
lost business data, so it was only inconvenient and costly, but not critical
to the survival of the business.

Larry Linson
Microsoft Access MVP