Did you see that I have an RSS feed now?
As with most development projects, this one was born from necessity. For reasons that are not important I push a large amount of data down to an Access database every night and, Access being Access, the file size becomes an issue all too often as it doesn't really know how to deal with large INSERTS and DELETES without expanding exponentially... Anyway, to cut a long story short, this Access database is constantly locked by another process so in order to clear the locks and compact the database I would need to reboot the server it was sat on and open it / compact it as soon as it came back up. This is until I was reminded a couple of weeks back about the Computer Management console which allows me to see and administer remote connections to shared files (such as this database)
All I needed to be able to do was programmatically find any sessions locking the database and then kill them off and then I could programmatically compact the database, write a log and jobs a goodun. The
WMI script that I need (I tried a few) was ServerSession. I got the basic code using the
scriptomatic tool which is a (VERY USEFUL) free download from MS.
Then all I needed to do was code it. You will see that in the video. The best thing about it being a windows application is that, as an exe, I can schedule it to run as part of the overnight backup so I should never see the mdb going to stupid size ever again. Here is what the log file looks like
A final warning would be to make sure you set the Engine in the Jet connection correctly for the Access version of the database you are trying to compact. In this example the database is Access '97 (I KNOW!) which is engine type 4. Most newer ones are 5. Enjoy. Demo code available
here!
http://www.hotlinkfiles.com/files/1533148_mphs9/CompactAccess.flv
266 views.