Re: Field Validation Rule fails in VBA !

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

--
Brendan Reynolds (MVP)

"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
news:eT7mh5ItFHA.1968@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>
>
> "Arnaud Lesauvage" <thewild_NOSPAMTHX@xxxxxxxxxxxxxxxxxxxxx> wrote in
> message news:1126097738.d04b8f2cafb4b1befb1ca559ecfcac49@xxxxxxxxxxx
>> 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.
>> :(
>
>


.

Relevant Pages

  • Re: Is anyone maintaining (or even using) usbtmc?
    ... The easiest way is to write something that wasn't a query and then try ... Of course, the read should fail, but there's no reason it ... subsequent USBTMC_IOCTL_CLEAR to fail with ETIMEDOUT. ... but, when this happens, the problem persists across several messages), ...
    (Linux-Kernel)
  • Re: Entering A Transaction
    ... type Number, size Long Integer, Required, Validation Rule: ... The RecordSource for both forms is a query statement: ... You can now sum the Amount field like this: ... > I have a transactions table with a field named Transaction. ...
    (microsoft.public.access.formscoding)
  • Re: Entering A Transaction
    ... type Number, size Long Integer, Required, Validation Rule: ... The RecordSource for both forms is a query statement: ... You can now sum the Amount field like this: ... > I have a transactions table with a field named Transaction. ...
    (microsoft.public.access.gettingstarted)
  • Re: Merge two tables
    ... usually i don't append *any* of them, until i find and fix whatever problems ... rule violations. ... validation rule violations follow the same logic. ... I used query to make a new ...
    (comp.databases.ms-access)
  • Re: Field Validation Rule fails in VBA !
    ... It would probably help if you told us a) what the validation rule is and b) ... what the data type of the field is. ... > This raises the error message that I entered as a failed validation check ... > If I run the exact same query from query designer, ...
    (microsoft.public.access.tablesdbdesign)