Large database

[Matthew]

I have a web design book that says Access is not for production servers
because of its inefficiency, among things. Has this been fixed in Access
2002?



If not, at what size should I start looking for a different database
software?



Matthew

 

[Ben]

Access is good for a great many things, but large databases with many
concurrent users (as on the web) Access is less than ideal. I believe
the maximum size is 2 GB. You have heard that Access is inefficient
because Access is a file/server application rather than a client/server
application. This results in a lot of network traffic to resolve
queries. In the case of a web application, this may not be bad because
the Access DB could be on the same machine as the web server. Access
also is not very efficient with locking, which will cause problems with
a web application if there are any number of concurrent users. The time
to look at alternatives such as SQL server is now.

HTH,
Ben

 

[Sal Rosario]

Access is not and will not be a database system for heavy duty concurrency
or large databases. Microsoft sells SQL Server 2000 for this more advanced
needs, specially for web applications.

If I need to have more than 20 symultaneous users updating data on my
database, or if my database holds critical information that I must not lose,
or if my file size will grow to more than 10MB, I wouldn't use Access. I
would use SQL Server 2000 or the equivalent open source MySQL.

Sal Rosario
Database Consultant in NJ
www.salrosario.com

 

[MacDermott]

I once wrote a web project which had about 80 users, entering a few (under
100) items of data each, a maximum of once a month. (Some only entered
quarterly, or even anually.)
For this level of usage, an Access database worked fine.

If you're thinking of using an Access database behind a web page remember:
Access databases must be taken offline for compacting from time to time,
so your website will not be available "24-7".
While Access is "generally reliable", it is more subject to corruption
than more "industrial strength" databases such as SQL Server or Oracle, and
much more difficult to recover from such problems. Do not use Access for
any application which is "mission critical", or where it will cause major
problems to have to fall back to a recent backup.
Access can support only a very small number of truly simultaneous users.
If your application will be open for public use, you may well experience
"peak times" where large numbers of users are trying to use the database.
This can cause a crash - then see the point above...

HTH
- Turtle

 

[Matthew]

Is it a big deal to upgrade later?

Matthew

Access is not and will not be a database system for heavy duty concurrency
or large databases. Microsoft sells SQL Server 2000 for this more advanced
needs, specially for web applications.

If I need to have more than 20 symultaneous users updating data on my
database, or if my database holds critical information that I must not lose,
or if my file size will grow to more than 10MB, I wouldn't use Access. I
would use SQL Server 2000 or the equivalent open source MySQL.

Sal Rosario
Database Consultant in NJ
www.salrosario.com

 

[Sal Rosario]

You could migrate later, but you may need to change significant amount of
ASP/ASP.Net code depending on how complex your application is. If your data
is not mission-critical, you can start small, test the waters, make sure
that you maintain your Access database (backing up/compacting & repairing).
Then, upsize later.

Sal Rosario
Database Consultant in NJ
www.salrosario.com

 

[John Vinson]

Is it a big deal to upgrade later?

Not if you design the Access application with future upgrading in
mind: little things like never using spaces in fieldnames, bigger ones
like using unbound Forms and detached recordsets.

A BIG chore if you don't plan ahead, though!

 

[Matthew]

Do you have a list of the things I can do to plan ahead?

Matthew

 

[John Vinson]

Do you have a list of the things I can do to plan ahead?

I don't but Microsoft does:

http://support.microsoft.com/id=175619

Note that A200x comes with MSDE, which is essentially a throttled but
fully capable version of SQL/Server. You don't (yet!) need to spring
for the considerable expense of a full SQL installation; you can test
your upsize to MSDE, or build the app using MSDE instead of JET from
the beginning.