Member Avatar for cpopham

Here is one that is stumping me.

I have an Access 2000 database backend. It got corrupted. It is a large database. I compacted and repaired it and a record came up corrupted. There was a compact error table created that has a binary column in it. The binary column points to a bookmark that tells which row was corrupted. I am trying use vb .net to retrieve that row using the binary column to show me which record is corrupted, however, I cannot get it to work right. I have found VBA for Access code that does something similar, but I need it in VB .net. Here is the VBA code that I have:

Sub main() 
    On Error GoTo ErrorHandler 
    Dim db As DAO.Database, vBookMark As Variant, _ 
      rsMSysCompactError As DAO.Recordset, strErrorTable As String, _ 
      rsErrorTable As DAO.Recordset, fldErrorField As DAO.Field, _ 
      strSQLSEL As String, strColumnValue As Variant, _ 
      qdTemp As QueryDef, strSQLINS As String, intLoop As Integer, _ 
      lngTableNameLength As Long, _ 
      colErrorCollection As New Collection, intErrorCount As Integer 

    Set db = CurrentDb() 
    ' Walk through the MSysCompactError table to find rows that reflect 
    ' lost data values. 
    Set rsMSysCompactError = db.OpenRecordset("SELECT * FROM MSysCompactError WHERE ErrorRecId IS NOT NULL", dbOpenDynaset) 
    intErrorCount = 0 
    While Not rsMSysCompactError.EOF 
        ' Get the name of the table that had column data missing. 
        strErrorTable = rsMSysCompactError!ErrorTable 
        ' Check to see that tablename is not greater than 48 characters 
        ' to stay under 64 character tablename limit. 
        lngTableNameLength = Len(strErrorTable) 
        If lngTableNameLength > 48 Then 
            strErrorTable = Mid(strErrorTable, 1, 48) 
            ' See if this truncated table name already exists. 
            On Error Resume Next 
            colErrorCollection.Add strErrorTable, strErrorTable 
            ' If this already exists in the collection, then there is a 
            ' duplicate table name. 
            If Err = 457 Then 
                ' Truncate one more digit to append on the intErrorCount 
                ' number to eliminate the duplicate table name. 
                strErrorTable = Mid(strErrorTable, 1, 47) 
                strErrorTable = strErrorTable & Mid((Str(intErrorCount)), 2, 1) 
                intErrorCount = (intErrorCount + 1) 
            End If 
        End If 
         
        ' Get the bookmark value of the row that had lost column data. 
        vBookMark = rsMSysCompactError!ErrorRecId 
        ' Open table that has lost column data. 
        Set rsErrorTable = db.OpenRecordset(strErrorTable, dbOpenTable, dbReadOnly) 
        ' Move to row that has lost column data. 
        rsErrorTable.Bookmark = vBookMark 
        ' Start to build SQL string to call up in a table window. 
        strSQLSEL = "SELECT * INTO MSysCompactError" & strErrorTable & " FROM " & strErrorTable & " WHERE " 
        strSQLINS = "INSERT INTO MSysCompactError" & strErrorTable & " SELECT * FROM " & strErrorTable & " WHERE " 
        intLoop = 0 
        For Each fldErrorField In rsErrorTable.Fields 
            strColumnValue = fldErrorField.Value 
            ' Logic to build predicate based on various data types. 
            If Not IsNull(strColumnValue) Then 
                ' Can't use ordinal as no guarantee of first column 
                ' being zero. 
                ' Check to see if this is the first column or not to 
                ' build SQL statement. 
                If intLoop = 0 Then 
                    If fldErrorField.Type = dbDate Then 
                        strSQLSEL = strSQLSEL & "[" & fldErrorField.Name & "] = " & "#" & strColumnValue & "#" 
                        strSQLINS = strSQLINS & "[" & fldErrorField.Name & "] = " & "#" & strColumnValue & "#" 
                    Else 
                        If fldErrorField.Type = dbText Or fldErrorField.Type = dbChar Or fldErrorField.Type = dbMemo Then 
                            strSQLSEL = strSQLSEL & "[" & fldErrorField.Name & "] = " & "'" & strColumnValue & "'" 
                            strSQLINS = strSQLINS & "[" & fldErrorField.Name & "] = " & "'" & strColumnValue & "'" 
                        Else 
                            strSQLSEL = strSQLSEL & "[" & fldErrorField.Name & "] = " & strColumnValue 
                            strSQLINS = strSQLINS & "[" & fldErrorField.Name & "] = " & strColumnValue 
                        End If 
                    End If 
                Else 
                    If fldErrorField.Type = dbDate Then 
                        strSQLSEL = strSQLSEL & " AND " & "[" & fldErrorField.Name & "] = " & "#" & strColumnValue & "#" 
                        strSQLINS = strSQLINS & " AND " & "[" & fldErrorField.Name & "] = " & "#" & strColumnValue & "#" 
                    Else 
                        If fldErrorField.Type = dbText Or fldErrorField.Type = dbChar Or fldErrorField.Type = dbMemo Then 
                            strSQLSEL = strSQLSEL & " AND " & "[" & fldErrorField.Name & "] = " & "'" & strColumnValue & "'" 
                            strSQLINS = strSQLINS & " AND " & "[" & fldErrorField.Name & "] = " & "'" & strColumnValue & "'" 
                        Else 
                            strSQLSEL = strSQLSEL & " AND " & "[" & fldErrorField.Name & "] = " & strColumnValue 
                            strSQLINS = strSQLINS & " AND " & "[" & fldErrorField.Name & "] = " & strColumnValue 
                        End If 
                    End If 
                End If 
            End If 
            intLoop = (intLoop + 1) 
            ' QJet limitation for maximum conditions is reached. 
            If intLoop = 39 Then 
                Exit For 
            End If 
        Next fldErrorField 
        On Error Resume Next 
        ' Create error table if it does not exist. 
        db.Execute strSQLSEL, dbFailOnError 
        If Err = 3010 Then 
            On Error GoTo ErrorHandler 
            ' Add rows to error table if it already exists. 
            db.Execute strSQLINS, dbFailOnError 
        End If 
        rsErrorTable.Close 
        rsMSysCompactError.MoveNext 
    Wend 
    rsMSysCompactError.Close 
    MsgBox "Done!" 
    Exit Sub 
ErrorHandler: 
    MsgBox "An error has occurred " & Err & " " & Error 
    Resume Next 
End Sub

Any ideas?
Thanks, Chester

  • 6 Contributors
  • 5 Replies
  • 652 Views
  • 8 Years Discussion Span
  • Latest Post Latest Post by aidan29

Dani AI

Generated

Brief answer to : the binary ErrorRecId stored in MSysCompactError is a provider-specific bookmark — it isn't usable through plain OleDb/DataTable APIs. The most reliable way from VB.NET is to call the same recordset/bookmark API that the VBA uses: either DAO or an ADODB.Recordset via COM interop. That lets you assign the binary bookmark back to the table recordset and read that row directly.

Add a COM reference (DAO for Access 2000 — e.g. Microsoft DAO 3.6 — or ADODB if preferred), open the MDB via the engine, read MSysCompactError.ErrorRecId (as an Object/Variant) and then open the affected table and set rs.Bookmark = vBookmark. A minimal VB.NET flow (after adding the COM reference) looks like:

' Add COM reference to Microsoft DAO 3.6 and Import DAO
Dim engine As New DAO.DBEngine()
Dim db As DAO.Database = engine.OpenDatabase(mdbPath)
Dim rsErr As DAO.Recordset = db.OpenRecordset("SELECT ErrorTable, ErrorRecId FROM MSysCompactError WHERE ErrorRecId IS NOT NULL", DAO.RecordsetTypeEnum.dbOpenDynaset)

While Not rsErr.EOF
  Dim vBookmark As Object = rsErr.Fields("ErrorRecId").Value
  Dim tbl As String = CStr(rsErr.Fields("ErrorTable").Value)
  Dim rsData As DAO.Recordset = db.OpenRecordset(tbl, DAO.RecordsetTypeEnum.dbOpenTable, DAO.RecordsetOptionEnum.dbReadOnly)
  Try
    rsData.Bookmark = vBookmark
    ' read fields from rsData here
  Catch ex As Exception
    ' bookmark assignment failed — handle fallback
  End Try
  rsData.Close()
  rsErr.MoveNext()
End While
rsErr.Close()
db.Close()

Important cautions and fallbacks: always work on a copy of the MDB. DAO 3.6 and Jet are 32‑bit COM — build the .NET app as x86 or the COM library won't load. If bookmark assignment fails (row gone or highly damaged), fall back to the technique in the original VBA: open the table and reconstruct a WHERE clause from non-null field values to try to find the closest match. If COM automation is not possible, ADODB with a client cursor can sometimes provide bookmarks, or try importing the table into a new blank database (or use a reputable recovery tool) as suggested by and others.

Member Avatar for fyz

Hi,

There is a tool called Advanced Access Repair. I have used it to repair many corrupt Access MDB files on my damaged disks successfully.

Hope this helps.

Alan

Member Avatar for Steal32

In case of database corruption you can import your corrupted databse to a new blank database or you can repair it using compact and repair utility provided by MS office. This tool will repair your damaged database. In case its not able to repair database then you search for access repair utility.

Member Avatar for Davidpoul

If your access database get corrupted then try Stellar Phoenix Access Recovery Software. I used it to repair my corrupt Access MDB files and it successfully repair and recover all my data.

Member Avatar for aidan29

First you can try with this step:
Hide Compact and repair the current Access file
If you are compacting a shared Microsoft Access database that is located on a server or shared folder, make sure that no one else has it open.
On the Tools menu, point to Database Utilities, and then click Compact and Repair Database/Project.
Second step: you should pay for AccessFIX, personally i prefer this tool because is great and it is able to show me the results before purchase it.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.