[AccessD] Records Affected 0

John,

 Make sure the all the queries UseTransaction property is set to yes and a
lot of what your seeing should disappear (or at least make more sense).

  My guess is that your getting errors, but the query is being partially
committed.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Wednesday, May 27, 2009 11:26 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Records Affected 0

I have an interesting situation.  I am working on a database where numeric
data is scanned into a 
table from paper.  The original author had developed queries to replace all
I with 1, all l with 1 
etc (characters replaced with numbers).  There is a whole series of these
queries, replacing 
anything that looks like a zero but is actually alpha with a zero and
anything that looks like a 1 
but is actually alpha with a number one.

If I try to perform a db.execute and set dbFailOnError a hand full of these
queries fail.  If I take 
off the dbFailOnError the queries execute but the Records Affected property
reports a zero.  Now I 
have intentionally seeded a specific column with alpha characters, and the
queries do in fact fix 
the alpha (replaces with number) but the Records Affected still reports a
zero.

Shouldn't the RecordsAffected report the actual number of records updated?
And what's up with the 
failure if I use dbFailOnError.  The error reported is "data type mismatch
in criteria".

At first when I tried this I would get a failure of the query even when run
manually.  I narrowed it 
down to the last three columns which were empty (maybe literally).  I
modified the table to place a 
default empty string "" as data was appended into the table and now the
query runs manually but will 
still fail when dbFailOnError is applied.

The code:

     db.Execute "qryFix6_Correct_O", dbFailOnError
     Debug.Print "Correct 0 - Records Affected: " & db.RecordsAffected

The query:

UPDATE Import SET Import.Field1 = Replace([Field1],"O","0"), Import.Field3 =

Replace([Field3],"O","0"), Import.Field4 = Replace([Field4],"O","0"),
Import.Field5 = 
Replace([Field5],"O","0"), Import.Field6 = Replace([Field6],"O","0"),
Import.Field7 = 
Replace([Field7],"O","0"), Import.Field8 = Replace([Field8],"O","0"),
Import.Field9 = 
Replace([Field9],"O","0"), Import.Field10 = Replace([Field10],"O","0"),
Import.Field11 = 
Replace([Field11],"O","0"), Import.Field12 = Replace([Field12],"O","0");

Again, the queries do run if I remove the dbFailOnError and they do "fix up"
the data correctly, but 
they incorrectly report zero records affected after fixing up the data.

I do so love Access.

-- 
John W. Colby
www.ColbyConsulting.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com