Auto compact datanbase ?

[brianjohnson]

Hi Folks,

I see that in Access 97 and following the opening of a database, it's possible to compact the opened database using menu options Tools/Database Utilities/Compact Database *without* needing to identify the database to be compacted and the new database name.

Could someone advise how to run this menu selection from code or a macro ? The Help notes the use of "RunCommand" but I can't see an option to compact the current database.

I guess the menu options assume it's the current db and does the the rest ? This database gets updated, renamed and copied around so I'd rather have a generic solution

TIA for any advice

Brian

 

[yippie_ky_yay]

Hey Brian,

Not sure if this works in 97 or not, but it does for 2000 and XP.

DBEngine.CompactDatabase (use this in VBA)

or you can -> Tools - Options - General (tab) - select "Compact on Close".

It really depends on what you're trying to accomplish. Let us know because there may still be more options.

Hope that helps!

-Sean

 

[jfgambit]

Here is a sample DB to compact a database on Close.

 

[brianjohnson]

Thanks to jfgambit & yippie

The issue for me is that copies of the database are held by various people. They get the database on EMail and generally rename it and store it locally - so when they get dbname.mdb in the post they may change it to (for example) dbname_july03.mdb.

What I need then is something that doesn't use current database name and new database name when the database is compacted - if I can access the menu system from code or when the application closes, I can ensure that the db is compacted whatever the db name might be - quite invisibly for the user - and so it gets saved as the file name used to open the database.

I could do this in code but would need the old and new database name. The menu system manages this whilst the database is open (or at least that's the way it looks to the user) so I thought this would be a good approach - something like "run menu, run database utilities, run compact database".

I'm doing all this because this database I've inherited uses an autonumber field to link tables and as you might know, this approach gets screwed up from time to time. Compacting the database resolves this on a temporary basis and gives me some time to come up with an alternative design approach.

It does seem that selecting Tools, Options, Advanced, Current Database Only, Command Line Arguments and inserting "Compact" should compact the current database on opening - am I right (not sure how to tell ! ?


Regards

Brian

 

[yippie_ky_yay]

Hey Brian - I have seen the "dbname_July31_2003.mdb" thing many times so I can sympathize!

The line of code I posted earlier does allow you to rename the db!
**start code**
Dim db As Database 'you might not need this line
Dim strOldDB As String
Dim strRenamedDB As String
Dim strPath As String
Dim intNameLength As Variant
Dim intPathLength As Variant

Set db = CurrentDb
strOldDB = db.Name
intNameLength = Len(Mid(db.Name, InStrRev(db.Name, "\") + 1))
intPathLength = Len(strOldDB) - intNameLength

strPath = Left(strOldDB, intPathLength)
strRenamedDB = strPath & "properDBname.mdb"

DBEngine.CompactDatabase strOldDB, strRenamedDB
**end of code**

I haven't tested this, but I think that should be close. This will put the db in the same folder that the user saved their copy in (you can easily change that though).

What kind of work are the users doing? If it's all data only work (no design changes or creation of forms, etc) then I would look into splitting the database instead of all this stuff (that is assuming that everyone has access to a shared drive). This will allow everyone to work on the db at the same time (only a record that two or more people try to modify at the same time will lock).

Hopefully this will work for you and you can avoid the posibility of two "dbname_July31_2003.mdb" files in your inbox!

Good Luck!
-Sean
PS if you use the above code and are in 2000 or XP, add a Microsoft DAO library reference.

 

[brianjohnson]

Thanks Sean

I'll get right on that.

The app is a club database. I look after it for errors and changes but the data is entered by the club secretary who in turn EMails regular copies back to me and to other club officers. They all tend to keep a few copies but what they choose to cal them and where they put them on their PC's is anybody's guess ! They all use Access 97 but use a range of OS's including Win 98, 2000 and XP.

What interested me was that it's possible to compact the db whilst in the db using menu options so seemed logical to seek to manipulate the menus from code.

I'll mail back when tested and let you know how it worked.

 

[brianjohnson]

For Sean

I've been all around this one. the IntStrRev function is available only it seems in VB 6. I can use string functions to cut bits out of the CurDir function and produce a new dbname .. the issue is that this approach can't be run when the db is already open - this is why I was so keen to understand how to use the built in menu bars but fraid I just can't see how to do it. Surely (hopefully !) Access 97 includes some sort of mechanism to manipulate the menu's from code or from a macro ? Such as from the main menu menu selecting Tools, Database Utilities, Compact Database ...

or am I completely off the track here ?

Regards

Brian

 

[yippie_ky_yay]

Hey Brian,

see if this is what you're looking for:
www.access-programmers.co.uk/forums/showthread.php?s=&threadid=52428&highlight=compact

OR
www.access-programmers.co.uk/forums/showthread.php?s=&threadid=6600&highlight=compact+database+with+code

These should work to automatically compact the database (no matter what the name/location). It won't help you rename the file though (I'm not sure if that could be done or not as the file would be locked when you open it), I think we would have to look into copying the db and then renaming the copy.

Anyways, let me know how it works out for you!

-Sean