compacting a database on-the-fly
-
Hi All, When you delete records from tables in a .MDB database file, the record space is marked as available, but is not removed from the file. So the file size grows over time. As near as I can tell, the only way to reclaim that space is to compact the database. It would be really nice if the "available" space could be re-used by new records, but it doesn't look like this can happen. It would also be nice if you could compact just a single table in the database, but again, it looks like you have to do the whole .MDB file. To compact a database, you must have exclusive access to it, and it must be closed (so how do you tell other applications to drop their connections to it?). The database file must be compacted to a temporary file, and then the original deleted, and the temp file renamed. This is a major pain when you have an application that is running ALL the time (and also when there are other apps also accessing the .MDB file). Does anybody know of a way to reclaim the "available" space in a .MDB file, either by have the space be re-used for new records, by compacting only a table within the database, or by being able to compact the database without having to shut EVERYTHING down? Thanks VERY much! - Sean
-
Hi All, When you delete records from tables in a .MDB database file, the record space is marked as available, but is not removed from the file. So the file size grows over time. As near as I can tell, the only way to reclaim that space is to compact the database. It would be really nice if the "available" space could be re-used by new records, but it doesn't look like this can happen. It would also be nice if you could compact just a single table in the database, but again, it looks like you have to do the whole .MDB file. To compact a database, you must have exclusive access to it, and it must be closed (so how do you tell other applications to drop their connections to it?). The database file must be compacted to a temporary file, and then the original deleted, and the temp file renamed. This is a major pain when you have an application that is running ALL the time (and also when there are other apps also accessing the .MDB file). Does anybody know of a way to reclaim the "available" space in a .MDB file, either by have the space be re-used for new records, by compacting only a table within the database, or by being able to compact the database without having to shut EVERYTHING down? Thanks VERY much! - Sean
I don't believe that can do that... I think that is better pass to SQL Server, or to My Sql, it last one is very cheaper... best Regards.... Carlos Antollini.
-
I don't believe that can do that... I think that is better pass to SQL Server, or to My Sql, it last one is very cheaper... best Regards.... Carlos Antollini.
This is a Visual C++ app, using a CDaoDatabase (and the Jet engine). My current solution is to check the database size at startup, and prompt the user to see if they want to perform a compaction if the database is larger than a certain threshold. The user is also prompted to shut down other applications which may be accessing the database. The problem with this solution, is that it forces the user to periodically shut down and re-start the application (as well as other applications). To do the compaction, I use the CDaoWorkspace static member function CompactDatabase. This works, but I wish there was a better solution. Thanks - Sean
-
This is a Visual C++ app, using a CDaoDatabase (and the Jet engine). My current solution is to check the database size at startup, and prompt the user to see if they want to perform a compaction if the database is larger than a certain threshold. The user is also prompted to shut down other applications which may be accessing the database. The problem with this solution, is that it forces the user to periodically shut down and re-start the application (as well as other applications). To do the compaction, I use the CDaoWorkspace static member function CompactDatabase. This works, but I wish there was a better solution. Thanks - Sean
Yes I know that. I worked with DAO several years, but the problem is that you need the database in exclusive mode for compact it. Is for this that I said you that I don't know if exist any solution, CDAOWorkspace need that nobody have the database open, for compact it. May be Acces 2000 has some commands like SQL has but I don't Know.... Best Regards.... Carlos Antollini.