How large can a table actually be?

[MelissaBerrett]

I have a table that is going to have potentially 5000 records added to it each month. Is this to large?

 

[KenReay]

Hi

There is not a limit on the number of rows in a table, but there is a limit on total database size, This limit depends on the version A97 is is 1gb, A2K and AXp I believe the max is 2gb.

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[nveau]

I'm not exactly sure how big an Access db can be, but unless you are going to archive this thing regularly it would probably make sense to at a minimum bump this thing up to the MSDE that is included with Access 2000 and 2002. I once had a table with nearly a million records and Access could handle it, but it was unbearably slow (even on a pretty beefy machine). I purchased sql server (and a nice big machine to run it) because the thing was going to continue to grow at a pace of 20,000 records a month, and now my reports and such run wonderfully well. I tend to like to use Crystal for my report writing anyway--so I can avoid Access altogether for some of my projects.

 

[MelissaBerrett]

Thanks for the speedy reply. The reason I ask is I am importing DOS files from our crappy Accounts Payable software into MSAccess. In order to export them correctly I have to keep the records in one table. I tacked on an autonumber for safe keeping, but other than that a lot of the fields hold duplicates. Is this okay, or are big problems ahead?
Thanks!!

 

[MichaelRed]

Many accounting systems are set up to run on a annual basis, and these are usually 'closed out' at the end of the year, and a 'new' year started with the ending balances of the previous year being carried forward. If this is the case, you (probably) should follow that practice and would have no issue with the number of records or database size.

If you are doing something else, ther may -at some point far away- be an issue. If the app adds 5K records permonth and you can handle 1M records, you have ~~ 200 months or approx 15+ years before the whistle blows. Given the pace of progress, it is somewhat doubtful that much of the process will remain static for that period of time, so the process changes will certainly impact the time scale (and probably in your favor).

My conclusion would be to borrow the tag line from Alfred E. Newman - What? Me Worry?

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[ataxman]

The first reply was correct. It is based on the database file size. It would depend on the number of fields (and their size) for each record as to how many records you could max the database file size at.

I consistently work with databases of 1 to 4 million records with 14 to 20 fields per record. Like another reply mentioned though, query and report run times really start to lag when a table gets large or if the queries are not efficient.

 

[mikey69]

Agree with ataxman - query design and indexing are crucial. 5000 records is small, though. I've got several complex applications with around 150 000 records, which runacceptably fast even on an elderly machine.