Results 1 to 9 of 9

Thread: Compact and Repair MS Access Database

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Senior Member LalitPandey87's Avatar
    Join Date
    Sep 2011
    Posts
    222
    Rep Power
    14

    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.

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
  •