Compression of Database

[James D]

Does HB automatically compact the HBData.mdb file? Or does it compact the file periodically? The reason I am asking is because as I test my HB configurations I notice that the HBData.mdb file increases in size. Which is to be expected. I have Microsoft Access on my HB Automation Server. But when I open the database in Microsoft Access; I have the "Compact File on Exit" checked; and when I exit the database, Microsoft Access Compacts the HB.mdb file greatly. I had at one time a 20 meg file, and after opening the database in Access and exiting it, the file size reduced to 2.5 megs. Is there a way I can get HB to compact the HBData.mdb itself, without the use of Microsoft Access.

Thanks for all your help

[roussell]

I don't think it does - years ago I used vbscript to compact an Access DB without using MSAccess. I'd havent thought about doing that to HB, but it's probably a good idea.

Something like:

Code: Select all

Const CreateLog = True
Set objAccess = CreateObject("Access.Application")
errReturn = objAccess.CompactRepair _
    ("c:\test.mdb", "c:\test2.mdb", CreateLog)
Wscript.Echo "Compact/repair succeeded: " & errReturn

should work, but you'll probably want to add some code to make a backup copy of the DB and then do the repair.

Terry

[Richard Naninck]

This issue has been brought up before.
The dbase grows due to large alphalists being cleared and rebuilt which leaves holes in the dbase. V3 of HB should compact and backup the database automatically once a month.

Scott; correct me if I am wrong..

[ScottBot]

It can only compress the DB when it is not using it. So when HouseBot starts, it will check to see if it's been more than one month since it was last compressed. If it's been longer, it will compress the DB and save a copy named HBData.autobackup.mdb to your \HouseBot\Config\Backup directory. So the idea is to just compress it occationally... like when Windows installs an update and needs to restart.

You can also use the HBData.autobackup.mdb file in the horrible case that your main HBData.mdb database file gets lost and you haven't made a backup.

[James D]

Well thanks for the replies. Is there a way I can change the compression to weekly or is it hard-coded into the Housebot program? If it can not be done no big deal. I just did not want my database to keep growing and growing and growing to the point that it slows my server to a crawl, if I forget to compress it.

[ScottBot]

There is a registry setting that you can set yourself if you want to change the frequency. It would be in the HKLM along with the other HouseBot settings under the General Settings key. You can add a DWORD value named DB Compressed Time Period and set it to the number of seconds to trigger the update.

However, this only gets checked when the server is started. So unless you restart the HouseBot server every week, it won't compress more often.

[Richard Naninck]

Thanks for that tip.
But why not do it everytime the server restarts?
I guess it would save me some trouble as well because of the following;
When my PC crashes for whatever reason and HB doesn't close cleanly, I often have database trouble when restarting HB again. HB just stops while loading the dbase with an error I can't recall right now. It continues doing that until I run the dbase through MS Access and repair / compress. If this were to done automatically and always, it would always start. Unless of course this is somehow a dangerous thing to do, but I don't see why.

[roussell]

I'm guessing that you could set it to run everytime the server restarts by setting the registry value to 1 (second). That should ensure that the flag is always set.

Terry

[Richard Naninck]

Yep, I know. Hence the thank you for the tip. I was just wondering why it is set to one month and not just anytime a restart occurs.

[ScottBot]

It's one month simply because I saw it as more of a 'regular maintenance' type of thing that's just provides periodic house keeping. I wouldn't have thought that the DB would grow that much in a month where it would be causing problem. Mine doesn't, but it's very clear that many of your configurations are a bit more.... agressive... than mine. I made it a registry setting because I figured there may be some cases like these that would require a different interval.

Another reason for the 1 month time frame is because it not only compresses the DB, but it also produces a backup. So if you did it every time you started (maybe you were restarting so many times due to a DB/system issue) it would overwrite a potentially good backup DB.

But, feel free to set it to whatever you like.

[Richard Naninck]

Thanks for the explanation. I have never used the automated backup since I backup my complete Meedio/HouseBot setup almost daily because I add or change stuff just that much.

[James D]

So is there a way to isolate the automatic backup from the file compression? Like having the file to be backed up every month and the compression of the database everything you restart your server. I know it might slow the "splash screen" at the beginning of the HB. Especially us with slower computers or server. No pressure Scott just a thought, or idea, or whatever. LOL.

[Steve Horn]

... And/or add menu option(s) to the File drop down to allow for manual compressions. Already an option to back up.

[ScottBot]

So is there a way to isolate the automatic backup from the file compression?

It could be done, but this particular backup is just an emergency fall-back to *some* database file in the case the user had not executed a full backup. It's really not intended to be a full backup and may not restore the system to a perfect state if there were other configuration changes done between the time of the backup and the time of the problem.

The "full backukp" (done from the File menu) will not only backup the database file, but also all of the associated theme data (and everything else in the config directory). If anything, this is the process that should be done automatically.