Maintain Database for Large No of Records

[shalsedar]

I need to maintain Access DB such that it stores large set of data for years coming along.
As Access have limitations in the data storage Capacity how is it possible for such large data sets of data.
Can anyone help me on it.
Thanks

 

[isladogs]

Hello and welcome to AWF

Use a split database with data in one or more backend.
For maximum scalability and stability use SQL Server for backend db

 

[The_Doc_Man]

The question you asked is perfectly legitimate. The problem is, it didn't explain enough.

So... you have these large data sets spanning years. What are you going to do with them and how long will you retain the data?

Also... do you need to retain detailed data at all times or is there a chance that you could build summaries based on some time period - week, month, year - so that after enough time would pass, you could just summarize? Or will you need to keep a few years of continuously available detailed data?

And finally, can you define "large set of data" in more precise terms? E.g. how long is a single record (estimate OK here) and how many records of this type do you accumulate in a given period?

Colin's answer isn't wrong, but there is the possibility that what you consider to be "large" is, in fact, not so large after all. But if you don't tell us some sizing numbers, we can't tell whether your situation will be comfortable or dire in a year or two.

 

[shalsedar]

Thanks for the feedback,
Yes the DB contains large data sets spanning years and need to retain the data so that we can build up Summarized Report based on it.
Table contains upto 297847 records on weekly basis,so may be at certain point it wud be impossible to store data due to data storage limitations,so in this case what cud be the best solution to handle this data.

 

[The_Doc_Man]

With over a quarter of a million records in a week (=12 million in a year), this is DEFINITELY time to think about SQL server as the back-end, though Access as a front-end integrates nicely. A record-header by itself will take up space and for these records to be meaningful, you will almost certainly have a few bytes per record.

Your problem would be in storage of these tables, but ALSO in maintenance thereof. If this is one big table, you have the problem that it would run out of space PERMANENTLY when the table grew to be 2 GB in size, BUT when it got to be about 1/2 that, you would never again be able to compact & repair it because of the way Access does that. (It has to copy the table before it can delete the old one.)

The other possible solution would be to break the table apart by year, with a separate database file for each year of data, and then use a UNION query to put the tables back together. However, there are limits to the size of an Access recordset and I don't recall them offhand. What you are describing is more data than Access can store, though it is not necessarily more than Access could process using SQL Server or ORACLE or SYBASE as a back end.

 

[static]

You'd build the result up in a temp table rather than using UNION.

Delete * from tmp
INSERT INTO tmp SELECT * FROM [2017.accdb].table1 WHERE field1='foo'
INSERT INTO tmp SELECT * FROM [2016.accdb].table1 WHERE field1='foo'
INSERT INTO tmp SELECT * FROM [2015.accdb].table1 WHERE field1='foo'

docmd.opentable "tmp"

 

[The_Doc_Man]

I checked for "Access Specifications" because something was nibbling at me. You are absolutely going to need to step away from an Access JET/ACE database as the BE file because of a limitation. When you talk about long records and lots of records, it doesn't matter whether you are looking at a lot of short records or a lesser number of very long records. You will reach a drop-dead point using an all-Access solution.

https://support.office.com/en-us/ar...ications-1E521481-7F9A-46F7-8ED9-EA9DFF1FA854

Recordset size 1 gigabyte

 

[static]

1GB is half the maximum size of a database so a filtered recordset shouldn't be a problem.*

The problem is the amount of forward planning required before you commit to the final solution.

*temporary storage exceeding 2gb could be.

 

[The_Doc_Man]

This is a case where if you chose to stay with Access, you would HAVE to do some intermediate summarization and then make summaries of the summaries. Even if you have an Access FE and an SQL BE, you might have issues with anything that has to be brought into the FE file. Letting SQL Server or some other suitable BE do local queries would help, which means you would need to learn about pass-through queries.

I wish you luck in this project, but you have a lot of design work to consider before you can start implementing this one.

 

[CJ_London]

To add to the Doc's comments. You can use SQL server express which is free - I believe the size limit is 10Gb rather than 2Gb. You also need to consider indexing - retrieving data based on an non indexed field will be very slow for such a large dataset.

As a rule of thumb, load say 4 weeks data into Access and indexed as required, compact, then note the file size. Multiply by 13 to provide an annual estimate.

I don't agree with the comment about compacting being limited to 1Gb file size. I've successfully compacted db's approaching the 2Gb limit. When access compacts, I believe the (simplified) process is:

1. create a new empty database
2. copy all objects to the new db on a component by component basis (records, indexes for tables) to eliminate space created by temporary objects
3. rename the new db the same as the old db.

I would also look at the data being stored - is it normalised? if not then normalise it. It can have a significant saving on space.

For example, say you are saving 250k invoices each week. If this has come through as a flat file containing customer name and address, invoice number and date and line item detail in a single record, normalising it into the three different components to eliminate duplicate can have a significant benefit.

OK, you'll need an additional index, but if done using longs rather than text will make a considerable saving. I've recently completed a similar exercise for a client - circa 2m records was bringing them close to the 2Gb limit. Normalising the data brought this down to around 300k.

This is a simplistic example, index pointers have been ignored for example. Consider a customer account number, name and address - perhaps on average 10 chars for the account number, 20 chars for the customer name and 60 for the address a total of 90 chars. So not being normalised you require 1m * 90=90,000,000 bytes of storage - plus indexing the customer account number 1m*10 =10,000,000. A total of 100,000,000 bytes.

Say in 1m records you have 50000 customers and you normalise it. so now you have

50,000*(90 bytes for the data, 4 for the PK and 4 for the index)=4,900,000
plus 1m * 4 for the FK and 4 for the index for the rest of the table)=8,000,000

a total of 12,900,000 bytes - 12.9% of the original storage requirement.

Something to consider....

 

[The_Doc_Man]

CJ, I think you are right. I'm not sure where I pulled out that "1 Gb limit" stuff. I guess I was having a senior moment or maybe a moment of low blood sugar.