How many records can one table hold?

[jonathanchye]

I have a massive table holding all current and past invoices. The table have around 12 fields but mainly text and dates. So far we have around 5000 records. It took roughly 5 years to achieve this number though so we average 1000 records a year.

I am just wondering how many records can the table take? What can I do if the data exceeds the limit?

Thanks.

 

[gemma-the-husky]

millions. it won't exceed the limit

 

[boblarson]

As Dave said, it wont. Access tables can hold millions of records. The main constraint is the database file size. It can only hold 2Gb of data (including system information). So, that is really the thing to watch. If it climbs to over 1.5 Gb and compacting doesn't take it down at all, then it is time to start formulating a plan to either use a database file for a few separate tables (which you can do) or perhaps move to SQL Server or SQL Server Express (SQL Server Express 2008 R2 can hold 10Gb of data).

So, at 1,000 records a year I think you are safe for your lifetime and the lifetime of your kids and grandkids and beyond.

 

[jonathanchye]

Thanks for the reassurance guys

Overhauling my DB to combine all previous year data as that would make things much easier for me.

The size of DB (was designed in 2010 but now I am redesigning to be 2007 compatible) was around 70MB but I think that was because of the sheer number of subforms used which I hope to reduce.

Still 70MB is far way from 1.5GB