Compacting a Database

With frequent use over a period of time, the performance of your database may deteriorate. When objects are deleted from a database but the space isn't reclaimed, fragmentation may occur. To improve the database performance and to reduce the database file size, you can compact or repair Microsoft Office Access databases by using the ADO extension library, Microsoft Jet and Replication Objects (JRO). In order to work with this object library, choose Tools | References in the Visual Basic application window and select Microsoft Jet and Replication Objects 2.5 Library.

You can compact a Microsoft Jet database by using the CompactDatabase method of the JRO JetEngine object. To compact the database, first ensure that it is closed. Provide a new filename for the compacted database, then rename or delete the original and rename the compacted database to the original name. The procedure below demonstrates how to compact the Northwind database.

©Hands-On 10-15: Compacting a Database

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the CompactDb procedure shown below.

' use the References dialog box to set up a reference to the ' Microsoft Jet and Replication Objects Library

Sub CompactDb()

Dim jetEng As JRO.JetEngine Dim strCompactFrom As String Dim strCompactTo As String Dim strPath As String strPath = CurrentProject.Path & "\" strCompactFrom = "Northwind.mdb" strCompactTo = "NorthwindComp.mdb"

' Make sure there isn't already a file with the ' name of the compacted database. On Error GoTo HandleErr

' Compact the database Set jetEng = New JRO.JetEngine jetEng.CompactDatabase "Data Source=" & _ strPath & strCompactFrom & ";", _ "Data Source=" & _ strPath & strCompactTo & ";"

' Delete the original database Kill strPath & strCompactFrom

Creating and Manipulating Databases with ADO

' Rename the file back to the original name

Name strPath & strCompactTo As strPath & strCompactFrom

ExitHere:

Set jetEng = Nothing MsgBox "Compacting completed." Exit Sub HandleErr:

MsgBox Err.Number & ": " & Err.Description Resume ExitHere End Sub

0 0

Post a comment