I see that some have asked how large a database can be. I'm building a
database whose working model currently has 220,000 records in the main
table. I built a crosstab query that transfers that data, making it
13,000 records. The database really works from that smaller
table---the queries, reports, and forms. The database, after
compacting is 50 megabytes.

Most of the database could work even after deleting the main table but
I want it to hang around for some look up forms.

So, I here, when all is said and done, that my main table will
potentially be 1,500,000 records at year end. (Data will be deleted
each year and started fresh). The crosstab will probably make a table
of about 88,000 to 200,000 records.

So I get this, adding in some misc records from other tables:

Current: 220,000 to 13,000 crosstab (+ 10,000 misc) = 50 megs.
Future: 1,500,000 to 200,000 crosstab (+ 100,000 misc) = X

Solving for x, I get about 353 megs.

So it seems that I am okay. Right?

Thanks,

Matt

Can't answer the size issue, but I do recall that there is a point where
JET will start to have issues as a DB grows. You will also have
performance issues as the DB grows. My flat out suggestion is to go
SQLServer since it is designed for the big stuff.

David H

(E-Mail Removed) wrote:
> I see that some have asked how large a database can be. I'm building a
> database whose working model currently has 220,000 records in the main
> table. I built a crosstab query that transfers that data, making it
> 13,000 records. The database really works from that smaller
> table---the queries, reports, and forms. The database, after
> compacting is 50 megabytes.
>
> Most of the database could work even after deleting the main table but
> I want it to hang around for some look up forms.
>
> So, I here, when all is said and done, that my main table will
> potentially be 1,500,000 records at year end. (Data will be deleted
> each year and started fresh). The crosstab will probably make a table
> of about 88,000 to 200,000 records.
>
> So I get this, adding in some misc records from other tables:
>
> Current: 220,000 to 13,000 crosstab (+ 10,000 misc) = 50 megs.
> Future: 1,500,000 to 200,000 crosstab (+ 100,000 misc) = X
>
> Solving for x, I get about 353 megs.
>
> So it seems that I am okay. Right?
>
> Thanks,
>
> Matt
>

(E-Mail Removed) wrote:

>I see that some have asked how large a database can be. I'm building a
>database whose working model currently has 220,000 records in the main
>table. I built a crosstab query that transfers that data, making it
>13,000 records. The database really works from that smaller
>table---the queries, reports, and forms. The database, after
>compacting is 50 megabytes.
>
>Most of the database could work even after deleting the main table but
>I want it to hang around for some look up forms.
>
>So, I here, when all is said and done, that my main table will
>potentially be 1,500,000 records at year end. (Data will be deleted
>each year and started fresh). The crosstab will probably make a table
>of about 88,000 to 200,000 records.
>
>So I get this, adding in some misc records from other tables:
>
>Current: 220,000 to 13,000 crosstab (+ 10,000 misc) = 50 megs.
>Future: 1,500,000 to 200,000 crosstab (+ 100,000 misc) = X
>
>Solving for x, I get about 353 megs.
>
>So it seems that I am okay. Right?


Size wise, I think you're ok. The upper limit for an MDB
file is 2GB, so it looks like you have a ways to go.

Performance wise, who knows? I have seen reports of much
larger MDBs that ran great and much smaller ones that were
unusably slow. Performance is mostly(?) a function of how
well you design it, how much attention you pay to your
table's indexes, how well you construct your queries and
even form and report structure.

--
Marsh
MVP [MS Access]