How to compact the database from the code

[sabavno]

Hi

Is there a way to code the process of compacting the database?

Thanks

 

[FancyPrairie]

Look in OnLine Help for the CompactDatabase method of the Jet Engine.

 

[snoopy75]

The tricky thing about CompactDatabase is that it can't be used on the currently open database, only on another database that's closed. So what you can do, I guess, is set up another database (db2) with nothing but a module programmed to compact your database (db1). Then, make a command in db1 to open db2, then quit. In db2, compact db1, then reopen it.

Very messy, I know. Anyone know a neater way to do it?

--Ryan

 

[DarenNM]

Can't you activate the "Compact Database" menu option? I'm pretty sure that selecting this menu option causes Access to close and compact the database and then reopen the database. I don't have Access on this PC so I can't try this out. I'll check back when I have access to Access(!) to see if you solved the problem.

Hope this helps

Daren
Must think of a witty signature

 

[nberryman]

Tools, Options, General and select Compact on Close

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[oharab]

Or have a look at the FAQs.
I've written one: faq705-1955 called "How do I compact my Access 97 database from code?"
It will work for Access 2k+ as well. Youjust need to change the reference to DAO35 to DAO36

HTH

Ben ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[sabavno]

Thanks oharab,

I followed all your steps described in the FAQs,
but when I call the function

Dim strFile As String
strFile = "V:\Polina\BlackBerryDatabase\CompactDB.vbs"

Call fHandleFile(strFile)

I get a compile error "Argument not optional" pointing to the call fHandleFile

What is the appropriate way to call that function?

Thanks

 

[oharab]

Have I not fixed that yet!!!
Sorry, you need to add one of the constants:
WIN_NORMAL 'Open Normal
WIN_MAX 'Open Maximized
WIN_MIN 'Open Minimized

so you get fhandlefile(strfile,winnormal)

Will edit that now.

Cheers

Ben ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[sabavno]

Ok, thanks, now it works
It opens my CompactDB.vbs file
But it does not run the code in the CompactDB.vbs?

What should I do to force that code in vbs file to run?

Please help me with this

Thanks

 

[oharab]

By the sounds of it you do not have Scriptinf installed on your pc. It is usually installed with Internet Explorer 5 I think, but your system admin may have crippled itf.
To work round it, you will need to use another database file so:

Create a blank file and call it CompactDB.mdb

Copy this code into a module -

Function CompactExternalDB()
Dim jEngine As Object
Set jEngine = CreateObject("DAO.DBEngine.36&quot 'change this to DAO.DBEngine.35 if you are using Access 97
'Create a jet engine to work from
fn = Dir(Left(Command(), Len(Command()) - 3) & "ldb&quot
Do Until fn = ""
fn = Dir(Left(Command(), Len(Command()) - 3) & "ldb&quot
'check for an ldb file for the database we are compacting.
'can't sompact until everyone is out of it
Loop
jEngine.CompactDatabase Command(), Left(Command(), Len(Command()) - 3) & "cmp"
'compact the database to a file with a cmp extension
Kill Command
'delete the old file
Name Left(Command(), Len(Command()) - 3) & "cmp" As Command()
'rename the compacted file
Set jEngine = Nothing
Application.Quit acQuitSaveNone
'close this file
End Function

Save the module and create a new macro. The only thing you want in the macro is a RunCode command and type CompactExternalDB() in the option.

Save the macro with the name AutoExec and close the database file.

In the mdb you want to compact add the following subroutine:

Sub CompactThisDB()
Shell SysCmd(acSysCmdAccessDir) & "msaccess.exe " & "[PathToCompact.mdb]\compactdb.mdb /cmd " & CurrentDb.name
End Sub


Now when you call the CompactThisDB subroutine another database will open, the current database will close and be compacted.

There is no error checking and it is a little ugly at the minute and will not work with password protected files yet, but I will have a go at tarting it up later.

HTH

Ben ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------

 

[sabavno]

Thanks a lot!

One more question though...so now in order to compact the database I would always have to keep the CompactDB.mdb?

There is no other way to compact it having just one current database?

Thanks again

I appreciate your help

 

[oharab]

That's correct.
I am sure there is some reason why you can't compact the database whilst you are in it, but I can't think for the life of me what it is!

The good news is CompactDB.mdb is pretty reusable. One day I will get round to putting an enhanced version of it on my website.

Ben ----------------------------------------
Ben O'Hara
Home: bpo@SickOfSpam.RobotParade.co.uk
Work: bo104@SickOfSpam.westyorkshire.pnn.police.uk
(in case you've not worked it out get rid of Sick Of Spam to mail me!)
Web:

http://www.RobotParade.co.uk

----------------------------------------