How I do to Compact and repair a file

[joseprez]

How I do to Compact and repair a file made in Access2000, from an application in VB6

 

[CCLINT]

Use JRO.

Add a reference to the "Microsoft Jet and Repication Objects Library"

Dim jro as new JRO.JetEngine

jroEngine.CompactDatabase "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & SourceNameAndPath & ";Jet OLEDB:Engine Type=5", "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & DestNameAndPath & ";Jet OLEDB:Engine Type=5"

DestNameAndPath has to be different than SourceNameAndPath

You can use a temp name for DestNameAndPath, and after compacting, delete the SourceNameAndPath and Rename the temp mdb as SourceNameAndPath using "Name" [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[datawise]

Sorry to be a pain, but I am still getting the same error. I even cut and pasted your code in with no success.

Here's the code

Private Sub Command1_Click()
Dim je As New JRO.JetEngine, srcdb As String, string1 As String, string2 As String, strfrom As String, strto As String, strKillfile As String
srcdb = Text1
If Option97 = True Then
    string1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & srcdb & ";Jet OLEDB:Engine Type=4"
    string2 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Left(srcdb, Len(srcdb) - 4) & "2k.mdb;Jet OLEDB:Engine Type=5"
    strto = "2K"
    strfrom = "97"
    strKillfile = Left(srcdb, Len(srcdb) - 4) & "2k.mdb"
    Label1 = "Converting from " & strfrom & " format to " & strto
    If Dir(strKillfile) <> &quot;&quot; Then Kill (strKillfile)
    je.CompactDatabase string1, string2
    Label1 = &quot;Converted to &quot; & Left(srcdb, Len(srcdb) - 4) & string3 & &quot;.mdb&quot;
Else
    string1 = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & srcdb & &quot;;Jet OLEDB:Engine Type=5&quot;
    string2 = &quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & Left(srcdb, Len(srcdb) - 4) & &quot;97.mdb;Jet OLEDB:Engine Type=4&quot;
    strfrom = &quot;2K&quot;
    strto = &quot;97&quot;
    strKillfile = Left(srcdb, Len(srcdb) - 6) & &quot;97.mdb&quot;
    'je.CompactDatabase string1, string2
    je.CompactDatabase &quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & srcdb & &quot;;Jet OLEDB:Engine Type=5&quot;, &quot;PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=&quot; & Left(srcdb, Len(srcdb) - 4) & &quot;97.mdb&quot; & &quot;;Jet OLEDB:Engine Type=4&quot;
    'dbE.CompactDatabase srcdb, strKillfile, dbLangGeneral, dbVersion30
    Label1 = &quot;Converted to &quot; & Left(srcdb, Len(srcdb) - 4) & string3 & &quot;.mdb&quot;

End If
End Sub

The error comes on the je.compactdatabase method. Here's the error:

 

[CCLINT]

Make sure you have the following installed:

VB6 SP5 with MDAC 2.5

What happens when you use the DBEngine.Compact method?
(make sure you are updated to MDAC 2.5 to make sure you have the correct version of JRO and DAO) [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[CCLINT]

From:
>Jet OLEDB:Engine Type=5"

To:
>Jet OLEDB:Engine Type=4"

Will not work like that.

Why do you need to convert back?

Maybe better would be to use Jet4 with ADO in your project (you can still access Jet 3 databases) and then import the tables into a Jet3 MDB:

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test\db2000.MDB;Jet OLEDB:Engine Type=5;"

conn.Execute "INSERT INTO TheTable(Field1) IN 'C:\test\db1997.MDB' [Provider=Microsoft.Jet.OLEDB.4.0] " _
& "SELECT Field1 FROM PersTheTable, adCmdText + adExecuteNoRecords

The above selects records from a 2000 mdb and puts them into a 97 Mdb.

Then you can use JRO compact method to create a new 2000 mdb from the 97 mdb when needed to transfer back. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!