ACCESS database repair

[ssk2k4]

Dear Forum members,

I am using ACCESS database (.mdb) in most of my VB applications. Sometimes I am facing a problem of getting that database corrupted. I need to repair that database using MS ACCESS Tools-Database Utility-Repair. This sometimes require to install MSACCESS on that machine. Instead I want to add a utility to repair ACCESS database in my application itself. So that if corrupted, using this utility, end user can repair the database on his own.

Pls. help me in this regard. If a code for this is available, it will be most welcomed.

Thanking you in advance.

[pclement]

See if the following helps:

http://www.freevbcode.com/ShowCode.Asp?ID=1024

[gupex]

Not a bad idea to copy the database before Compact & Repair (FileCopy) - just in case!

[chupacabra]

I've done a bunch of searching, and I tried the program above, but I need/prefer to use ADO, though, instead of DAO.

I tried this code:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''

Dim jro As New jro.JetEngine


jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dataSource & ";Jet OLEDBatabase Password=test", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\abbc2.mdb;Jet OLEDB:Engine Type=4;Jet OLEDBatabase Password=test"

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''

but I get error:
"Run-time error'-2147467259 (80004005)':
Cannot perform this operation; features in this version are not available in databases with older formats."



I have the reference to MS jet and replication objects library 2.6

what would cause this error?
or, is there a better way to do it?

Ideas?

[chupacabra]

I think I'll just use the code in the example, after I tune it to fit my needs.
sooo.... nevermind, I guess. Unless someone wants to volunteer an answer anyway

[pclement]

I'm not sure which version of the Access database you have but the source appears to be missing the Jet OLEDB:Engine Type parameter.

[chupacabra]

I'm not sure which version of the Access database you have but the source appears to be missing the Jet OLEDB:Engine Type parameter.

wow, you're right. I missed that.

Thanks, I'll plug that in, and see if that fixes the problem!

[chupacabra]

wow, you're right. I missed that.

Thanks, I'll plug that in, and see if that fixes the problem!

Nope, i get the same error.
I have Access 2003. Shouldn't this work with that?

Public Sub CompactRepairDB(dbFname As String, Optional dbPath As String)

    Dim jro As New jro.JetEngine
    Dim dataSource As String
    
    If dbPath = "" Then
        dbPath = App.path
    End If
    
    If (Right(dbPath, 1) = "\") Or (Right(dbPath, 1) = "/") Then
        dataSource = dbPath & dbFname
    Else
        dataSource = dbPath & "\" & dbFname
    End If
    
jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dataSource & ";Jet OLEDB:Engine Type=4;Jet OLEDB:Database Password=test", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\abbc2.mdb;Jet OLEDB:Engine Type=4;Jet OLEDB:Database Password=test"

[chupacabra]

I think it was bc there i was saying version 4, whereas it should be 5.

got working example from here -> http://visualbasic.ittoolbox.com/doc...ual-basic-2903

Public Function CompactDB(pFileName As String) As Boolean
'---------------------------------------------------------------------------
' PROCEDURE : CompactDB
' DESCRIPTION :
' RETURN VALUE: true or false depending wether there were errors or not
' NOTE :
' Add "Microsoft Jet and Replication Objects 2.x Library" to
' the references of your project
' EXAMPLE :
'---------------------------------------------------------------------------

    On Error GoTo ErrH
    Dim CONN As New jro.JetEngine
    Dim ConnstringSorg As String, ConnstringDest As String
    
    ' Ensure file is not read only
    SetAttr pFileName, vbNormal
    ConnstringSorg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    pFileName & ";User ID=;Password=;"
    ConnstringDest = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
    App.path & "\Temp.mdb" & ";Jet OLEDB:Engine Type=5;"
    
    Screen.MousePointer = vbHourglass
    CONN.CompactDatabase ConnstringSorg, ConnstringDest
    Screen.MousePointer = vbDefault
    
    'Copia il file compattato.
    Kill pFileName
    FileCopy App.path & "\Temp.mdb", pFileName
    Kill App.path & "\Temp.mdb"
    
    Set CONN = Nothing
    CompactDB = True
    Exit Function
ErrH:
    Screen.MousePointer = vbDefault
    Debug.Print Err.Description
End Function

[pclement]

Yes, the Engine Type should be 5 (Jet 4.0). An Engine type of 4 is for Jet 3.x databases.