Field Validation Rule fails in VBA !

  • Thread starter Thread starter Arnaud Lesauvage
  • Start date Start date
A

Arnaud Lesauvage

Hi all !

I have a strange problem here :
I am inserting data from a linked table into a local table (it is
a table i my frontend, that I use as a temporary storage for
better performances).
I added a validation rule on a field, and it always fail when I
run the INSERT statement from VBA (using connection.execute), but
it works when I run the query manually !

Let me be a little more specific :

First of all, I know that the rule is always valid (with the
actual data). It is very easy to check, and I did so many times.

My code looks like this :
currentproject.connection.execute "INSERT INTO
myTableWithValidation SELECT * FROM myLinkedTable WHERE someField
= something"
This raises the error message that I entered as a failed
validation check message.
If I run the exact same query from query designer, it works !

If I use DoCmd.RunSQL, instead of connection.execute the query works !

I have to use connection.execute though, because this query is
inside a quite large transaction.


What am I doing wrong ?


Thanks for helping !

Arnaud

PS : sorry for multiposting, but I forgot to crosspost my first
message. :(
 
Hi,


It may occurs that the failure is about inserting a (one) particular
record out of many records. It is generally expected to NOT necessary end
the processing if an error occur. Only critical error would stop the
processing (such as no more storage capacity left, lost of the network
connection, etc). In fact, as example, with CurrentDb.Execute, there is an
OPTIONAL FLAG to tell to report the error to VBA:


CurrentDb.Execute strSQL, dbFailOnError


If you don't specify the second argument with the defined constant
dbFailOnError, a not-critical error while the SQL statement is executed
WOULD NOT BE REPORTED to your VBA environment.

It seems you get that case, as, for example, a duplicated value under a
UNIQUE constraint (no dup). Without the flag, under that circumstance, the
duplicated values won't be appended, as record, plain and simple, while with
the flag, same thing, but, in addition, you get a VBA trappable error. If
embedded inside a transaction, in the last case, you can decide that the
whole transaction is compromised, and explicitly roll it back, as example.

That sounds to be "by design".



Hoping it may help,
Vanderghast, Access MVP
 
This is a good point, Michel. However, in my tests, there is no data that
should fail the validation rule. The data looks like this ...

one.two
two.three
three.four

With a validation rule of LIKE "*.*", the data can be successfully appended
via the UI or DAO, but fails via ADO. With a check constraint of LIKE "%.%"
the data can be successfully appended via ADO, but not via the UI or DAO.

Here's a link to the more complete thread in the tablesdbdesign group ...
http://tinyurl.com/bwsla
 
Back
Top