Useing the Microsoft JRO object

[Gary.Lowe]

Dear All

Don't know if anyone else has come accross this problem but it seems really wierd.

usually when I am developing database applications I give the user the choice to compact and backup the database useing the DBEngine.CompactDatabase method which works fine.

I am currently developing a new databaese app useing ADO, so I thought I would use the new Microsoft JRO object to compact. Something very strange has happened.

The database is Access 97
The app is developed in VB6 SP3
I am useing the mdac 2.5

This is the code I used to compact it

Public Function fn_DBBackup(ByVal sSource As String, _
                         ByVal sBackUp As String) As Boolean
 
    Dim objBU As New JRO.JetEngine
    Dim Response
    
    Response = MsgBox("Are you sure you can repair the database?" & vbCrLf _
            & "You cannot repair the database if it is in use!", vbYesNo, "DB Repair")
    

    If Response = vbYes Then
            If bFileExists(sBackUp) Then
                Kill sBackUp
            End If
            
            
            If bFileExists(sSource) Then
                objBU.CompactDatabase "Data Source=" & sSource, _
                                        "Data Source=" & sBackUp
            Else
                MsgBox "The source database cannot be found" & vbCrLf _
                & "Please check the source information!", vbOKOnly, "DB Source Error!"
                fn_DBBackup = False
                Exit Function
            End If


            
            If bFileExists(sBackUp) Then
                Kill sSource
            End If
            
            objBU.CompactDatabase "Data Source=" & sBackUp, _
                                   "Data Source=" & sSource

           
           fn_DBBackup = True
    Else
            fn_DBBackup = False
    End If

This works fine, until I try to open the database in Access97. It then display's a message claiming the database is in an unrecognised format.

I thought maybe due to the JRO object being quite a new thing it some how upgraded the version to Access 2000.

This seems not to be the case as when I tried to open it in Access 2000, it says the database was an earlier version.

Does anbody know what is wrong?

I can still open the database in VB and process.

Thanks

[Gary.Lowe]

Sorry but I seem to have some sort of spelling problem with the word 'using'.

I can't seem to help putting an 'e' in it.

[E.Kimball]

Try this it should work.


Dim JRO as JRO.JetEngine
Set JRO = New JRO.JetEngine


Public Sub Repair

JRO.CompactDatabase _
"Provider = Microsoft.Jet.OLEDB.4.0;"_
&"Data Source =" & DataBase_Name, _
"Provider = Microsoft.Jet.OLEDB.4.0;"_
&"Data Source = " & NEW_DATABASE_NAME _
& ";Jet OLEDB:Engine Type=4"


end sub


NOTE : Use Engine Type 4 for Access 97 and
Engine Type 5 for Access 2000