Results 1 to 9 of 9

Thread: Compact and Repair MS Access Database

  1. #1
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    220
    Rep Power
    6

    Compact and Repair MS Access Database

    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:
    
    
    
    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:
    
    
    Call Database_Compact_and_Repaire("d:\test.MDB")
    With Password
    Code:
    
    
    Call Database_Compact_and_Repaire("d:\test.MDB", "password")
    Last edited by LalitPandey87; 11-14-2011 at 10:40 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,086
    Rep Power
    10
    Hi Mohan,

    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Moderator littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    7
    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:
    Sub test()
        Call CompactDatabase("Complete Path of Database")
    End Sub
    Code:
    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
    Last edited by littleiitin; 01-21-2013 at 10:54 AM.

  4. #4
    Junior Member
    Join Date
    Aug 2014
    Posts
    4
    Rep Power
    0
    Ok it is the most common solution, but in Access 2010 it does not work when there is a severa corruption. I have seen many database where this process fails, then what do we do?

  5. #5
    Junior Member
    Join Date
    Sep 2016
    Posts
    1
    Rep Power
    0
    Access Database Repair software comes with all the major options and features with ribbon arrangement. So, there is no need to browse along the software for longer. All the options like Save, Select Database, Exit, Stop, Find Tree Item etc. are arranged in the ribbon. It also repairs corrupt MS Access (.MDB or .ACCDB) database file and then recover tables, queries, forms, macros, reports, etc. It also restores permanently deleted data from MDB file of MS Access 2013, 2010, 2007, 2003, 2002, 2000. To download visit: http://www.i-m.mx/stephanic/repairmsaccess/

  6. #6
    Junior Member
    Join Date
    Nov 2016
    Posts
    3
    Rep Power
    0

    How to repair Access database file?

    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.

  7. #7
    Junior Member
    Join Date
    Aug 2014
    Posts
    4
    Rep Power
    0
    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.

    Quote Originally Posted by Sana_Casa View Post
    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.
    Last edited by Manfred; 03-28-2017 at 11:26 PM.

  8. #8
    Junior Member
    Join Date
    Nov 2016
    Posts
    3
    Rep Power
    0

    You should a professional solution.

    Quote Originally Posted by Manfred View Post
    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.

    Quote Originally Posted by Manfred View Post
    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.

    Quote Originally Posted by Manfred View Post
    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.

  9. #9
    Junior Member
    Join Date
    Nov 2016
    Posts
    3
    Rep Power
    0

    How to repair Access database?

    Quote Originally Posted by Manfred View Post
    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.

Similar Threads

  1. Access Query Help
    By Vipergs8v10 in forum Access Help
    Replies: 2
    Last Post: 05-08-2013, 07:02 PM
  2. Ranking In MS Access
    By Transformer in forum MS-Access Tips And Tricks
    Replies: 0
    Last Post: 04-23-2013, 03:06 PM
  3. Free U.S. ZIP CODE Database...
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 12-02-2012, 09:08 AM
  4. Checking Table Exist in Access Database or Not
    By littleiitin in forum Excel Tips and Tricks
    Replies: 1
    Last Post: 11-16-2011, 05:02 PM
  5. TreeView Use With Database
    By Rasm in forum Download Center
    Replies: 1
    Last Post: 04-13-2011, 08:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •