DB Growing Too Big, Too Fast

[LarryDK]

We are using a C application which receives real time data and then use ODBC to update several large Access tables with inserts (with several hundred thousand rows), and deleting and rebuilding a few other smaller tables (less than 1000 rows). The database is being compacted daily which is reducing it from 900 meg to 45 meg.

We have tried various combinations of openning and not openning the database between updates, but it does not seem to matter. Updates can occur every few minutes. We are new to ODBC and while the application seems to be working OK, the database growth is a real problem.

Is there a utility or way of knowing which table(s) may be causing the database to grow so fast? We are using Access 97.

Thanks for any help.

 

[THoey]

Really, I would think that the utility that you are looking for is called MS SQL Server or Oracle. The amount of data you are pumping in and out, the dropping and recreating of the tables, you would probably need to start compacting a lot more frequently.

Ms. Access doesn't ever really delete anything (records, forms, tables, etc). She just marks it so that it is invisible to you and your application. When you run the compact, all things marked as invisible are finally removed.

I know this probably wasn't what you wanted to hear, but IMHO, I would compact hourly (or so) until you have the chance to migrate to a more robust RDBMS. Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[THoey]

Really, I would think that the utility that you are looking for is called MS SQL Server or Oracle. The amount of data you are pumping in and out, the dropping and recreating of the tables, you would probably need to start compacting a lot more frequently.

Ms. Access doesn't ever really delete anything (records, forms, tables, etc). She just marks it so that it is invisible to you and your application. When you run the compact, all things marked as invisible are finally removed.

I know this probably wasn't what you wanted to hear, but IMHO, I would compact hourly (or so) until you have the chance to migrate to a more robust RDBMS. Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[7ony]

With that type of growth, I think you are deleting & inserting into the smaller tables more frequently than expected. It the small tables are just temporary tables, try creating 2 Access Databases, one w/ the perminant files & the other w/ the ever changing, temporary files. Then, simply compact the temporary.mdb. Or, just re-create it when needed, depending on your needs.
That sort of growth sounds suspicious. Perhaps theres a re-think needed.
Good luck,
7ony