Field Validation Rule fails in VBA !

[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

 

[Brendan Reynolds]

It would probably help if you told us a) what the validation rule is and b)
what the data type of the field is (in both the source and target tables).
It might also be useful to know whether the linked table is a linked Jet
('Access') table or something else?

 

[Arnaud Lesauvage]

It would probably help if you told us a) what the validation rule is and b)
what the data type of the field is (in both the source and target tables).
It might also be useful to know whether the linked table is a linked Jet
('Access') table or something else?

Brendan, thanks for answering.
The validation rule is : LIKE "*.*"
(meaning that there must be a dot in the field)
The field is text, 12 characters long, indexed non-unique.
The linked table is JET (access 2000).

The problem happens in both ways : inserting into a linked table
or inserting from a linked table.

Arnaud

 

[Brendan Reynolds]

This is interesting. I can reproduce the error when attempting to execute
the SQL statement via ADO (CurrentProject.Connection.Execute) but the SQL
statement executes successfully via DAO (CurrentDb.Execute). This would seem
to indicate that it has something to do with ADO using the ANSI standard
wildcard '%' instead of the Jet wildcard '*'. But you can't use the '%'
wildcard when defining a validation rule via the UI. I have not tested
creating a validation rule using the '%' wildcard via ADO, that might be a
possible solution you might want to investigate. Alternatively, if you don't
mind using DAO instead of ADO, that would solve the problem ...

Public Sub TestRule()

' The commented-out line below will delete all records from the specified
table - be careful!
' CurrentProject.Connection.Execute "DELETE * FROM tblTarget"

' The two lines below cause the error with the validation rule of LIKE
"*.*"
' CurrentProject.Connection.Execute "INSERT INTO tblTarget ( TargetText )
" & _
' "SELECT tblSource.SourceText FROM tblSource;"

' This works with the existing validation rule.
CurrentDb.Execute "INSERT INTO tblTarget ( TargetText ) " & _
"SELECT tblSource.SourceText FROM tblSource;"

End Sub

 

[Brendan Reynolds]

Well, curiosity got the better of me, and I just had to find out whether
creating the validation rule via ADO would allow the use of the '%'
wildcards. And it does. Removing the existing validation rule and then
creating a constraint as in the example below should (as far as I can tell
based on limited testing) solve the problem ...

Public Sub CreateCheck()

CurrentProject.Connection.Execute "ALTER TABLE tblTarget " & _
"ADD CONSTRAINT CheckText " & _
"CHECK (TargetText LIKE '%.%')"

End Sub

 

[Arnaud Lesauvage]

This would seem
to indicate that it has something to do with ADO using the ANSI standard
wildcard '%' instead of the Jet wildcard '*'. But you can't use the '%'
wildcard when defining a validation rule via the UI. I have not tested
creating a validation rule using the '%' wildcard via ADO, that might be a
possible solution you might want to investigate.

Yes, I think you have a very good point here ! I did not think
avout this wildcard character thing !
I will try to set the rule via ADO.

Alternatively, if you don't
mind using DAO instead of ADO, that would solve the problem ...

Well, it is not that I mind, but I have no knowledge of DAO, and
all my VBA modules are coded with ADO.
Also, how do you use transactions with DAO ?

 

[Arnaud Lesauvage]

Well, curiosity got the better of me, and I just had to find out whether
creating the validation rule via ADO would allow the use of the '%'
wildcards. And it does. Removing the existing validation rule and then
creating a constraint as in the example below should (as far as I can tell
based on limited testing) solve the problem ...

Public Sub CreateCheck()

CurrentProject.Connection.Execute "ALTER TABLE tblTarget " & _
"ADD CONSTRAINT CheckText " & _
"CHECK (TargetText LIKE '%.%')"

End Sub

Brendan, that's great !
I wonder if the modified constraint will still work with DAO, or
wit ha standard Access query (from within the query designer, for
instance) ?
I'll give it a try !

Another question : can you change the validation-error message
when creating a constraint that way ?

 

[Arnaud Lesauvage]

I wonder if the modified constraint will still work with DAO, or
wit ha standard Access query (from within the query designer, for
instance) ?
I'll give it a try !

I gave it a try, and my worst fears became true : this constraint
fails when I try to insert data via a normal query...

I adjsuted the constraint to use InStr, so the query is :

CurrentProject.Connection.Execute "ALTER TABLE " & _
"Releves_Formulaire_Encodage " & _
"ADD CONSTRAINT CheckText " & _
"CHECK (InStr(1, [LOGACTU], ""."")>0)"

And it seems to work.

Thanks a lot for finding the bug Brendan !

 

[Brendan Reynolds]

I can get the "%.%" constraint to work with queries executed via the Access
UI if I turn on the "SQL Server Compatible Syntax (ANSI 92)" option (Tools,
Options, Tables/Queries). It still doesn't work via DAO, though, and I've
had 'issues' with that option in the past.

--
Brendan Reynolds (MVP)

I wonder if the modified constraint will still work with DAO, or wit ha
standard Access query (from within the query designer, for instance) ?
I'll give it a try !

I gave it a try, and my worst fears became true : this constraint fails
when I try to insert data via a normal query...

I adjsuted the constraint to use InStr, so the query is :

CurrentProject.Connection.Execute "ALTER TABLE " & _
"Releves_Formulaire_Encodage " & _
"ADD CONSTRAINT CheckText " & _
"CHECK (InStr(1, [LOGACTU], ""."")>0)"

And it seems to work.

Thanks a lot for finding the bug Brendan !

 

[peregenem]

I can get the "%.%" constraint to work with queries executed via the Access
UI if I turn on the "SQL Server Compatible Syntax (ANSI 92)" option (Tools,
Options, Tables/Queries). It still doesn't work via DAO, though, and I've
had 'issues' with that option in the past.

I finally got round to testing in Access (I don't usually have it
installed) and the following seems to work in both 'environments'

CHECK((data_col LIKE '*.*' OR data_col LIKE '%.%') AND data_col <>
'%.%' AND data_col <> '*.*')

 

[Brendan Reynolds]

Excellent! Works via the UI and via ADO or DAO, with and without the ANSI 92
option turned on. Nice work.