-
Apr 27th, 2006, 12:45 AM
#1
[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.
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:
Option Explicit
'Add a reference to MS Access xx.0 Object Library
Private Sub Command1_Click()
On Error Goto MyError
Dim oApp As Access.Application
Set oApp = New Access.Application
oApp.DBEngine.CompactDatabase "C:\RobDog888.mdb", "C:\RobDog888Cpd.mdb"
Kill "C:\RobDog888.mdb"
Name "C:\RobDog888Cpd.mdb" As "C:\RobDog888.mdb"
oApp.DBEngine.RepairDatabase "C:\RobDog888.mdb"
MsgBox "Compact Complete!", vbOkOnly
oApp.Quit acQuitSaveNone
Set oApp = Nothing
Exit Sub
MyError:
MsgBox Err.Number & " - " & Err.Description, vbOkOnly
End Sub
To Compact & Repair via code...
VB 6 Code Example:
VB Code:
Option Explicit
'Add a reference to MS Access xx.0 Object Library
Private Sub Command1_Click()
On Error GoTo MyError
Dim oApp As Access.Application
Set oApp = New Access.Application
oApp.CompactRepair "C:\RobDog888.mdb", "C:\RobDog888CRd.mdb", False
Kill "C:\RobDog888.mdb"
Name "C:\RobDog888CRd.mdb" As "C:\RobDog888.mdb"
MsgBox "Compact & Repair Complete!", vbOKOnly
oApp.Quit acQuitSaveNone
Set oApp = Nothing
Exit Sub
MyError:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Jun 10th, 2007, 04:49 PM
#2
Re: [FAQ's: OD] How do I perform a Compact & Repair on my database?
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|