Search Tools Links Login

Compact Database using JRO (Jet & Replication objects)


I recently developed a database application and wanted to use only ADO and no DAO. I soon found out that compacting the Jet database was impossible using ADO (until 2.1+ came along that is). This code requires a reference to Microsoft Jet and Replication objects 2.1+ Library (which comes with ADO 2.1+). You can download this update from http://www.microsoft.com/data.

Original Author: Justin Spencer

Assumptions

I use this routine in the form_unload sub to compact the current database. If you were to try to compact while there was still an active connection, Jet locking would take over and return an error.
Set the current connection to nothing before compacting (set mcn = nothing).

Returns

True or False depending on success of operation

Side Effects

Not aware of any

API Declarations

'## Requires reference to Microsoft Jet and Replication objects 2.1+ Library (Standard ADO 2.1+ feature).
public const PASSWORD = "password" 'replace with database password

Code

'## To use:
private sub command1_click()
  msgbox compressdatabase ("C:database.mdb") '## Replace with path to database
end sub
Public Function CompressDatabase(mSourceDB As String) As Boolean
on error goto Err
  Dim JRO As JRO.JetEngine
  Set JRO = New JRO.JetEngine
  
  Dim srcDB As String
  Dim destDB As String
  
  srcDB = mSource
  destDB = "backup.mdb"
  
  JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & srcDB & ";Jet OLEDB:Database Password=" & PASSWORD, _
  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & destDB & ";Jet OLEDB:Database Password=" & PASSWORD & ";Jet OLEDB:Engine Type=4"
  Kill srcDB
  DoEvents
  Name destDB As srcDB
  compressdatabase = true
  exit function
Err:
  compressdatabase = false
End Function

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 95 times

Categories

Visual Basic 6

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.