Unanswered: A97: How to compact secure & replicated DB from code?
I'm developing a replicated and secure database. Because of replication, file size grows like expected.
I want to compact the local replicated database on exit, and maybe twice since only then you seem to really regain lost space in replication.
In design mode with the menu bar visible, the built-in Compact-command under Tools/Database Utilities/Compact does exactly what I need: Close the local user's replicated DB, compact it and reopen, without prompting the user for a new DB-name or workgroup login info..
My problem is that this only works from within the menu bar.
In the distributed application I hide all built in menu bars from the user.
So my baseline question is: How do I get Access to start its internal Compact function with some code?
So far I've tried the following:
If I call the Compact method from the DBEngine, it wants a different DB-name to compact into.
If I use external utilities like mentioned in archives in this and other forums, the external compact function hangs everytime at security info missing. These external compactors do not work nicely with workgroup info, it seems.
If I copy the menu bar command button to my private menu bar, it still works like I want it to.
If I walk down the CommandBars collection all the way down to Compact This Database, it asks me for a new database name!
From your post:
"If I call the Compact method from the DBEngine, it wants a different DB-name to compact into"
That is perfectly correct. If you notice when you choose Compact from the toolbar, Access creates a dummy db called db1.mdb which is what it is compacting into, then deletes the old and renames the new when the compacting process is finished.
So, that said, you just to do this:
'Compact your Db into a file called DB_Compacted
DBEngine.CompactDatabase D:\MyDoc\DbName.mdb, D:\MyDoc\DbName_Compacted.mdb
'When it is compacted kill the original
'Rename the new one
Name D:\MyDoc\DbName_Compacted.mdb" As D:\MyDoc\DbName.mdb
Try it out on dummy Db's first to make sure all is well and good before going live!!!!!!!!
Your code sample is familiar to me, I've used this before.
Problem is that it will not run from within the database that I want to compact. You cannot compact an open database in this way.
It will only run from a second, external database. But I do not want the end users to start a second DB only for compacting.
Remember, the Compact button on the toolbar does exactly what I want. So what magic is there behind this built in option?
Maybe my problem sounds like:
How to create and open a second, temp database in a secure workgroup, that will close my original database, compact and rename it, and then reopen it again in this secure workgroup, with the same user name and password that my user issued at start (and with proper maintenance of replication ID's).
It may have to do with workspace-objects, but at the moment I am not familiar on that part of Access, and do not know how security will work out there.
OK - sorry misread the first communique. I rarely use the Security and the Workgroups utilities any more (too gunky for me) and have never had cause to use replicated databases so I may not be of any further help to you here.
However, I recently received in the mail a Smart Tip book from Element K Journals. One of the tips they offered was how to use a command line switch to compact databases and doing so by dropping a shortcut onto the user's desktop.
Basically : "Path to MS Access" "PathToDatabase" /compact
I'm not sure if it would work, but if you used it in tandem with the /wrkgrp switch (and /user and /pwd if need be) to join your workgroup first and then compact, it may help.
The Command Line switch is familiar to me. It may be of help for the sole purpose of compacting indeed. It doesnot start the database afterwards, only compacts.
Feel my solution lies somewhere in the Workspace object, that is in the middle of the DBEngine object (top level) and the actual Database level. Note: A Workspace is something different than a Workgroup, but this is my first time really playing with it.
May be Access starts a second database in the same Workspace and compacts/reopens the original database, all in the same Workspace.
Thanks anyway for your help, I'll keep on searching.