by Jamie Grossfeld (2 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: Visual Basic 5.0
Difficulty: Unknown Difficulty
Originally Published: Sat 24th February 2001
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Compact ado jet database
VBcoders
'you will need two copies of the same database, with one connection. This code
'will compact and repair an ADO ( not DAO) database when the close
'click 'event is used. As soon as the connection is closed the database compacts.
'The code for compacting is from microsoft's knowledgebase, The other code is
'mine but feel free to use it. The code will also update a database when the
'click close event is fired.
Dim DbFile As String
Dim cn As ADODB.Connection 'Connect to the ADO Data Type
Dim rs As ADODB.Recordset
DbFile = "C:\Program Files\Microsoft Visual Studio\VB98\tracker\TrackerA.mdb"
On Error Resume Next
With datPrimaryRS.Recordset
.MoveNext
If .EOF Then .MoveLast
Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DbFile & ";" & _
"Persist Security Info=False"
cn.Open
cn.Close
Set cn = Nothing
Unload Me
End With 'This is important. You must close the connection to compact the database.
Dim JRO As JRO.JetEngine
Set JRO = New JRO.JetEngine
Kill "C:\My Documents\TrackerA.mdb"
JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\tracker\TrackerA.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\TrackerA.mdb;Jet OLEDB:Engine Type=4"
Kill "C:\Program Files\Microsoft Visual Studio\VB98\tracker\TrackerA.mdb"
JRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\TrackerA.mdb", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\tracker\TrackerA.mdb;Jet OLEDB:Engine Type=4"
End Sub
