Results 1 to 2 of 2

Thread: [FAQ's: OD] How do I perform a Compact & Repair on my database?

  1. #1

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    [FAQ's: OD] How do I perform a Compact & Repair on my database?

    Compacting and Repairing helps to keep your database in top running condition.
    Quote Originally Posted by Access Help File
    As you change data in a database, the database file can become fragmented and use more disk space than is necessary. Periodically, you can use the CompactDatabase method to compact your database to defragment the database file. The compacted database is usually smaller and often runs faster. You can also change the collating order, the encryption, or the version of the data format while you copy and compact the database.

    Compacting optimizes the performance of both Access databases and Access projects.
    You can perform a Compact and Repair on your database manually four ways or at least one way via code.


    A Compact and Repair can be performed automatically every time you close your database...
    Set the option in the Options menu when you have your database open.
    Go to Tools > Options > General tab > check the Compact on Close check box.


    To Compact and Repair a currently open database manually...
    Go to Tools > Database Utilities > click Compact and Repair Database/Project.


    To Compact and Repair a closed database manually...
    1. Go to Tools > Database Utilities > click Compact and Repair Database.
    2. In the Database to Compact From dialog box, specify the Access file you want to compact, and then click Compact.
    3. In the Compact Database Into dialog box, specify a name, drive, and folder for the compacted Access file. Click the Save button.


    A Compact can be done easily via a commandline switch of "/compact".
    /compact - Compacts and repairs the Access database, or compacts the Access project specified before the /compact option and then closes Access. You can execute it from the Run dialog or from another program using the VB 6 Shell method or ShellExecute API for example.



    To Compact And/Or Repair via code...

    VB 6 Code Example:

    VB Code:
    1. Option Explicit
    2. 'Add a reference to MS Access xx.0 Object Library
    3. Private Sub Command1_Click()
    4.  
    5.     On Error Goto MyError
    6.  
    7.     Dim oApp As Access.Application
    8.  
    9.     Set oApp = New Access.Application
    10.     oApp.DBEngine.CompactDatabase "C:\RobDog888.mdb", "C:\RobDog888Cpd.mdb"
    11.     Kill "C:\RobDog888.mdb"
    12.     Name "C:\RobDog888Cpd.mdb" As "C:\RobDog888.mdb"
    13.     oApp.DBEngine.RepairDatabase "C:\RobDog888.mdb"
    14.     MsgBox "Compact Complete!", vbOkOnly
    15.     oApp.Quit acQuitSaveNone
    16.     Set oApp = Nothing
    17.     Exit Sub
    18.  
    19. MyError:
    20.     MsgBox Err.Number & " - " & Err.Description, vbOkOnly
    21. End Sub

    To Compact & Repair via code...

    VB 6 Code Example:

    VB Code:
    1. Option Explicit
    2. 'Add a reference to MS Access xx.0 Object Library
    3. Private Sub Command1_Click()
    4.  
    5.     On Error GoTo MyError
    6.  
    7.     Dim oApp As Access.Application
    8.  
    9.     Set oApp = New Access.Application
    10.     oApp.CompactRepair "C:\RobDog888.mdb", "C:\RobDog888CRd.mdb", False
    11.     Kill "C:\RobDog888.mdb"
    12.     Name "C:\RobDog888CRd.mdb" As "C:\RobDog888.mdb"
    13.     MsgBox "Compact & Repair Complete!", vbOKOnly
    14.     oApp.Quit acQuitSaveNone
    15.     Set oApp = Nothing
    16.     Exit Sub
    17.  
    18. MyError:
    19.     MsgBox Err.Number & " - " & Err.Description, vbOKOnly
    20. End Sub
    Last edited by RobDog888; Aug 29th, 2006 at 07:41 AM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  2. #2

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [FAQ's: OD] How do I perform a Compact & Repair on my database?

    To do a C & R on an Access database using ADO/ADO.NET...

    Classic VB 6:
    http://support.microsoft.com/kb/230501/

    Visual Basic.NET:
    http://support.microsoft.com/default...b;en-us;306287

    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

Posting Permissions

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



Click Here to Expand Forum to Full Width