• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Compact ado jet database

Compact ado jet database

Compact ado jet database

Rate Compact ado jet database

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

'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