Access MDB backend compact errors

[cpopham]

I have an Access MDB file and I have compacted an repaired it, but it has errors. It has created a MSysCompactError that has the errors in it. There is a binary column in the error table that has a bookmark to the offending row. I have found a module an access module that is suppose to give you the rows that had problems. I am trying to build something similar in vb .net to get the corrupt rows. This is the module code for access:

 

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 

 

The part I am interested in is the ErrorRecid which is the binary bookmark. How can I get the information from that column to see which row had the problem.

 

I have most of it worked out, but that one binary field is giving me fits and I looked everywhere and cannot find.

 

Thank you for the help.

 

Chester