Compacting Database through VB

[Hashish]

I am working on a database and on a specific form i have a button for
'Compact Database'. Now the code i use to compact onClick of the button is:

CommandBars("Menu Bar").Controls("Tools").Controls("Database
utilities").Controls("Compact and repair database...").accDoDefaultAction

This works fine except if a guy in say, japan, uses it and has a japanese
language pack, then access cant find "Menu Bar", "Tools", etc. cause they are
in english in the code and in japanese in access and a problem occurs.

so i wanted to know if there is another way to compact where i could
probably use some internal properties etc. and where language wont be a
problem. I need to compact an active database....

 

[Arvin Meyer [MVP]]

All databases must be closed to be compacted. The only other way to compact
the "current" database is to set Compact on Close in Tools >>> Options

 

[Hashish]

But when i select 'Compact & repair database' from 'database utilities' in
'tools' then access compacts my open database. So there must be a way to
compact an open database through code as well...Any thoughts...?

 

[Douglas J. Steele]

http://www.mvps.org/access/general/gen0041.htm

You can also use MichKa's TSI SOON (Shut One, Open New) database add-in
http://www.trigeminal.com/utility.asp?ItemID=8#8

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Stockwell43]

Go to "Tools", "Options" and click on the "General" tab. You will a check box

 

[Arvin Meyer [MVP]]

Maybe I have to say it louder. You cannot compact an open database. The code
you posted uses the msaccess.exe to run the code and reopen the database.
Other methods use created databases to do it. The reason a database must be
closed is because what the compacting process does is:

1. Close the database.
2. Create another database and rewrite the file.
3. Check for corruption of the new file.
4. Delete the original database file
5. Rename the new file with the old name.

You cannot delete or rename an open file. It must be closed.

I would think that the method used by accDoDefaultAction is the same in any
language.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Stockwell43]

I did not post any code for compacting the database. I simply used the tools
offered within the database. If your not suppose to use it, why did Microsoft
put it there? I've used it on several databases and never had a problem.
Unless I totally missed the question was asking for?

 

[Dennis]

But when i select 'Compact & repair database' from 'database utilities' in
'tools' then access compacts my open database. So there must be a way to
compact an open database through code as well...Any thoughts...?

No, it DOESN'T. It creates a NEW database (a temproary one), imports all the
contents of your original database into it, compacts THAT (temporary)
database, deletes your open one, renames the temporary one to the name of
your original, and re-opens it.

Clear enough? You can watch this behavior in action by having Windows
Explorer open and pointing to the directory where this is taking place.

 

['69 Camaro]

Hi, Arvin.

I would think that the method used by accDoDefaultAction is the same in
any language.

It is, but Hashish is calling it through the CommandBars collection. There
is no CommandBars("Menu Bar") object in the Japanese version of Access.
He'll have to find out what the Japanese equivalent is and put it into an
IF-block, then have his VBA procedure check which language setting is being
used and execute the appropriate line of code. And that will only work if
it's an Access 2000 database or newer version, as I'm sure you know, but I'm
stating this for other readers who may not be aware that Access 97 and older
won't execute code to compact the current database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.