by Justin Spencer (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 3.0
Difficulty: Intermediate
Date Added: Wed 3rd February 2021
Rating: 
(5 Votes)
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 https://www.microsoft.com/data.
Assumes
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).
Code 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
VBcoders'## 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
