Using CheckSums to insure data integrity

R

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
 
M

Marshall Barton

Ron said:
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.
Click to expand...


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
 
R

Ron W

Ron said:
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.
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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)"
Click to expand...
Any help would be most appreciated, INCLUDING alternate methods for
performing data integrity checking. I am wide open to all suggestions/
solutions.
Click to expand...

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 -
Click to expand...

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
 
R

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
 
M

Marshall Barton

Ron said:
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
Click to expand...

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 [snip]
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
Click to expand...


There are two utility tables that I use and recommend for
all databases. One is the Numbers table populated with 0
through 999 or more (Where clause limits it to specific
need).

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). This avoids any extra work
(aggregation, UNION vs UNION ALL, etc) for Jet in getting
just one record. I thought about using my one row table in
a similar way to what you did:
(SELECT 1 FROM OneRow
UNION SELECT 2 FROM OneRow
. . .
)
But figured that if you needed a utility table, the Numbers
table is more straightforward, more versatile and used in
more situations. If speed is not an issue, your clever use
of First on any old table is also an approach I'll have to
keep in mind.
 
J

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).
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

determining if a field should be included 6
dsum sytax error 5
Run time Error 3035, System resource exceeded 4
Query is too complex 5
help with SQL statement 3
Union query not displaying select statement 1
Heavy data munching - is there a more efficient way of doing this? 8
do I need more indexes - followup 1

Top