Binary_checksum

[YvesX54]

Hello ,

is there something like a binary_checksum function in Access (2003). (MSSQL
aggregate func)

I'm trying to create some sort of synchronization between Access & another
DBMS and I want to know if something on a particular row has really changed
(I cannot use a 'Modified' date or a Timestamp field)

A inline-query and (very) fast function that concatenates all fieldvalues of a row and then creates a key would also be a consideration, altough I think
speed is going to be an issue.

Regards,
Yves.

 

[WayneRyan]

Yves,

Interesting to use a CheckSum. Seems valid, but what does the CheckSum
signify? That only some "appropriate" form accessed the data? That noone
directly accessed the table(s)?

No thoughts along those lines, but do have some on comparing data.

Detect Inserts:

Select A.* 
From   TableOther As A Left Join TableMain As B On
       A.SomePrimaryKey = B.SomePrimaryKey
Where  B.SomePrimaryKey Is NUll

Detect Deletes:

Select A.* 
From   TableMain As A Left Join TableOther As B On
       A.SomePrimaryKey = B.SomePrimaryKey
Where  B.SomePrimaryKey Is NUll

Detect Updates:

Select A.* 
From   TableMain As A Left Join TableOther As B On
       A.SomePrimaryKey = B.SomePrimaryKey
Where  A.Field1 <> B.Field1 And
       A.Field2 <> B.Field2 And
       A.Field3 <> B.Field3

You can then change them into Insert/Delete/Update queries. If you have
a lot of tables, you can use the TableDefs collection to get the names,
and write the SQL with some VBA code.

If the tables are small, you might consider using recordsets. Then you
can just crawl through the records and compare without even knowing the
field's names.

I'd vote for the queries though.

hth,
Wayne

 

[The_Doc_Man]

As a teacher of Windows security for System Administrators, I understand why you might wish to include checksums. Your concern about them is warranted because exchanging data from disparate systems usually involves operations that now and then can drop a bit or two. But you need to think it through. Your problems that will slow you to a crawl will be (1) data format conversion and (2) the inherent slowness of VBA. I'll take them in order.

Suppose that you have a record coming in that uses these fields:

A: Long (integer)
B: Text (max 12 chars)
C: Double (real)
D: Boolean or Yes/No
E: Date/Time
F: Memo
G: Currency

OK, so it's a mish-mash record and a bit contrived. But it will help illustrate my point. How are you going to compute a checksum of this? To split up a Long, you need something that will mask out bytes and rotate the remainder so you can see all four bytes as individual entities. To handle the Text and the Memo fields, you can use the MID function to grab one byte at a time, but how big will that memo field get? The Yes/No field is either 0 or -1 so that's no sweat. But...

The currency, date, and double fields are in formats that are almost intractable if you want to break them up into bytes. You need something that compiled languages call a "cast" - and VBA doesn't have such a thing. To do checksums you need to treat the bytes of a DOUBLE, the bytes of a DATE (in binary?), the bytes of a CURRENCY. And you cannot guarantee yourself that you would be able to fit the result of your conversion into a LONG. Particularly since Currency is longer than LONG and is scaled on top of that! So you have a non-trivial problem in getting the data to do the checksum that you want.

Access won't give it to you in the "right" format, either, because - to be simple-minded about it - Access is too stupid. You are asking Access to break out of its data presentation paradigm. Access is DESIGNED to present things in their "true" format, not as a disheveled lump of bytes. Hell, I'm not sure I could make ORACLE do this! So if ORACLE won't do it, you know that Access won't.

Now, let's take the other viewpoint. Execution speed bothered you - as well it should. VBA is semi-compiled to a form of pseudo-code. It is NOT a fully compiled language. That means, among other things, that you will be S L O W for anything involving byte mucking. And to do the right kind of mucking will take patience as well as fortitude. Access just doesn't have the right functions as far as I can tell.

The way I see it, you have two possible solutions. I don't know you well enough to know what resources you have, so I won't make assumptions.

Solution A: Requires that you have a VB compiler, which DOES produce machine code, or the Assembler, which DOES produce machine code. Make a callable subroutine that resides in a .DLL file. Give the routine entry points based on the data type of the thing you are passing in. Find the Help Files entry on calling external subroutines and define an interface. Then pass in data fields and your checksum variable. Have your compiled subroutine grab the input bytes one at a time and generate your checksum. BUT that wouldn't necessarily work for the other system unless it, too, has the ability to call your checksum routines. Which could be a real problem.

Solution B: Import the fields as text in a temporary table. Using the MID function on each text-oriented field, one byte at a time, to build your checksum. Which means the OTHER DB has to do the same. Append the checksum as the last field from the other DB. Recompute it before import on your DB. If they match, import the data using the appropriate CDat, CDbl, CSng, CCur, CLng, CInt, etc. for each text field.

Both solutions require cooperation on the part of the other DB, whatever it is. So to my mind, your FIRST question is whether that DB can support your needs. That is your first big show-stopper. If you can't break past that barrier, you are done.

 

[blah]

That's a reply worthy of a Pulitzer.