Compact and Repair Multiple Databases by Code

Dim app As Access.Application

Private Sub Form_Load()
in VB6 this code will compact and repair a database. You should check to be sure that it is not already open, because compacting requires exclusive use and will throw an error if open.

Dim repairDB As Boolean
    Set app = New Access.Application
    repairDB = app.CompactRepair("C:\AAA testing\AccessStuff\TestMDB\firstimportcompact.mdb","C:\AAAtesting\AccessStuff\TestMDB\firstimportcompactDone.mdb")

If repairDB Then MsgBox "Successfully compacted"
End Sub


Bob

You also need to select a reference to MS Access 10 objects.

Bob

I just tested this code from a button on a form in Access 2k and it works the same except if the destination file exists it will throw an error.

Bob

I have an Access app I wrote that does exactly what you are looking for.
Anyone have a place we could post it?

Steve

Thanks Bob, I'll try that. What if I want to repair several Access Databases at the same time, or right after each other?
Steve, could you send the App to my email fcandreasson@yahoo.com

Thanks alot

You could create a loop that compacts each one. Use any list of the names of the databases to fill a variable in the loop.

or your loop could be within the function or you could send the application reference to the function for each one to compact
Dim app As Access.Application

Public Function  repairDB(nextdb as Access.Application) as boolean
    Set app = New Access.Application
    repairDB = app.CompactRepair("C:\AAAtesting\AccessStuff\TestMDB\firstimportcompact.mdb","C:\AAAtesting\AccessStuff\TestMDB\firstimportcompactDone.mdb")

    If repairDB Then MsgBox "Successfully compacted"
End Function

Bob

fmri
by the way it is against EE rules to post your email addess in a comment. You can put it in your profile, but not a comment.

see https://www.experts-exchange.com/help.jsp

Bob

I get the Error Message that "Member or Method could not be found" for the app.compactRepair
I am using Access 2000, and have referenced MS Access Objects 9.0
Is there anything else that I am missing, or can this not be done with MS 2000
Sorry about the email...I did not know :(

I need to name the Repaired DB the same as the Existing DB.
Is there a way to do this in MS Access 2000?

I have the dao 251/351 compatability library referenced. Tested this on 2k and works.

Private Sub Command0_Click()
DBEngine.CompactDatabase "c:\testaccess\jones.mdb", "c:\testaccess\jonesCompacted.mdb"
End Sub

Bob

Thanks Bob, this works.
So is this the only way to do it?
Is it possible to compact and repair all .mdb files in a folder automatically, or do I have to do add each one as stated above?

You could create variables for each filename and/or pathname and just do a loop which would change the value in the variable to each file in the folder.
Could use FSO to read the directory and run through each of the files.

Bob

Glad to help. Were you able to compact and repair all .mdb files in a folder automatically?
Bob

No, I was not. Still working on it.
If you have any suggestions...please feel free to post them, they are highly appreciated.
Thanks alot for your help!

Try changing the following from above

> Private Sub Command0_Click()
> DBEngine.CompactDatabase "c:\testaccess\jones.mdb",
> "c:\testaccess\jonesCompacted.mdb"
> End Sub

to:
' put all the files to be compacted in a single directory and run ........
Private Sub cmdLoopFilesDir_Click()
' uses ms dao 25/351 lib

Dim oldName As String
Dim newName As String


      ChDir (txt1)
      oldName = Dir(txt2.Text, vbDirectory)
      newName = Left(oldName, InStr(oldName, ".") - 1) & "Compacted" & Right(oldName, 4)
   
       DBEngine.CompactDatabase oldName, newName
       
      Kill oldName
      FileCopy newName, oldName
      Kill oldName

I didn't test this on 2k but it should work.

End Sub

Bob

Sorry, it;s getting late.
I meant to add a loop around this something like.......

Do While Dir <> ""
> 
>       ChDir (txt1)
>       oldName = Dir(txt2.Text, vbDirectory)
>       newName = Left(oldName, InStr(oldName, ".") - 1) & "Compacted"
> & Right(oldName, 4)
> 
>        DBEngine.CompactDatabase oldName, newName
> 
>       Kill oldName
>       FileCopy newName, oldName
>       Kill oldName
loop

Bob