upgrading Access to a larger DB

[Ness]

Hi All,

I have created an Intranet application for a booking
system using Access and ASP. Access is only used to store
tables and I am using my ASP pages to manipulate the 2
main tables and place the data in a bookings table. What
is the most cost effective solution for upgrading my
database when it gets too big?

Or could I keep my 2 main tables in one MS Access database
and create a sperate database for bookings and when that
is too big start another database?

Roughly how many records can be kept in one MS Access
database?

Any suggestions would be gratly appreciated. Thank you

 

[Albert D. Kallal]

The question of how large can you go before outgrowing ms-access is kind of
like what is the Matrix question?

There is no cut an dry answer here.

I will say that tables that have say only 100,000 records are certainly what
one would call medium size. So, if your tables are 30 to 250,000 records,
then that size is well within ms-access, and will function very well at all.

When you start reaching the 1 million size, then that is getting large.

However, we often see some people having trouble with a file that has only
4,000 records. That is tiny. So, much of the issues of perforce is going to
be design, and how well the developer knows database designs, and has a good
deal of experience to know what works, and what does not.

It is much like some web developers that don't realize how much graphics
size will slow down the load of the page. Only with a bit of experience,
does one learn what works well, and what does not.

 

[M.L. Sco Scofield]

Albert gave you some good info on size.

If you are using a Jet database with an ASP, a more important issue is concurrency. How many people are going to be doing updating
at the same time? If it's a controlled web site, you shouldn't have a problem. If it's a public site with hundreds of people hitting
it, you'll have concurrency problems long before you get to Access's size limits.

I use Jet tables behind the ASPs on my web site with no problems. The only updating they do is an email sign up and a class
registration. If there is ever more than one person doing this at the same time on my site, I'd be very surprised.

One thing to keep in mind when calculating concurrency is how long an ASP keeps a connection to the database open. If I had a
hundred people on my site, I doubt that I'd even had two concurrent users. My ASPs only hold a connection open for fractions of a
second.

If you project more that 20 or 30 concurrent users, then you should start looking into SQL Server.

--

Sco

M.L. "Sco" Scofield, MCSD, MCP, MSS, Access MVP, A+
Useful Metric Conversion #16 of 19: 2 monograms = 1 diagram
Miscellaneous Access and VB "stuff" at www.ScoBiz.com