Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004

    Unanswered: Limiting size of Access DB with VB6

    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.

    Thanks in advance

  2. #2
    Join Date
    Apr 2004
    outside the rim
    I have a silly question: "Why bother?"

    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)

    Have fun
    have fun!

    Todd S.
    click to email

    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET

    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts