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:
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
Code:
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
Cant 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 SubThe 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