Compacting database using ADO

[Mark Sreeves]

I used this code to compact an Acess97 database

Public Sub CompactDB()

On Error Resume Next

'close database
cnn.Close
Set cnn = Nothing

'delete the backup DB
Kill DataBase & ".bak"

'uses ADO 2.x and Microsoft Jet and Replication Objects 2.x

    'declare variables
    Dim objJRO As JRO.JetEngine
    Dim strConnSource As String
    Dim strConnDestination As String
    
    'set connection string info
    strConnSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataBase
    strConnDestination = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataBase & ".bak"
        
    'instantiate object
    Set objJRO = New JRO.JetEngine
    
    'compact database from source to destination
    objJRO.CompactDatabase strConnSource, strConnDestination
    
    'release objects
    Set objJRO = Nothing



'copy  back from temporary db to real one
FileCopy DataBase & ".bak", DataBase

  'now remake the connection..
  
  On Error GoTo errHandler

  Set cnn = New adodb.Connection
  
    
  cnn.Provider = "Microsoft.Jet.OLEDB.4.0;"
  cnn.Properties("Data Source") = DataBase
  cnn.Open
  Exit Sub

errHandler:

RaiseException SYSTEM_ERROR, , , , "Error Number: " & Err.Number & vbCrLf & "Description: " & Err.Description


End Sub

I found the code at:

http://forums.vb-world.net/showthrea...threadid=10634

It seeems to have converted my database to an Access 2000 one as I can no longer open it using Access 97


How should I compact a '97 db using ADO whist retaining '97 format?

[Jaguar]

Try this...

http://msdn.microsoft.com/library/ps...o/jros9h4j.htm

Here is what is talks about specifically about CompactDatabase.

http://msdn.microsoft.com/library/ps...o/jrmt4ujp.htm

You should be changing your provider settings to reflect the correct Jet Engine

 
Jet OLEDB:Engine Type   Indicates the version of the source database 
to open or the version of the new database to be created.
The OLE DB engine types that you can use are: 
1 for JET Version 1.0

2 for JET Version 1.1

3 for JET Version 2.x

4 for JET Version 3.x

5 for JET Version 4.x

If this is omitted for the destination database, 
it will default to 5. The value for the destination 
database cannot be a version prior to that of the source 
database.

[Mark Sreeves]

Thanks for your help Jaguar and Clunietp (Clunietp has helped me via email)

But...

I've been using mdac_typ.exe version 2.1.2.4202.3 which I believe is the latest one and that doesn't seem to support
"Provider=Microsoft.Jet.OLEDB.3.5; which is what I assume Access 97 databases to be.

Will this work if I use version 2.0.0.3002.15 which came with VB6?

Should I just "bite the bullet" and use "Provider=Microsoft.Jet.OLEDB.4.0 and distribute mdac
2.1.2.4202.3?

[Clunietp]

You could distribute MDAC 2.1 and use the OLEDB 4 provider for your data access, but that will be painfully slow.

Distribute MDAC 2.0 AND 2.1. (install 2.0 FIRST) The reason you need both is for the OLEDB 4 provider to compact the database, and the OLEDB 3.51 provider for your normal DB processing.

Or, you could just convert your database to JET 4 and use MDAC 2.1 or even MDAC 2.5.......


Tom