Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2021
    Location
    Missouri Ozarks, USA
    Posts
    19

    Module to validate table records using checksum aggregates

    I'm looking for a way to automate a process for validating the values in Access tables by using some standardized aggregate method and I wanted to throw some ideas out there...




    In sum, I want to develop a validation process so that every table in every database could be represented by a single integer number. If that number was constant, the contents of the table would be validated as unchanged.


    The basic idea would be to create a module containing a set of functions according to the datatype of each field. Below are some possible methods for each datatype so it could be reduced to a single integer and the sum of all integers totaled into a checksum_aggregate()...


    Datatype Aggregate function
    ------------ -------------------------
    integer sum of integers
    double Cint(sum of doubles)
    text sum of (len(text))
    date sum of CInt(mm+dd+yyyy)


    The checksum_aggregate integer could be stored as "initial checksum" and "final checksum" each time database is opened along with the database version (name of the database) and date that the database file was opened.


    I wanted to post this to see if others had tried similar validation approaches or if there were entirely different ways to do this.

    Thanks in advance,

    CR

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What's the purpose or point? You can change the data in a text field without changing its length, therefore no change to the sum. That's probably only one example of how data could be changed without changing any sums. Then there is the fact that a sum could change but you'd have no way of knowing what's different unless you had a basis for comparison. Maintaining table field history is a way to make that comparison without tedious programming for sums but it's not something I've ever needed to do.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with micron. I suggest you describe what it is you are trying to validate. Describing is an interesting process -sort of forces you to the details and - from experience- that is where you start to understand the requirement. When you can describe "it" in simple terms and can explain that to others - you'll probably identify a variety of feasible approaches.

  4. #4
    Join Date
    Jan 2021
    Location
    Missouri Ozarks, USA
    Posts
    19
    I apologize for being really vague...

    I need a way to validate whether or not the content of a table object has been modified between time T1 and time T2. Modified date is not sufficient. I need a true/false response and do not need to answer what has been modified.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I understood your idea, just not the point of it for the reasons I gave. Knowing something was changed without knowing what the change was seems pointless - especially if the change can be made without altering the checksum. Nor would it tell you who made a change, and if at the table level, it won't even tell you which field was changed, let alone which record. I still don't see the point.
    Last edited by Micron; 04-15-2021 at 09:43 AM. Reason: spelin and gramur
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Join Date
    Jan 2021
    Location
    Missouri Ozarks, USA
    Posts
    19
    The issue of whether or not the content of a table has been modified comes up when I am trying to track down data-entry and edit errors during our transcription from paper to Access database. The first thing I need to rule out is whether or not the table has been changed at all. That would help me narrow down which tables I need to look at.

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Again, I think micron has highlighted the shortcoming(s) of that approach. If the modified date is insufficient, and length of text/string could remain the same even if chars were replaced, you might consider some sort of encryption. You could verify/decrypt the "encrypted string" to identify any change or not. Untested, but seems logically consistent.

    Just saw your latest post.

    How exactly does the data entry occur? Does new data get put into a collector file/table before being applied to your "production" data? Seems that might be useful in reviewing the "entered data".
    -You could check which table specific data is destined for
    -you could validate some spelling/dates or values
    -you could do some analysis before applying the"new" data to your existing data.

    What is the criticality of the data entry? Is there some likelihood of sabotage or intended malicious damage with consequences?

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    maybe post at 10:07 will get you started
    https://www.tek-tips.com/viewthread.cfm?qid=1686217
    I guess you'd calculate and store the checksum in a field in each record of each table. A query that calls a function that iterates over the fields in each record could write this value per record, excluding autonumber and checksum fields. The same function could verify the current checksum via a recordset. This code could, if the checksum field is Null, simply write the value. If not, then perform a comparison. This way the same function could be used by the query or anyone testing for changes. If using the ASCII values as suggested in the link, it will spot the difference between A and a or . versus ; If you can live with that.

    I've never done this and it sounds interesting but I think I get the reason for the request now.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @canyonraven, I have written something that will output a checksum value based on a string concatenated from all the fields in a record. I had to modify the checksum code at the posted link as it doesn't play well when using Option Explicit. Since there is no documentation there, I had to guess but it seems to work. If you'd like, I can post it but you'll have to tweak it if you want to incorporate the test that decides to write or compare.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Join Date
    Jan 2021
    Location
    Missouri Ozarks, USA
    Posts
    19
    Thank you for the links and ideas. Yes, Micron, please post the checksum code based on the string concatenation if possible. That would be helpful.

    Re. your question, Orange

    "What is the criticality of the data entry? Is there some likelihood of sabotage or intended malicious damage with consequences?"

    I am working with several production databases that store long-term ecological and environmental monitoring data. Our applications are pretty "battle-tested" but we have new interns each year who find new and creative ways to break them
    Maybe I just need to write better applications. But when I have to do fixes, the first thing I need to do is rule-out the tables that are unchanged, whatever the modification date says.

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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
    Last edited by Micron; 04-15-2021 at 02:40 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Join Date
    Jan 2021
    Location
    Missouri Ozarks, USA
    Posts
    19
    Thank you!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Validate data entered against another table
    By russdwright in forum Forms
    Replies: 7
    Last Post: 09-27-2017, 11:48 AM
  2. Queries, Counts, Sums, Aggregates, Pain
    By Levonas in forum Access
    Replies: 11
    Last Post: 02-04-2015, 08:04 PM
  3. Replies: 10
    Last Post: 12-05-2014, 11:13 PM
  4. Match TEXTBOX value with TABLE and then validate?
    By taimysho0 in forum Programming
    Replies: 2
    Last Post: 11-22-2011, 11:25 AM
  5. Module to be run by macro to validate
    By GraemeG in forum Modules
    Replies: 4
    Last Post: 02-26-2011, 06:26 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums