Database - summary

[Richard Pyle]

> MS Access is the most popular database software (be careful,
> Access should not be used for larger databases. For example,
> Willoughby Ass.
> http://www.willo.com/cgi/content.cgi?main.html,snap.html,intro_lef
> t,snap.html,intro_right recommend only 60.000 objects for their
> Access-based database SNAP!)

Access 2000 can handle files up to 2GB in size. I've been able to
successfully develop databases entirely within MS Access for up to about a
quarter-million taxon "assertion" records -- linking 66K taxon names to 20K
Reference citaions, alongside 40K collection objects and 10K records of
people and organizations (all-told involving about 60 linked tables) -- all
within a single file less than 200MB. It operates amazingly well on a
single-processor, 366Mhz Pentium laptop. When you consider that nearly half
the file size is made up by about 50 embedded hi-res images (which would
best not be embedded), and even then we're only talking 10% of Access'
capacity, I think it's safe to say that file size is not the main limiting
factor.  Databases with >>60K records are routinly handled with ease by
Access, provided the system was designed and coded well.

The real limitation that I've encountered in Access is with respect to the
number of simultaneous concurrent users.  Who you should believe for
"actual" number of simultaneous concurrent users that Access can support
depends on a lot of factors, but we've generally had success with up to
about 5 or 6 -- but mini-crashes (e.g., individual record locks crapping
out) crop up from time to time, even with a half-dozen users.  For this
reason, we are moving in the direction of SQLServer with Access front-ends
to handle most of our in-house DB development needs. Incidentally, we are
also evaluating BioLink at the moment for our Botany collection.  So far,
we're very encouraged.  It seems to have the right combination of
well-developed schema, good user interface tools, and direct access to data
due to its SQL Server platform.

Una Smith wrote:

> Two more:  PANDORA (don't recall if it handles loan paperwork),
> available on ibiblio.org, and BIOTA, available from Sinauer
> Associates.  PANDORA is freeware.  BIOTA is inexpensive, has a
> very nice manual and a very impressive list of features.

Also, don't forget Vernon Systems COLLECTION
(http://www.vernonsystems.com/). There's also MANDALA
(http://pherocera.inhs.uiuc.edu/index.htm), but I don't know if its being
distributed.

Aloha,
Rich

P.S. Just read this:

> As for the assertion that you should not use Access for large databases,
> this is pure propaganda that was once true but is now outdated and
> unfortunately maintained by Oracle and its affectionados. I have one
> database with 180,000 records in it that runs fine in Access2000.
> The limit
> is around 2 gigabytes for the size of the mdb file. That would be 3.6
> million records in my herbarium database. Then you can export to SQL
> automatically - unlike a lot of MS software, it actually works, I've tried
> it.
>
> Stinger

It's not just Oracle affectionados -- I've heard the same erroneous
propaganda coming out of FoxPro diehards as well.  The Oracle part I can
understand, given Larry Ellison's feelings about Bill Gates....but FoxPro is
now owned by MS, so I'm not sure what fuels that fire.  This is not to say
that certain aspects of Oracle and/or FoxPro aren't superior to Jet 4.0 or
SQLServer....but when the anti-Access crowd resorts to false information to
make their case, it's a bit disturbing.



Richard L. Pyle
Ichthyology, Bishop Museum
1525 Bernice St., Honolulu, HI 96817
Ph: (808)848-4115, Fax: (808)847-8252
email: deepreef at bishopmuseum.org
http://www.bishopmuseum.org/bishop/HBS/pylerichard.html
"The views expressed are not necessarily those of Bishop Museum."