Compact and Repair MS Access Database

LalitPandey87 · 11-07-2011 03:14 PM

The easiest way to avoid Database corruption is to compact & Repair it regularly.
In Access 2007, you can manually compact by clicking the Office Button, select Manage, and then choose Compact And Repair Database from the Manage This Database list.
To automate the process using VBA here is the Code.

Code: [View]




Function Database_Compact_and_Repaire(strDbPath As String, Optional strDBPass As String = "") As Long

        On Error GoTo ErrFailed
            'Delete the existing temp database
            If Len(Dir$(strDbPath & ".tmp")) Then
                VBA.Kill strDbPath & ".tmp"
            End If
            With CreateObject("JRO.JetEngine")
                If strDBPass = "" Then 'DB without password
                    .CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath & ".tmp;Jet OLEDB:Encrypt Database=True"
                Else             'Password protected db
                    .CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath & ";Jet OLEDBatabase Password=" & strDBPass, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath & ".tmp;Jet OLEDB:Encrypt Database=True;Jet OLEDBatabase Password=" & strDBPass
                End If
            End With
        On Error GoTo 0
            VBA.Kill strDbPath 'Delete the existing database
            Name strDbPath & ".tmp" As strDbPath 'Rename the compacted database
ErrFailed:
            Database_Compact_and_Repaire = Err.Number
            
            strDbPath = vbNullString
            strDBPass = vbNullString
    
End Function

You can call this function as below.
Without Password

Code: [View]



Call Database_Compact_and_Repaire("d:\test.MDB")

With Password

Code: [View]



Call Database_Compact_and_Repaire("d:\test.MDB", "password")

Admin · 11-07-2011 06:01 PM

Hi Mohan,

littleiitin · 01-21-2013 10:17 AM

The below is compatible with all access database (above code will not work for 2010 database) .The only requirement is add refrence for Microsoft Access:

This code can also make backup before compaction you need to just, pass paramete "True" after Database path.

Code: [View]

Sub test()
    Call CompactDatabase("Complete Path of Database")
End Sub

Code: [View]

Public Function CompactDatabase(sDatabasePath As String, Optional BackupBeforeCompactDB As Boolean = False) As Long
   
   'Microsoft Access Library must be added..
   
    Dim strTempFile     As String
    Dim strPathTemp     As String
    Dim strExtention    As String
    Dim strTempF        As String
    
    strPathTemp = WorksheetFunction.Substitute(sDatabasePath, ".", "|", Len(sDatabasePath) - Len(Replace(sDatabasePath, ".", "")))
    strTempFile = Left(sDatabasePath, InStr(1, strPathTemp, "|") - 1)
    strExtention = "." & Right(sDatabasePath, Len(sDatabasePath) - InStr(1, strPathTemp, "|"))
    
    On Error GoTo Err
    
    strTempF = strTempFile & "_Temp" & strExtention
    
    If BackupBeforeCompactDB = True Then
      FileCopy sDatabasePath, strTempFile & "_Backup" & strExtention
    End If
    Application.DisplayAlerts = False
    DBEngine.CompactDatabase sDatabasePath, strTempF, dbLangCyrillic
    Application.DisplayAlerts = True
    FileCopy strTempF, sDatabasePath
    Kill strTempF
    Exit Function
Err:
  CompactDatabase = Err.Number
  Err.Clear: Exit Function
End Function

Manfred · 08-07-2014 11:08 PM

Jack Adision · 09-08-2016 02:44 PM

Sana_Casa · 11-23-2016 04:37 PM

Manfred · 03-21-2017 10:46 PM

Hello, I followed these steps, but I was not lucky. I need to repair a database in Access 2010, which tool ldo you recommend? I used AccessFIX but it isn't free.

Originally Posted by Sana_Casa

If any of you is looking for a solution to repair Access database file, I have multiple solutions here to share. First you should try Compact and Repair utility, built-in tool to repair corrupt Access database. Microsoft also provides JetComp.exe that can repair Access database. There's one more thing you can do here. You can import corrupt database file into a new database file. I can share this blog post here in which you can find the complete information: Repair Access Database with Compact and Repair Utility | N. Sem's Blog

Before you can purchase any paid software, try the manual tricks given in the blog post shared above. I hope they would be helpful.

Thanks and best regards.

Sana_Casa · 04-17-2017 03:35 PM

Originally Posted by Manfred

Hello, I followed these steps, but I was not lucky.

What did you try? Was that Compact and Repair utility or JetComp.exe utility? Or did you try to import corrupt database into a new database? In case of severe corruption, you can't fix a corrupt database. You should, now, try a professional recovery solution to recover content from corrupt database file. This is all you can do here.

Originally Posted by Manfred

I need to repair a database in Access 2010, which tool do you recommend?

I can't recommend any such software since I haven't used any.

Originally Posted by Manfred

I used AccessFIX but it isn't free.

No freeware utility is available to repair Access database. There are only commercialware tools in the market, and most of them are available with free demo/trial version. There might be some tools in the market which are more effective and less expensive than the one you used. You should try them. But make sure you first download free demo/trial version.

Thanks.

Sana_Casa · 04-17-2017 03:41 PM

Originally Posted by Manfred

I need to repair a database in Access 2010, which tool do you recommend?

I was searching best Access database repair tool on Google, and I found this - http://www.data-recovery-solutions.c...e-recovery.php

You should try it once. But I'm recommending this software. And remember my words, try before you buy. You can download its demo version for free evaluation. By using demo version, you can see what you could recover from the corrupt Access database before you can actually pay for software. If you're satisfied with the results, then only you should purchase the software.

Hope it would be helpful for you.

Compact and Repair MS Access Database

Compact and Repair MS Access Database

How to repair Access database file?

You should a professional solution.

How to repair Access database?