Compact Current Database Release Notes

Version 2.0, March 14, 1999

The topic of how to compact the currently-opened database from code has come up frequently on the Access newsgroups. Although there is a menu command that can be used to accomplish this in Access 97, it isn't available from VBA code. Somebody recently posted some very clever VB code that could be used to create a separate program to accomplish this task (if someone knows who it was, please let me know so I can give them credit). The basic idea was to use GetObject to control the currently-running instance of Access, and use Automation methods to close, compact, and reopen the database. My only problem with doing this in VB is that I dislike the idea of a 2-disk install program for such a small utility. My solution was to convert the code to C++, so that the end result is a small EXE that doesn't require any runtime DLL's or a special install program.

What's New in Version 2

The main thing that has changed with version 2 is that I have added support for several command-line parameters:

/verbose - when this parameter is used, CompactCurrent will display any errors that occur to the user. By default, CompactCurrent will just exit if an error occurs, which is what version 1 did.

/exit - this parameter tells CompactCurrent to close Access after compacting the database, rather than reopening the database.

/prompt - instructs CompactCurrent to display an about dialog before compacting. The dialog will show the product name and version, and give the user a chance to continue with compacting or cancelling without compacting.

/timeout n - Specifies the timeout value, in seconds, used by CompactCurrent. Sometimes Access doesn't respond to the GetActiveObject call right away (especially if you're using the Shell statement), so CompactCurrent will keep trying until this timeout value is hit. The default timeout if this parameter is not used is 3 seconds.

The other addition I've made is a BAS module that contains a CompactCurrentDatabase() function in it. This function uses the ShellExecute API to launch CompactCurrent rather than the Shell statement. This should avoid the problems mentioned below. This should make it easier to use CompactCurrent if you aren't used to making API calls and don't want to use the Shell statement.

A special thanks to Michael Dunphy for helping me get to the bottom of the GetActiveObject() problem that occurs when using the Shell statement (see below).

Known Issues

If you find any bugs, please send me email. There are a few things you should know about the Shell statement in Access that can cause problems with CompactCurrent:

The biggest problem people had with version 1 is that sometimes when CompactCurrent was run it wouldn't seem to do anything. The problem turned out to be that sometimes Access wouldn't respond to the GetActiveObject call that CompactCurrent uses. So far it looks like this only happens when using the Shell statement to launch CompactCurrent. If you wait long enough, Access will eventually respond, but sometimes it can take as much as 5-10 seconds. Version 2 of CompactCurrent will keep trying the GetActiveObject call until it succeeds or the timeout value is hit, which should help in most cases. To comletely avoid this problem, I recommend that you use the CompactCurrentDatabase() VBA function that I've written, which uses ShellExecute instead of Shell.

Some people have complained that the CompactCurrent window gets hidden by the Access window so that you can't see it. This is because the default behavior of the Shell statement is to launch the program minimized. If you specify vbNormalFocus for the windowstyle parameter, you should get the desired behavior.

Future Enhancements

I said before that I wouldn't make any more changes, and look what happened. This time I really mean it, though. I'll fix any bugs that get reported to me, but I won't be doing another upgrade to CompactCurrent. I don't even do programming in Access anymore, so this utility has little use for me. I've supplied the sourcecode for anyone who would like to take over development.

Freeware

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.