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
    222
    Rep Power
    13

    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:10 PM.

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





    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwplzlpYpmRqjGZem14AaABAg. 9hrvbYRwXvg9ht4b7z00X0
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgyOGlCElBSbfPIzerF4AaABAg. 9hrehNPPnBu9ht4us7TtPr
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwHjKXf3ELkU4u4j254AaABAg. 9hr503K8PDg9ht5mfLcgpR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw1-OyZiDDxCHM2Rmp4AaABAg.9hqzs_MlQu-9ht5xNvQueN
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9htG01cKBzX
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htJ6TpIOXR
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwMKwGZpDjv7vi7pCx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugw6UrV69zpeKvLOeOV4AaABAg. 9ht16tzryC49htOKs4jh3M
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxVW-am20rQ5GFuJ9F4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugygb0YiLOI7fG1zQSx4AaABAg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugygb0YiLOI7fG1zQSx4AaABAg. 9htWqRrSIfP9i-fyT84gqd
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugygb0YiLOI7fG1zQSx4AaABAg. 9htWqRrSIfP9i-kIDl-3C9
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9i57J9GEOUB
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9i58MGeM8Lg
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz0Uy2bCSCTb1W-0_14AaABAg.9htChVuaX9W9i59prk5atY
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgwaWs6XDXdQybNb8tZ4AaABAg. 9i5yTldIQBn9i7NB1gjyBk
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=UgxV9eNHvztLfFBGsvZ4AaABAg. 9i5jEuidRs99i7NUtNNy1v
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugx2zSXUtmLBSDoNWph4AaABAg. 9i3IA0y4fqp9i7NySrZamd
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9i7Qs8kxEqH
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9i7TqGQYqTz
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAJSNws8Zz
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAJvZ6kmlx
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAK0g1dU7i
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKCDqNmnF
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKHVSTGHy
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKSBKPcJ6
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKgL6lrcT
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKlts8hKZ
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAKrX7UPP0
    https://www.youtube.com/watch?v=LuAipOW8BNQ&lc=Ugz4oMZ09MKcExYlWf94AaABAg. 9hwsCHaKX6A9iAL5MSjWpA
    Last edited by DocAElstein; 07-09-2023 at 07:59 PM.
    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
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    13
    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:24 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?

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g7lhoX-ar5
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg. 9f6hAjkC0ct9f8jleOui-u
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eekDyfS0CD
    https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg. 9edGvmwOLq99eevG7txd2c
    https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg
    https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg. 9dPo-OdLmZ09dc21kigjmr
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg
    https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg. 9cXui6zzkz09cZttH_-2Gf
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-13-2023 at 10:41 PM.

  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/



    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgwviLabd7r_3KpP6wh4AaABAg. 9h5lFRmix1R9h78GftO_iE
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h77HSGDH4A
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h76fafzcEJ
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h759YIjlaG
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg. 9h740K6COOA9h74pjGcbEq
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgyG714V_k7odQMrTz14AaABAg
    https://www.youtube.com/watch?v=2oT4qrHmDMY&lc=UgzJJUDVv2Mb6YGkPYh4AaABAg. 9h5uPRbWIZl9h7165DZdjg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg. 9fxrOrrvTln9g9wr8mv2CS
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g96yGbAX4t
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg. 9fz3_oaiUeK9g7lhoX-ar5
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh
    https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg. 9g9wJCunNRa9gJGhDZ4RI2
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg. 9f6hAjkC0ct9f8jleOui-u
    https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 07-12-2023 at 05:15 PM.

  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 10:56 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. Ranking In MS Access
    By Transformer in forum MS-Access Tips And Tricks
    Replies: 1
    Last Post: 09-15-2023, 07:12 AM
  2. Access Query Help
    By Vipergs8v10 in forum Access Help
    Replies: 2
    Last Post: 05-08-2013, 06:32 PM
  3. Free U.S. ZIP CODE Database...
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 2
    Last Post: 12-02-2012, 08:38 AM
  4. Checking Table Exist in Access Database or Not
    By littleiitin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 11-16-2011, 04:32 PM
  5. TreeView Use With Database
    By Rasm in forum Download Center
    Replies: 1
    Last Post: 04-13-2011, 07:34 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
  •