By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,709 Members | 1,650 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,709 IT Pros & Developers. It's quick & easy.

Compact/Delete an access 97 db using VBA

P: 2
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
Feb 15 '08 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 446
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!!)

Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1.  Dim stAppName As String 
  2.     stAppName = "WinZip C:\compacted_db.mdb, C:\"
  3.     Call Shell(stAppName, 1)
but you will need to check the WinZip syntax

Feb 18 '08 #2

P: 2
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
Feb 19 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.