Sorry up front, I'm 1/2 step up from a newbie, so please spell this out very clearly..
Using VB6 with Active X data controls 2.0, I have written a program that reads temperatures from multiple locations at 5 minute intervals. These temperatures are stored in a table in an Access database.
I only need to save the data for about 1 month. this makes my database size approximately 9 MB. To save any more info than this would be a waste of hard drive space.
Is there a way to limit the size of a database at design time?
Or, what would be the best way to accomplish this using VB6
I would like to store the new data at the end of the database, and drop off the first record.
I know this sounds like a simple problem, but I've been fighting this for too long.
At 9 MB a month, it will take you almost 95 years to fill a 10 GB hard drive - and they make them a lot bigger than 10 GB these days!
If you setup a "running buffer" by adding a new record and deleting the oldest record, you will encounter the "ballooning effect" of Access mdb's. When deleting records, you do not recover all of the physical file space those records occupied - especially if you perform block deletes. This is easy to replicate - open an mdb, add and then remove a set of records a few times and see how the file size increases even though the number of records does not. The space can be recovered using a compact, but that has some issues of it's own.
Sounds like you want a "circular buffer". If you index all of your records (they should be anyway) with an autonumber and set the table up with 8640 rows (one record every 5 minutes for 30 days), and include a full time stamp with each record, you can determine the last index value used by the record with the highest timestamp.
So, order of operations is:
1. Get Index using "DMax" or similar on the timestamp field
2. Add 1 to the value
3. If the value is now 8641, change it to 1
4. Change to the record whose index equals your new value (FindFirst)
5. Write your new data - include a full timestamp (use the Now() function)