how robust is access?

[Logan McKinley]

I am working on an information management program with a pretty hefty
database and was wondering if access would be robust enough to handle it.
The database will have around 40 tables with 80 relationships and no more
then a few thousand records. I am writing an ASP.Net frontend that will use
an odbc connection to the database and will have at most 10-15 users
concurrently. I have already created the database in access and was
wondering if it was worth my time to move it to mySQL, as this is my only
other option.
Thanks in advance,
~Logan

 

[Arvin Meyer]

I am working on an information management program with a pretty hefty
database and was wondering if access would be robust enough to handle it.
The database will have around 40 tables with 80 relationships and no more
then a few thousand records. I am writing an ASP.Net frontend that will use
an odbc connection to the database and will have at most 10-15 users
concurrently. I have already created the database in access and was
wondering if it was worth my time to move it to mySQL, as this is my only
other option.

Logan,

As databases go, a few thousand records is not a "hefty" database. I've
worked on spreadsheet data output with more records than that. A half a
million records in a well-designed database, might be considered "hefty" by
most Access standards, but I've worked with much bigger ones than that. I
generally stress test my Access databases with between 3/4 of a million and
1 million records. I wouldn't bother with moving the database unless you
begin to see problems that you can't figure out.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Logan McKinley]

so it would not matter that we would have maybe 5-10 people using the
database (via the ASP.Net frontend) at a time?

Thanks again,
~Logan

 

[Albert D. Kallal]

That sounds well within access limits.

I have a small sized application.

The access application has:

27,000 lines of source code,
160 forms,
60 tables (highly related, and I DO rely on cascade deletes and lots
referential integrity to make the application run).

The number of queries is 181,
The report count is quite low at 73.

This low report count of 73 is due that I use a good deal of code in the
reports, and they are HIGHLY re-usable, and this I never have the typical
bad access designs where you see 10 reports that are almost identical, but
only a MINOR change made to each one. Each report has MANY options, and this
keeps the report count low. You can see some report prompt screens to see
how I get options BEFORE I launch reports at:


The table sizes are very small, typically in the 30,000 to 40,000 record
range. Thus, once again we are talking very small record counts.

The front end application size with all of the above code, forms etc can be
zipped onto ONE floppy disk. In fact, it *just* now has slightly gotten
larger then a single floppy when zipped. (1.47 megs).

With 4, or 5 users in the application all day on a standard office LAN,
response time for form loads, and dealing with customers on the phone is
absolute instant. In other words, this application is used live to deal with
customers, and response time for form loads NEVER gets in the way of smooth
operation. Some forms do take almost 1 second to load, but those are forms
with about 2-5 combo boxes, and about 4 sub forms that handle the several
one to many relationships. I don't hide those sub-forms behind the tabs, and
probably could get the load times down to even lower then 1 second. However,
I only do as much work on a form to get the load times acceptable...after
that...it is not worth my time to make the application go faster then the
speed that users can use it!

So, if you only have 50, or 60 tables, and the table sizes are really small
like 30,000 or 40,000 records..then you should not experience any delays in
normal operation if your designs and code is well written....

You will note how I said the above is a small application. I used to think
the above was rather large, or at least medium sized. After spending some
time in these newsgroups......the above application is actually considered
to be quite small by today's standards now.

 

[Logan McKinley]

thanks, i thought my db was rather sizeable but now i have a comparison and
see it as it is, I swear it looks big and scary in the relationship view
.
Thanks again,
~Logan

 

[Arvin Meyer]

You shouldn't have a problem. ASP front-ends are a bit different because
they work through a single user (I_USR in IIS) Too many simulataneous "hits"
can corrupt the database.

I had a long report that took about 45 seconds to run so we build a que
database to hold the requests. Users in straight Access never caused a
problem, but the Intranet user would regularly corrupt the que database
because they'd grow impatient and click the thing 15 or 20 times. Get just a
few users doing that an the que would be 40 to 80 records in no time. The
database tables themselves were fine, but the queing DB would corrupt a few
times a week.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[david epsom dot com dot au]

Microsoft does not recommend use of Access/Jet with IIS at all.

There are some poorly described problems which make it difficult
to create a reliable web backend with an Access ODBC connection.

Also, you tend to run into problems with backup/update when
using an MDB database for an internet site.

Access/Office also includes a small version of SQL Server, so
if you have Access you also have a licence for that (it's on
the CD, but not installed by default).

But I haven't heard anything bad about mySQL either.

(david)

 

[Albert D. Kallal]

Do however remember, that your experience may slightly differ, as you taking
about using JET and a web server. That is somewhat of a different setup.

For a few users hitting the web site..it should be ok..but the interaction
between a web server and JET and users is somewhat different.

I would just test and monitor the situation for a awhile.

 

[rich]

Now I work a lot on ORACLE but hopefully I am no too biased!

Now you really need to look at the business case and order the features
in order of priority. Should you need solid robust data with almost no
data loss, whatever the reason (ie, complete server meltdown) then I
would look at SQL Server or ORACLE.

Both of the above have re-do logs, archive logs which means generally
100% recovery, something which access cannot do unless you are a super
coder (comments from access programmers most welcome!). Should you be
able to get away with last nights backup tapes then access may be ok.

Another thing is you cannot tune the DBMS much in access. In ORACLE you
can change almost anything, however ORACLE DBAs dont come cheap.

I think the key issue is data loss, if you cannot afford it, think about
upscaling.

Rich

 

[Albert D. Kallal]

Now I work a lot on ORACLE but hopefully I am no too biased!

Now you really need to look at the business case and order the features
in order of priority. Should you need solid robust data with almost no
data loss, whatever the reason (ie, complete server meltdown) then I
would look at SQL Server or ORACLE.

If you have hard disk crash..how can you playback...use the logs? In the
case
of Oracle, or ms-access, you will have to go to the previous days backup.

Both of the above have re-do logs, archive logs which means generally
100% recovery

How do you get at the logs when the disk drive has crashed? Please explain?

Unless you use re-dundent systems, and have some type of hot
backup/replication, if the server has a meltdown ...you don't have any re-do
logs. So, I am at a real loss here as to how you have 100% recovery from a
server meltdown (ie: a hard disk crash).

 

[david epsom dot com dot au]

How do you get at the logs when the disk drive has crashed? Please
explain?

You log to a separate disk. This is standard.

However, it is the responsibility of your DBA to set up the system to
ensure that the log disk is not full.

Yes, if you want a robust system, you need to have appropriate hardware
and staff. But given appropriate hardware and staff, it is still often
appropriate to use standard software built for that task, rather than
trying to build your own system out of pieces (like Access) not built
for that task.

(david)

 

[Albert D. Kallal]

Without question, with the proper setup, a good server system is the way to
go.

However, unless that database is logging (or replicating) to another disk,
or computer you don't get that wonderful recovery that the poster speaks
of.

 

[Arvin Meyer]

Microsoft does not recommend use of Access/Jet with IIS at all.

There are some poorly described problems which make it difficult
to create a reliable web backend with an Access ODBC connection.

Also, you tend to run into problems with backup/update when
using an MDB database for an internet site.

Access/Office also includes a small version of SQL Server, so
if you have Access you also have a licence for that (it's on
the CD, but not installed by default).

But I haven't heard anything bad about mySQL either.

(david)

The small version of SQL-Server (MSDE) is throttled back with more than 5
concurrent users and may not be licensed for use on the Internet. SQL-Server
is perfect, but Internet licensing costs US$5000 for a single processor
version.

Access has been working fine on 2 websites I've done over the past 4 years,
one an intranet and the other an Internet. Neither site gets heavy usage.
The intranet gets at most a few dozen hits a day and the Internet site gets
no more than a thousand evenly spread throughout about a 14 to 16 hour
period. Neither database has corrupted in the 3 1/2 year period and they
average about 50 to 60 MB of data.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[david epsom dot com dot au]

Without a competent DBA, SQL Server is a worse solution than
Access in many respects.

(david)

 

[david epsom dot com dot au]

Access has been working fine on 2 websites I've done over the past 4

one an intranet and the other an Internet. Neither site gets heavy

I think I would recommend changing the Jet settings
on the server so that Jet defaults to only one thread.

MSDE is throttled to only run 5 simultaneous queries. They
queue after that. On a single processor server, with no
queries that run slow by design, this should make very little
difference.

It is not clear to me what the licensing situation is for
MSDE. What reference do you have for the Internet restriction?

(david)