database size

[Paul]

I can't recall the recommended max size of a DB (records)
I know at some point the program bogs down or dies
altogether....?

 

[Albert D. Kallal]

There is not cut and dry answer here.


The only limit in ms-access is the file size. So, depending on how large
each record is, that will determine the outcome.

Assuming that each record is 200 characters long, then 2 gig/200 = 10
million
records.

Of course, if each record is only 100 characters long, then you get 20
million records.

I don't think ms-access would handle 10 million records well.

But you can certainly stuff in 1 million records.

So, limit is actually the file size, and not the number of records. However,
in actual practices, the amount of records you can work with will depend
much on the application you have written, and how well it is written. The
#1 factor in performance is how well the developer set up the application.

For example, a table of 100,000 records is very small *if* your application
is written o NOT have forms generally work with more then one record at a
time. If you have a poorly written application, then 10,000 records can be
too slow.

Of course, other factors like is the application going to be multi-user, and
is a network involved is again another whole ball of wax that effects all of
the above.

I mean, an access database with 200,000 records is not much, but if you now
throw in a network and have to drag records across the network, then a lot
caution needs to be taken in the designs, and how that data is retrieved.

 

[Guest]

-----Original Message-----
There is not cut and dry answer here.


The only limit in ms-access is the file size. So, depending on how large
each record is, that will determine the outcome.

Assuming that each record is 200 characters long, then 2 gig/200 = 10
million
records.

Of course, if each record is only 100 characters long, then you get 20
million records.

I don't think ms-access would handle 10 million records well.

But you can certainly stuff in 1 million records.

So, limit is actually the file size, and not the number of records. However,
in actual practices, the amount of records you can work with will depend
much on the application you have written, and how well it is written. The
#1 factor in performance is how well the developer set up the application.

For example, a table of 100,000 records is very small *if* your application
is written o NOT have forms generally work with more then one record at a
time. If you have a poorly written application, then 10,000 records can be
too slow.

Of course, other factors like is the application going to be multi-user, and
is a network involved is again another whole ball of wax that effects all of
the above.

I mean, an access database with 200,000 records is not much, but if you now
throw in a network and have to drag records across the network, then a lot
caution needs to be taken in the designs, and how that data is retrieved.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.
So microsoft has an imposed 2 gig file size limit. Is

there a work-around so the file size could be increased to
3 gig or 4 gig?

Thank you.
Murray Franz

 

[Joan Wild]

So microsoft has an imposed 2 gig file size limit. Is
there a work-around so the file size could be increased to
3 gig or 4 gig?

No. You can link to multiple backends, each with one table if you want.
Then each table could be up to 2 gig, however you can't enforce referential
integrity between tables in different backends.

If you need this capability, you should look to a server database instead.