ACCESS database repair


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Thread: ACCESS database repair

  1. #1
    Join Date
    Apr 2006
    Posts
    4

    ACCESS database repair

    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.

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Feb 2004
    Location
    Sydney, Australia
    Posts
    498
    Not a bad idea to copy the database before Compact & Repair (FileCopy) - just in case!

  4. #4
    Join Date
    Jun 2006
    Posts
    168
    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?

  5. #5
    Join Date
    Jun 2006
    Posts
    168
    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

  6. #6
    Join Date
    Dec 2003
    Posts
    2,750
    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.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  7. #7
    Join Date
    Jun 2006
    Posts
    168
    Quote Originally Posted by 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.
    wow, you're right. I missed that.

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


    Visual Basic 6.0
    Microsoft Access 2003

  8. #8
    Join Date
    Jun 2006
    Posts
    168
    Quote Originally Posted by cupacabrah
    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?



    Code:
    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"
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  9. #9
    Join Date
    Jun 2006
    Posts
    168
    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


    Code:
    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
    -Jon


    Visual Basic 6.0
    Microsoft Access 2003

  10. #10
    Join Date
    Dec 2003
    Posts
    2,750
    Yes, the Engine Type should be 5 (Jet 4.0). An Engine type of 4 is for Jet 3.x databases.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Similar Threads

  1. how to access password protected database?
    By Sean in forum VB Classic
    Replies: 6
    Last Post: 07-23-2002, 08:04 PM
  2. Replies: 1
    Last Post: 05-27-2002, 10:16 AM
  3. Multi-User access for access database
    By Dave W in forum VB Classic
    Replies: 5
    Last Post: 10-26-2001, 11:29 AM
  4. Replies: 0
    Last Post: 12-11-2000, 02:32 PM
  5. Replies: 6
    Last Post: 08-21-2000, 07:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center