Methinks that the weak point is data validation. Users should not be able to write numbers to string fields, values less than (or greater than) x, and so on. That takes a lot of second guessing, and as I've always said, the more idiot proof you make something, the bigger of an idiot you will find (I know, not nice so I usually kept that sentiment to myself). Better validation might be something to work on in concert with the following:
Code:
Function CRC_Calc(CRC_Message As String) As String
'**modified from a post at https://www.tek-tips.com/viewthread.cfm?qid=1686217
'Calculates a 16 bit checksum of a String of any length.
Dim Polynomial16 As Long, Char_Text_DEC As Long
Dim X As Integer, Y As Integer
Dim CRC_value As Long, CRC_Text_Single As String
Dim LSB_CRC As Variant, LSB_Char As Variant, CRC_LO As Variant, CRC_Hi As Variant
Dim CRC_STRING As String
Y = 1
X = 0
CRC_value = 0
Polynomial16 = 33800 'Polynomial &H8408
For Y = 1 To Len(CRC_Message)
CRC_Text_Single = Mid$(CRC_Message, Y, 1)
Char_Text_DEC = Asc(CRC_Text_Single)
For X = 1 To 8
LSB_CRC = CRC_value And &H1
LSB_Char = Char_Text_DEC And &H1
If LSB_CRC = 1 And LSB_Char = 1 Or LSB_CRC = 0 And LSB_Char = 0 Then
CRC_value = Fix(CRC_value / 2)
Char_Text_DEC = Fix(Char_Text_DEC / 2)
ElseIf LSB_CRC = 0 And LSB_Char = 1 Or LSB_CRC = 1 And LSB_Char = 0 Then
CRC_value = Fix(CRC_value / 2)
Char_Text_DEC = Fix(Char_Text_DEC / 2)
CRC_value = Polynomial16 Xor CRC_value
Else
End If
Next X
Next Y
If Len(Hex(CRC_value)) = 4 Then
CRC_LO = Mid$(Hex(CRC_value), 3, 2)
CRC_Hi = Mid$(Hex(CRC_value), 1, 2)
ElseIf Len(Hex(CRC_value)) = 3 Then
CRC_STRING = "0" & Hex(CRC_value)
CRC_LO = Mid$(CRC_STRING, 3, 2)
CRC_Hi = Mid$(CRC_STRING, 1, 2)
ElseIf Len(Hex(CRC_value)) = 2 Then
CRC_STRING = "00" & Hex(CRC_value)
CRC_LO = Mid$(CRC_STRING, 3, 2)
CRC_Hi = Mid$(CRC_STRING, 1, 2)
ElseIf Len(Hex(CRC_value)) = 1 Then
CRC_STRING = "000" & Hex(CRC_value)
CRC_LO = Mid$(CRC_STRING, 3, 2)
CRC_Hi = Mid$(CRC_STRING, 1, 2)
ElseIf Len(Hex(CRC_value)) = 0 Then
CRC_LO = "00"
CRC_Hi = "00"
End If
CRC_Calc = Chr("&h" & CRC_LO) & Chr("&h" & CRC_Hi)
'CRC_Calc = Chr(CRC_value Mod 256) & Chr(CRC_value \ 256) 'ALTERNATIVE METHOD
End Function
You'd call the above as
Code:
Public Function getFieldsString() As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strConcat As String
Dim i As Integer
On Error GoTo errHandler
Set db = CurrentDb
For Each tdf In db.TableDefs
'' If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
'' Set rs = db.OpenRecordset(tdf.Name)
'' If Not (rs.BOF And rs.EOF) Then
'' Do Until rs.EOF
'' For i = 0 To rs.Fields.Count - 1
'' strConcat = strConcat & rs.Fields(i)
'' Next
'' 'Debug.Print strConcat 'enable if you want to see the string being passed
'' Debug.Print CRC_Calc(strConcat) & vbCrLf 'change to code you need to use checksum value; e.g. execute sql (or edit rs since it's already in use)
'' rs.MoveNext
'' Loop
'' End If
Debug.Print tdf.Name
End If
Next
exitHere:
On Error Resume Next
Set db = Nothing
Set rs = Nothing
errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Function
I would run this "as is" at first and examine the table names output. Examine the list for any table names you'd want to exclude and decide how to do that (name tests?). The first function won't be called at this point. Then uncomment the block with 2 single quotes to view the checksum print out to the immediate window. If that looks good, the code will need a test to compare stored checksum and handle the test, or to write the checksum - instead of just debug.print
I did not exclude 1st table field, which in a lot of cases may be the autonumber field, thinking it wouldn't matter at all. However, to avoid including the checksum field (which I don't have) you'd subtract 2, not 1 - provided the field is always last in the loop. If that doesn't seem safe, you could make sure each table had the same checksum field name and test for that name in the field loop before comparing/updating.
The first function would not compile for me until I typed several variables. Since I didn't know what they're meant to hold, some were typed as variants. Others were more obvious so I typed them according to my interpretation. It all seems to work for me.
HTH