Using CheckSums to insure data integrity

[Ron W]

I have a buckets full of data that I have to regularly move across the
Internet in a mostly unattended manner. The data is gathered locally
by scanning barcodes (lots of em). Data then gets moved from the
barcode scanner to a table in local Access database. We have done this
errorlessly many thousands of times. Now I'd like to automate the
aggregation of this data to our Sql Server back at Intergalactic HQ.
We have set up a Web Service for this and it seems to be working just
fine. However I am concerned about the possibility that some of the
data might get corrupted or
lost during the transfer.

So I am looking for some sort of simple test I can run on both sides
(Access and Sql) that would Lickety Split generate a check sum or hash
value that would reduce the possibility of a data transfer error to
really low probability's. I have been fooling around with a Sum of the
Digits method using a query on Sql that I am unable to duplicate on
the Access Side. Here is the Sql Query

SELECT
sum(CAST(SUBSTRING(Cast(tblScan.AttID AS nvarchar(12)), n, 1) AS INT))
AS
Asset_sum
FROM (
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12) AS I(n)
CROSS JOIN
tblScan

On our server this produces a result in less than a second summing
10,000 rows. The table structure on the Access and Sql Sides are
identical. I am now trying to AccessIZE it but have not been
successful. Here is a non working AccessIZEd version

SELECT
sum(Clng(mid(cstr(tblScan.AttID), n, 1) )) AS Asset_sum
FROM (
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12) AS I(n)
CROSS JOIN
tblScan

The above query when run in Access dies with an "Syntax error in FROM
Clause" and the cursor sitting between the "I(" and the "n)"

Any help would be most appreciated, INCLUDING alternate methods for
performing data integrity checking. I am wide open to all suggestions/
solutions. Many thanks in advance

Ron W

 

[Ron W]

I have a buckets full of data that I have to regularly move across the
Internet in a mostly unattended manner. The data is gathered locally
by scanning barcodes (lots of em). Data then gets moved from the
barcode scanner to a table in local Access database. We have done this
errorlessly many thousands of times. Now I'd like to automate the
aggregation of this data to our Sql Server back at Intergalactic HQ.
We have set up a Web Service for this and it seems to be working just
fine. However I am concerned about the possibility that some of the
data might get corrupted or
lost during the transfer.

So I am looking for some sort of simple test I can run on both sides
(Access and Sql) that would Lickety Split generate a check sum or hash
value that would reduce the possibility of a data transfer error to
really low probability's. I have been fooling around with a Sum of the
Digits method using a query on Sql that I am unable to duplicate on
the Access Side. Here is the Sql Query

SELECT
sum(CAST(SUBSTRING(Cast(tblScan.AttID AS nvarchar(12)), n, 1) AS INT))
AS
Asset_sum
FROM (
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12) AS I(n)
CROSS JOIN
tblScan

On our server this produces a result in less than a second summing
10,000 rows. The table structure on the Access and Sql Sides are
identical. I am now trying to AccessIZE it but have not been
successful. Here is a non working AccessIZEd version

SELECT
sum(Clng(mid(cstr(tblScan.AttID), n, 1) )) AS Asset_sum
FROM (
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9 UNION ALL
SELECT 10 UNION ALL
SELECT 11 UNION ALL
SELECT 12) AS I(n)
CROSS JOIN
tblScan

The above query when run in Access dies with an "Syntax error in FROM
Clause" and the cursor sitting between the "I(" and the "n)"

Any help would be most appreciated, INCLUDING alternate methods for
performing data integrity checking. I am wide open to all suggestions/
solutions.

Seems to me that you can get that effect by creating a
little utility table (named tblNumbers) with one field
(named Num) populated with 1 through 12.  Then the Access
query would look like:

SELECT
        sum(Clng(mid(cstr(tblScan.AttID), Num, 1) )) AS Asset_sum
FROM tblScan, Numbers

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Marsh, You are really smart! i couldn't see the dam forrest from the
trees. Here is what I found that works.

SELECT
Sum(Clng(IIf(Len(Mid(CStr([AttID]),[N],1))=0,0,Mid(CStr([AttID]),[N],
1)))) AS Asset_sum
FROM tblScan, Numbers

I try'd to use an NZ() instead of the IIF() but appearantly the result
of the MID() is a 0 length string and not a Null. Anyway this
works.

However there is another way using a table derived from a set of union
queries (as in the Sql example) instead of having to create an actual
table. There were a couple of obstacles. Access (apparently) has a
constraint against allowing a Query that does not have at least one
table in it. Since the only non system table in this database is the
tblScan table, it is the one I had to use. I then had to limit the
output of each SELECT statement to just one row hence the First()
Aggregate function. Anyway this actually works, and is not noticeably
slower than either of the other two examples. All of em return a
result over 3000 row test data set in under a second. Both of the
Access queries return the same result as the Sql query.

SELECT Sum(Clng(IIf(Len(Mid(CStr([AttID]),[N],
1))=0,0,Mid(CStr([AttID]),[N],1)))) AS Asset_sum
From tbSscan, (
SELECT First(1) as N From tblScan UNION ALL
SELECT First(2) as N From tblScan UNION ALL
SELECT First(3) as N From tblScan UNION ALL
SELECT First(4) as N From tblScan UNION ALL
SELECT First(5) as N From tblScan UNION ALL
SELECT First(6) as N From tblScan UNION ALL
SELECT First(7) as N From tblScan UNION ALL
SELECT First(8) as N From tblScan UNION ALL
SELECT First(9) as N From tblScan UNION ALL
SELECT First(10) as N From tblScan UNION ALL
SELECT First(11) as N From tblScan UNION ALL
SELECT First(12) as N From tblScan ) AS Numbers

Really appreciate your help, Thanks a bunch.

Ron W

 

[Ron W]

Revised Query using the Derived Table

SELECT Sum(Clng(IIf(Len(Mid(CStr([AttendeeID]),[N],
1))=0,0,Mid(CStr([AttendeeID]),[N],1)))) AS Asset_sum
From tblscan , (
SELECT 1 as N From tblScan UNION
SELECT 2 as N From tblScan UNION
SELECT 3 as N From tblScan UNION
SELECT 4 as N From tblScan UNION
SELECT 5 as N From tblScan UNION
SELECT 6 as N From tblScan UNION
SELECT 7 as N From tblScan UNION
SELECT 8 as N From tblScan UNION
SELECT 9 as N From tblScan UNION
SELECT 10 as N From tblScan UNION
SELECT 11 as N From tblScan UNION
SELECT 12 as N From tblScan ) AS Numbers

Dont need the First() Aggregate function if you do not use the UNION
ALL. This is what i will winf up using.

Thanls again Marsh for geting me going in the right direction.

Ron W

 

[John W. Vinson]

The other is a one row table (usually with app specific
fields for settings like version, etc) that I also use for
speed in several situations to make up a record (eg, add
"All" to a combo box).

I first learned relational databases back in the 1980's, when the company I
was working for brought in Oracle. At the time (and I'm sure still!) all
Oracle databases had a builtin table oddly named Dual, with one record and one
field, for just this purpose.