[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Compact and Repair Multiple Databases by Code

Posted on 2005-04-20
17
Medium Priority
?
449 Views
Last Modified: 2012-05-05
Is there a way to create some program that would Compact and Repair Mutliple Databases.
Since it takes quite some time to do this, I would like to be able to Compact and Repair serveral MS Access Databases when I don't have to use them.
0
Comment
Question by:fmri
  • 11
  • 5
17 Comments
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13830712
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
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13830715
You also need to select a reference to MS Access 10 objects.

Bob
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13830774
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 39

Expert Comment

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

Steve
0
 

Author Comment

by:fmri
ID: 13834229
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
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13835287
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
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13835332
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 http://www.experts-exchange.com/help.jsp

Bob
0
 

Author Comment

by:fmri
ID: 13835976
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 :(
0
 

Author Comment

by:fmri
ID: 13836439
I need to name the Repaired DB the same as the Existing DB.
Is there a way to do this in MS Access 2000?
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13840042
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
0
 
LVL 12

Accepted Solution

by:
Bob Lamberson earned 500 total points
ID: 13840083
Then I reread your comment -
What I have always done is delete the original and rename the compacted one.

add these lines to the bottom of the code
Kill  "c:\testaccess\jones.mdb"
FileCopy "c:\testaccess\jonesCompacted.mdb","c:\testaccess\jones.mdb"
Kill "c:\testaccess\jonesCompacted.mdb"
Bob
0
 

Author Comment

by:fmri
ID: 13846987
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?
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13848519
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
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13871858
Glad to help. Were you able to compact and repair all .mdb files in a folder automatically?
Bob
0
 

Author Comment

by:fmri
ID: 13876248
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!
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13899834
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
0
 
LVL 12

Expert Comment

by:Bob Lamberson
ID: 13899844
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
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question