Visual Basic Tip: 02/12/96


Compact Database and Repair Database (VB 3.0).

The CompactDatabase statement essentially copies all the data and
security permissions settings from one database into another.  In the
process, the data in the resulting database is organized contiguously
to recover disk-space.  You must close a database before you compact it.
In a multiuser environment, you must make sure all other users have
closed the database as well.  To make sure, you can open the database
for exclusive use and then close it.

RepairDatabase attempts to repair a corrupted Microsoft Access database.
This statement repairs a Microsoft Access database that was left invalid
by an incomplete write operation.  This can occur if VB or your
application quits unexpectedly because of a power outage or computer
hardware problems.  RepairDatabase also attempts to validate all system
tables and all indexes.  Any data that cannot be salvaged is discarded.
It's a good idea to provide your users with a way to execute the 
RepairDatabase statement in your application if their database behaves
unpredictably.  It's also a good idea after repairing a database to
compact it with CompactDatabase


'**** Compacting a database file ****

  On Error Resume Next
  Dim tmpfile$, TheMsg$
  Dim Insize&, Outsize&, diff&, Ratio&
 
  AppPath$ = app.Path
  If Right$(AppPath$, 1) <> "\" Then AppPath$ = AppPath$ + "\"
  deffile$ = AppPath$ + "DATA.MDB"
  cr$ = Chr$(13) + Chr$(10)
  screen.MousePointer = 11

  tmpfile$ = AppPath$ + "DATA.TMP"
  Insize& = FileLen(deffile$)
  CompactDatabase deffile$, tmpfile$
  If Dir$(tmpfile$) <> "" Then
   Kill deffile$
   Name tmpfile$ As deffile$
  Else
   TheMsg$ = "Error Compressing Database File." + cr$
   TheMsg$ = TheMsg$ + "Database file not compressed!"
   screen.MousePointer = 0
   MsgBox TheMsg$, 48, "Error"
   Exit Sub
  End If
  Outsize& = FileLen(deffile$)
  diff& = Insize& - Outsize&
  Ratio& = 100 - (100 * (Outsize& / Insize&))

  Dim aa As String * 20
  TheMsg$ = "Compression complete!" + cr$ + cr$
  RSet aa = Format$(Insize&, "##,###,##0")
  TheMsg$ = TheMsg$ + "---------------------------------------------------------------------------" + cr$
  TheMsg$ = TheMsg$ + "Original File Size (bytes)        " + aa + cr$
  RSet aa = Format$(Outsize&, "##,###,##0")
  TheMsg$ = TheMsg$ + "Compressed File Size (bytes)" + aa & cr$
  RSet aa = Format$(diff&, "##,###,##0")
  TheMsg$ = TheMsg$ + "---------------------------------------------------------------------------" + cr$
  TheMsg$ = TheMsg$ + "File Space Freed Up (bytes)   " + aa + cr$ + cr$
  RSet aa = Format$(Ratio&, "##,###,##0")
  TheMsg$ = TheMsg$ + " Compression Percentage       " + aa

  screen.MousePointer = 0
  MsgBox TheMsg$, 64, "Compression Statistics"



'**** Repairing database file ****

  screen.MousePointer = 11

  AppPath$ = app.Path
  If Right$(AppPath$, 1) <> "\" Then AppPath$ = AppPath$ + "\"
  
  RepairDatabase AppPath$ + "DATA.MDB"
  screen.MousePointer = 0
  MsgBox "Repairs to your Database are Complete.", 64, "Repairs Complete!"

Return to Phil's Web Page

Created by: Philip