Compact/Delete an access 97 db using VBA

[BinDogg]

Hello all,

Im having an issue with compacting an access 97 database. I am currently runing the following code which works fine ONCE! However, when I run the code a second time I get an error because the file i wish to create and compact already exists. Is there a way to delete the file before creating & compacting? a bit like......

' *** START

' *** desired code here
' delete Acces db "C:\compacted_db.mdb"

Application.DBEngine.CompactDatabase "C:\db_to_compact.mdb", "C:\compacted_db.mdb"

' *** END

if you coud also give any help on automatically zipping the compacted file "C:\compacted_db.mdb" (the desired result is to let a user email a set of files), then you truely would be a saint

Cheers, John

[sierra7]

Hi
You might want to read the 'Microsoft Scripting Runtime' thread which is running at present as this shows more sophisticated ways of handling files.

However, the quick and dirty way is to check whether the file exists and then delete it using the DIR() and Kill() functions. So it might look something like this (NOT TESTED!!)

If Dir("C:\compacted_db.mdb") = "compacted_db.mdb" Then Kill("C:\compacted_db.mdb")

I can't help you too much on zipping files but WinZip supports command line instructions but you need to download an Add-On from their site
After which you may be able to run something like

 Dim stAppName As String 
    stAppName = "WinZip C:\compacted_db.mdb, C:\compacted_db.zip"
    Call Shell(stAppName, 1)
 

but you will need to check the WinZip syntax

S7

[BinDogg]

Thanks S7

that works a treat and its the simplist bit of code to write (even if it is dirty).

i'll leave the zip feature for now as if a user can't zip a file then they should'nt be using a computer!

Once again many thanks