MS Access tables storage capacity

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
Hi Experts;

I need a clear advice on the Ms Access tables storage size:
Our client with a supermarket has many products on the shelves, now our current Point of sales works as follows:
(1)      For every product line, five line entries are generated as follows (Revenue line, Vat Line, Cost of Sales Line, Stock line and the Cash/Receipt line)
(2)      All the five lines are stored in POS Table details
My question or worry is can this table manage to handle let say 850, 000 product sold per year which is equal (850000 X 5 lines = 4,250,000 lines) if an access table can handle that, then can we continue to use the same POS for the next 5 years? Will it not burst???????
The above line represent data that will be required for accounting purpose, example:
(1)      Revenue Account
(2)      Cost of sales Account
(3)      Vat output Account
(4)      Stock Account
(5)      Cash/Receipts Account
The performance as at now is very good no issues at all.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
An Access database file can easily hold that amount of records.
The absolut size limit is 2GB, so you could look at the current size holding the current count of POSs and extrapolate to an expected size.

That said, I would consider moving the backend to SQL Server. The Express edition is free to use. No hurry and no panic, but consider.
Software & Systems Engineer
Just a small remark. As known the maximum size of Access database is 2gb but this doesn't  mean that you can't split the data in many database...if i remember correctly a guy made a test via PERL and was able to connect to 500+ databases simultaneously.
On the other hand the obvious solution is to usev 3rd party engine
ste5anSenior Developer

As the others wrote, Access can handle this. BUT: you have a architectural issues here. This is where John's answer makes really sense.

Consider using a separate product (POS line) database per year or other meaningful time period.

This allows simple removal of old data and preparing the new years data without interfering with the current year.
Or when there is a quarterly stock-taking and adjustment of the products sold, then it may make sense to change it quarterly.

btw, the same considerations should be made when using an other backend than Access.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial