Access Auto backup access database

[jimmycooker]

Hello,

I have a problem that I'm hoping someone can help me with.

Background:

I am trying to find a way to solve a problem I am having with a building management system. The BMS program is TREND and I use it to gather data from Electricity, Steam, Water and other meters around a production facility.

Altogether there are 150 meters. Each meter has a table in an access database with 2 columns that looks something like this:

23/06/2005 23:00 654655
23/06/2005 23:15 655456
23/06/2005 23:30 98751
23/06/2005 23:45 8654
24/06/2005 987512
24/06/2005 00:15 45564
24/06/2005 00:30 87546
24/06/2005 00:45 679546
24/06/2005 01:00 65465
24/06/2005 01:15 987212

The first column is a time index going back to when the meter was first setup on the system, this could be anything up to 3 or 4 years ago so that could mean (96 15 minute intervals per day x 365 days a year x 4years) 140,000 rows per table. When you multiply this my the number of meters (approx 150) you can see that the database is very big.

Each meter is connected to an outstation which can accomodate around 20 meters. These outstsions record and store the 15 minute data, which is sent to the TREND program once it gathers 96 readings, or 1 days worth of data.

The problem is that the TREND system was not built for the purposes of data aquisition and it cannot handle a database this big.

When the database gets too big it takes the program too long to retrieve the 96 newest points from the outstation and it times out resulting in a loss of data for that particular meter.

Solution:

What I think I need to do is find a way to keep the database that interacts with the TREND system at a set size i.e for any new rows that are added to a table on the bottom a corresponding number of rows will be reomoved from the top of the table. This way the databse size will stay roughly the same size.

The second thing I need to do is create a backup database that automatically adds the new rows from the TREND database (at some stage every day) without delelting any from the top. This way I have all my historical data in one place and the trend system doesn't slow down.

If anyone can help or direct to the right path please let me know.

Many thanks,
James.

 

[Techy]

Haven't heard of of "Trend" before.

but alot of small db apps can't handle large amount of data.

Do a bit of research and find out if this program will support MySQL datasources. if it does then you can migrate the data to MySQL. (MySQL is free) you could use Microsoft SQL, Oracle etc.

 

[jimmycooker]

Hi,

I think TREND (http://www.trend-controls.com/) are currently working on exactly this...i need to sort something out now though as i'm not sure how long they are going to take...do those type databases better than access db when they get bigger?

Thanks,
James.