Compacting an Access database with ADO and Delphi

Compacting an Access database with ADO and Delphi
While working in a database application you change data in a database, the database becomes fragmented and uses more disk space than is necessary. Periodically, you can compact your database to defragment the database file. This article shows how to use JRO from Delphi in order to compact an Access database from code.
Why compacting While you add and delete records from database tables, your database becomes more and more fragmented and uses disk space inefficiently. Compacting a database makes a copy of the database, rearranging how the database file is stored on disk. The compacted database is usually smaller and often runs faster. This chapter of the free database course for Delphi beginners shows how to use JRO from Delphi in order to compact an Access database from code. JRO TLB ADO does not directly expose a method for compacting a database. By using Jet and Replication Objects (JRO), you can compact databases, refresh data from the cache, and create and maintain replicated databases. The JRO exposes two objects, the JetEngine object and the Replica object. The Replica object is used to manipulate replicated databases. We will not deal with database replications in this chapter. By using the Jet Engine object we can programmatically control compacting and refreshing data from the memory cache. As with ADOX, the JRO library must be imported in Delphi, since it is not a part of the ADOExpress (or dbGo in D6). The description of the ADOX library is "Microsoft Jet and Replication Objects 2.x Library (Version 2.x)". The JRO library file name is MSJRO.dll. We've already seen the steps needed to import a type library in Delphi (ADOX). The same process should be repeated in this case. To import JRO in Delphi you need to open a new project and Select Project | Import Type Library. In the dialog box choose "Microsoft Jet and Replication Objects 2.x Library (Version 2.x)". Note that it will add two new classes, the TReplica and TJetEngine. Press Install button to add JRO to a package or press Create unit to just create a single interface unit. If you click Install, two new icons will appear on the ActiveX tab (if you have left the default Palette page on the Dialog). Note: Delphi 6 users will not succeed in importing JRO type library. If you have Delphi 6, while trying to install the library in a package, an error will pop up indicating that ActiveConnection in the JRO_TLB file doesn't exist (along with some other errors). The problem lies in Delphi 6 TLB importer. There are two options to overcome the problem: 1. Use Delphi 5 to import JRO an then install it in Delphi 6. 2. Manually declare the missing ActiveConnection property and change property declarations to make them writeable. Compact Delphi Project It's time to see some code. Create a new Delphi application with one form. Add two Edit controls and a Button. From the ActiveX component page pick JetEngine. The first Edit should be renamed to edSource, the second one to edDest. The button should be renamed to btnComapct. The JetEngine should be renamed to JE. It should all look like: The TJetEngine class has a CompactDatabase method. The method takes two parameters: the ADO connection string for the source as well for the destination database. CompactDatabase method compacts a database and gives you the option of changing its version, password, collating order and encryption. Encrypting a database makes it indecipherable by a utility program or word processor. Encrypted databases can still be opened by Access or through Delphi code. The proper way to protect a database is to set a password for it. Collation order is used for string comparison in the database. Changing a database version gives you the way to "upgrade" it. In our form, the edSource is used to specify the database we want to compact. The edDest specifies the destination database. Within the connection strings, you specify various connection properties to determine how the source database is opened and how the destination database is compacted. At a minimum, you must use the Data Source property in each connection string to specify the path and name of the database. When you use the CompactDatabase method, you can't save the compacted database to the same name as the original database. CompactDatabase also requires that the destination database does not exist. The next code (btnCompact OnClick event handler) is an example of the CompactDatabase method: procedure TForm1.btnCompactClick(Sender: TObject); var dbSrc : WideString; dbDest : WideString; const SProvider = 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source='; begin dbSrc := SProvider + edSource.Text; dbDest := SProvider + edDest.Text; if FileExists(edDest.Text) then DeleteFile(edDest.Text); JE.CompactDatabase(dbSrc,dbDest); end; Note that the above code presumes an Access 2000 database. Microsoft Jet OLEDB 4.0 is the default data engine for Access 2000. In many cases you'll want to have the same database name after the compact operation. Since edSource and edDest can't be the same your code should replace the original file with the compacted version. The next function takes only one parameter - the name of the database you want to compact: function DatabaseCompact (const sdbName: WideString) : boolean; var JE : TJetEngine; //Jet Engine sdbTemp : WideString; //TEMP database sdbTempConn : WideString; //Connection string const SProvider = 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source='; begin Result:=False; sdbTemp := ExtractFileDir(sdbName) + 'TEMP' + ExtractFileName(sdbName); sdbTempConn := SProvider + sdbtemp; if FileExists(sdbTemp) then DeleteFile(sdbTemp); JE:= TJetEngine.Create(Application); try try JE.CompactDatabase(SProvider + sdbName, sdbTempConn); DeleteFile(sdbName); RenameFile(sdbTemp, sdbName); except on E:Exception do ShowMessage(E.Message); end; finally JE.FreeOnRelease; Result:=True; end; end; The DatabaseCompact receives a sdbName string parameter with the full name of the database you want to compact. The function returns True if compact is successful False otherwise. The sdbName is compacted in sdbTemp, the sdbName is then deleted and sdbTemp renamed to sdbName. The DatabaseCompact could be called as: DatabaseCompact('C:\ADP\aboutdelphi.mdb'); The DatabaseCompact function is ideal to be called from within your Delphi ADO application as an external application. It could also be written as a console mode application that takes one command line parameter (or more) since it requires no GUI.