Dear Friends Is there any easy way (query) to find out corrupted (Chinese) records? I have a huge database on network using by lot of users, I daily backup my database compact and repair. Sometimes users complain for slow applicaiton processing. Some of records got corrupted and become Chinese due to network problem or some other causes and records filtering etc not work properly. I have to scroll down manually in the main table through all the records and check for corrupted records, I try to run some queries to get my data, but still its long and hard work to get the corrupted records. Is there any easy way to find out the corrupted records using query? Thanks.
I think you first issue is to find out why records are being corrupted. Is this a split database? are you or any of your users using a wireless connection?
Yes it is a split database on local network, all the users are connected through LAN to the be on a network drive from their fe application copies on their desktop, nobody is using a wireless connection.
Should I reduce the volume of the database by deleting the expired/old records from the database? (i.e expired contracts before 31-Dec-2005)
Characters turning to Chinese is a known bug in Access. Something to do with formatting in Notes fields I think but I forget exactly and can't locate a reference just now.
>> Is there any easy way to find out the corrupted records using query? <<
Not really with a query. What I do to find/eliminate the problem is do a FIELD by FIELD copy/append FROM the suspected corrupt table to a NEWLY CREATED table with and IDENTICLE structure .. make sure the destination table is FRESH and any autonumber fields are reset to 1, also, if you use the AutoCorrect feature, I would suggest that you turn that option off before proceeding. I would even suggest leaving it off as that feature has been blamed to cause corruption in the files it is set to true on.
I have used this method several times and have found the MEMO fields are the most troublesome for record corruption.... The code will (*should*)crash out on the corrupt record (despite the error handler), once it crashes, look at the immediate window to see which record you are on, then modify code (clngStartAtPrimaryKey) to select all the RECORDS AFTER the corrupt one. NOTE: I USE AN AUTONUMBER (incrementing) IN ALL MY TABLES, so the code is written with that assumption. (NOTE: the code contains the values from last time I used it ... so modify as you need too!)
Code:
Public Sub RestoreTable()
Dim rstCorrupt As New ADODB.Recordset
Dim rstNew As New ADODB.Recordset
Dim fld As ADODB.Field
Const cstrCorruptTableName = "tblDowntimeIncidents" 'Suspect table
Const cstrPrimaryKeyField = "DowntimeID" 'ALL my PRIMARY KEYs are AutoNumber fields
Const clngStartAtPrimaryKey = 101138 'Start at 0, then work your way up
Const cstrNewTableName = "tblDowntimeIncidents_Restored" 'Name of the destination table.
'Open the Corrupt table using the information above to avoid the corrupt record
rstCorrupt.Open "SELECT * FROM " & cstrCorruptTableName & _
" WHERE " & cstrPrimaryKeyField & ">=" & clngStartAtPrimaryKey & _
" ORDER BY " & cstrPrimaryKeyField _
, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
'Open the DESTINATION table
rstNew.Open cstrNewTableName, CurrentProject.Connection, adOpenDynamic, adLockOptimistic, adCmdTableDirect
On Error GoTo Error_Handler
Do Until rstCorrupt.EOF
If Not rstNew.EOF Then 'Not neccessary, but I like to add records on the EOF cursor location
rstNew.MoveLast
rstNew.MoveNext
End If
Debug.Print rstCorrupt.Fields(0).Name & "=" & rstCorrupt.Fields(0) 'Field 0 is ALWAYS my primary
rstNew.AddNew
For Each fld In rstCorrupt.Fields
rstNew.Fields(fld.Name) = rstCorrupt.Fields(fld.Name).value
Next fld
rstNew.Update
rstCorrupt.MoveNext
Loop
Error_Handler:
On Error Resume Next
rstCorrupt.Close
rstNew.Close
Set rstCorrupt = Nothing
Set rstNew = Nothing
End Sub
If you don't like the code base method, to do the appending, I have also used, and recommended the following steps.
1. Turn off 'Name AutoCorrect' (Tools | Options | Look on the 'General Tab')
2. Delete any relationships to the table with the corrupt record (You may want to write them down so you can re-create them.
3. Create a NEW table that has an IDENTICAL structure to the one that has the corrupt record.
4. NOW ... Write an APPEND QUERY that will APPEND the records from the corrupt table to the new table with criteria set up in such a way to skip the corrupt record. Here is an example in SQL where FacilityID number 1 is the corrupt record:
INSERT INTO tblFacility_NotCorrupt
SELECT tblFacility.*
FROM tblFacility
WHERE tblFacility.FacilityID<>1;
OR like this if you can not see the ID due to the corruption...this one will only allow ID's between 1 and 100 or ID's greater than 105, thus effectively 'skipping' 101-104...
INSERT INTO tblFacility_NotCorrupt
SELECT tblFacility.*
FROM tblFacility
WHERE (tblFacility.FacilityID>0 And tblFacility.FacilityID<=100) OR (tblFacility.FacilityID>=105);
5. Try to delete your currupt table
6. Rename your NEW table to the old table name
7. Rebuild your relationships.
8. Turn On Name AutoCorrect ... if you want to ... there are several reasons to NOT turn it back on though.
IF you CAN NOT delete the corrupt table as stated in #5, then do this between step 5 and 6:
1. CREATE a new BLANK database ...
2. Import ALL of your objects from the corrupt database with the EXCEPTION of the corrupt table (File | Import | Get External Data ...) ... be sure to click on the Advanced button and import the relationships and anything else that is appropriate.
3. Recompile your NEW application (if you have any code in it).
4. Delete the corrupt database and rename your new one to match the old.
To find record with "strange/illegal" chars you could try a function to search any suspected fields IF it contain a known value or characters which isen't normal. Something like this:
Code:
Function FindCorrupt(AnyStr As String) As Boolean
' Purpose Find illegal char's in a field
' Limitation: Assume the illegal chr is first in the string.
' Call function from a query by creating a field
' Field: FindCorrupt([SuspectedField]) and set the where-clause to False
Dim i As Integer
Dim j As Boolean
AnyStr = Replace(AnyStr, " ", "")
For i = 48 To 122 ' chr(48) to chr(122) or 0 -> z
If InStr(1, AnyStr, Chr(i), 1) > 0 Then
FindCorrupt = True 'field/Record ok
Exit Function
Else: j = False
End If
Next i
FindCorrupt = j
End Function
It should work in principal, provided that the dbEngine can interrogate the corrupted records. You could revers it by setting the where-clause to TRUE and get records that dosen't contain any illegal characters.
Haven't testet on corrupted fields (haven't yet come across one) , so test it on a copy first.
But this dosen't cure your fundemental corruption issue, just find the current ones.
These are useful information. I should strictly adhere to these, usually I do development on connected live data while all others users are using the be from their own copies.
But I think that should not be the problem because the front ends on their (users) machines are compiled and in accde format, moreover I don't have any memo field in the database.
>>
OR like this if you can not see the ID due to the corruption...this one will only allow ID's between 1 and 100 or ID's greater than 105, thus effectively 'skipping' 101-104
...
.......
.........
1. CREATE a new BLANK database ...
2. Import ALL of your objects from the corrupt database with the EXCEPTION of the corrupt table (File | Import | Get External Data ...) ... be sure to click on the Advanced button and import the relationships and anything else that is appropriate.
3. Recompile your NEW application (if you have any code in it).
4. Delete the corrupt database and rename your new one to match the old.
Thanks for your comprehensive detail and method as well as a code. I will try the code and will get through.
Last time one of record with autonumber filed got corrupted. The record was got stuck for deletion, updating etc.
I create a new table with the same structure of the corrupted table,delete the relationship and bla,bla..aauuhhh its was a big HARD and struggling work.
I rename the newly created table as you said and create the relation back.
' Call function from a query by creating a field
' Field: FindCorrupt([SuspectedField]) and set the where-clause to False
Code:
Dim i As Integer
Dim j As Boolean
AnyStr = Replace(AnyStr, " ", "")
For i = 48 To 122 ' chr(48) to chr(122) or 0 -> z
If InStr(1, AnyStr, Chr(i), 1) > 0 Then
FindCorrupt = True 'field/Record ok
Exit Function
Else: j = False
End If
Next i
FindCorrupt = j
End Function
It should work in principal, provided that the dbEngine can interrogate the corrupted records. You could revers it by setting the where-clause to TRUE and get records that dosen't contain any illegal characters.
I tried this code its look meaningful but I got an error message telling that "The Data type mismatch"
My database has some corrupted records in some fields and they are Chinese. I create a query and use the function:
Field: FindCorrupt([SuspectedField] - on the corrupted field, but it gives datatype mismatch error.
I then try on another field but it gives just -1 results in that field for all the records. when I try to give the 0 criteria its give the same datatype mismatch in the criteria results.
My table has 2,10,101(plus) records and its too much difficult to scroll through all the records until find the corrupted record.
, so test it on a copy first.
