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/showthread.php?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/psdk/msjro/jros9h4j.htm

Here is what is talks about specifically about CompactDatabase.

http://msdn.microsoft.com/library/psdk/msjro/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?

:confused::confused::confused::confused::confused::confused::confused::confused::confused::confused: :confused:

[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