CODEWITHSUNDEEP
ms-accessvbams-access-2010
MyNameIsKhan
MyNameIsKhan

Reputation: 2612

Compacting and repairing a list of other databases

Most of the resources I can find on this are from several years past for older versions of Access and I am curious if best practices / new methods have come to light.

I have a table containing paths to a bunch of .mdb files that I wish to compact/repair. What is the correct way to do it? Is this still the right way?

Function Compact_DB(path as String, databaseName as String) 
    'for example, path = "C:\MyFiles\dev\" 
    'databaseName = "MyDatabase.mdb"

    'COMPACT CHOSEN DATABASE, TO TEMPORARY DATABASE NAME 
    DBEngine.CompactDatabase path & databaseName, path & "Spare1.mdb" 

    'DELETE OLD DATABASE 
    Kill path & databaseName

    'RENAME TEMPORARY DATABASE TO ORIGINAL NAME 
    Name path & "Spare1.mdb" As path & databaseName 

End Function

And then just run this in a loop over all my databases?

Sub Compact_All()
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    DoCmd.SetWarnings False

    rs.Open "select * from compactList", CurrentProject.Connection

    While Not rs.EOF
        call Compact_DB(rs("path"), rs("databaseName"))
        rs.MoveNext
    Wend

    rs.Close
    set rs = Nothing
End Sub

Upvotes: 0

Views: 145

Answers (1)

Erik A
Erik A

Reputation: 32682

Most things haven't changed. Yes, this is still an appropriate way to go about it.

Just remove the stray DoCmd.SetWarnings False. That shouldn't be there.

A further note: Call is deprecated. Use Compact_DB rs("path"), rs("databaseName") instead of that line with Call.

Also, While ... WEnd is mostly replaced with Do While ... Loop. But that's just style.

Do note that all databases need to be fully closed by all users. You can't compact open databases.

Upvotes: 2

Related Questions