Like validation problem in SQL append query

[dgy]

I am having problems with SQL append queries (INSERT INTO etc) in Access 2007
whenever a target table has a field with a validation rule that uses Like.

Whenever I run the SQL INSERT command, it refuses to append any records and
gives the reason as the validation text of a field in the target table that
contains a Like clause in its validation rule. If I remove the Like part of
the validation rule in the target table, it runs okay. But the records that
should be inserted are valid under the unadjusted rule (ie pass the like
test).

That is confirmed because if I replace the SQL command (called through
CurrentProject.Connection.Execute) with an Access append query that has
exactly the same underlying SQL syntax (called through DoCmd.OpenQuery) then
the records are appended no problem. I have used this as a temporary work
around and test.

However I really don't want to have pre-constructed Access queries lying
around for this purpose. It arises a lot in the system I am working on and
there is sufficient variability that it would be much easier to be able to
construct SQL to suit the circumstance of the particular insert/append.

The issue seems to be something to do with like validation clauses and
perhaps a SQL/Access difference but as far as I can gather from the reference
docs there shouldn't be a problem. The Like clauses tested so far are about
ensuring particular forms of text usually to do with making sure particular
characters are digits eg Like "C####" (so "C1234" is valid); Like "#######"
(so "1234567" is valid). The source data complies with the requirements
although it gets rejected .

I don't know whether it makes any difference but in each case the source
table is in the current database while the target is a linked table in
another. All are Access 2007 databases.

TIA,

dgy

 

[Dirk Goldgar]

I am having problems with SQL append queries (INSERT INTO etc) in Access
2007
whenever a target table has a field with a validation rule that uses Like.

Whenever I run the SQL INSERT command, it refuses to append any records
and
gives the reason as the validation text of a field in the target table
that
contains a Like clause in its validation rule. If I remove the Like part
of
the validation rule in the target table, it runs okay. But the records
that
should be inserted are valid under the unadjusted rule (ie pass the like
test).

That is confirmed because if I replace the SQL command (called through
CurrentProject.Connection.Execute) with an Access append query that has
exactly the same underlying SQL syntax (called through DoCmd.OpenQuery)
then
the records are appended no problem. I have used this as a temporary work
around and test.

However I really don't want to have pre-constructed Access queries lying
around for this purpose. It arises a lot in the system I am working on and
there is sufficient variability that it would be much easier to be able to
construct SQL to suit the circumstance of the particular insert/append.

The issue seems to be something to do with like validation clauses and
perhaps a SQL/Access difference but as far as I can gather from the
reference
docs there shouldn't be a problem. The Like clauses tested so far are
about
ensuring particular forms of text usually to do with making sure
particular
characters are digits eg Like "C####" (so "C1234" is valid); Like
"#######"
(so "1234567" is valid). The source data complies with the requirements
although it gets rejected .

I don't know whether it makes any difference but in each case the source
table is in the current database while the target is a linked table in
another. All are Access 2007 databases.

I wonder if it has to do with the difference between normal Jet "Like"
syntax and the ANSI-92 "Like" syntax. When you execute queries via ADO, the
ANSI-92 ("SQL-Server Compatible") syntax is in effect, and the Like operator
takes a different set of wild-card characters. I would not have expected
this to apply to validation rules in the target table, only to WHERE clauses
in the SQL, but what you describe sounds rather like it's using ANSI-92
syntax when applying the validation rule for an append query executed via
ADO.

For example, Jet's Like uses '*' and '?' to match all and match one,
respectively, where ANSI-92 Like uses '%' and '_'. I don't know offhand if
ANSI-92 syntax even allows the use of '#' to match a digit.

Is it really the validation rule in the table that is giving the problem?
Not a Like expression in the query SQL itself?

What happens if you execute your query using DAO methods instead of ADO? In
other words, instead of

CurrentProject.Connection.Execute strSQL

use

CurrentDb.Execute strSQL, dbFailOnError

If the problem is really ADO in ADO's insistence on ANSI-92 syntax, using
DAO to execute the query should get around it.

 

[John Spencer]

You could try changing the validation rule so that is conforms to both
flavors of SQL.

Like "C[0-9][0-9][0-9][0-9]" is the same as Like "C####" but the first
version works with both flavors of SQL.

Of course, that doesn't solve the two flavors of wild cards "*" versus "%"
and "?" versus "_"

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Dirk Goldgar]

You could try changing the validation rule so that is conforms to both
flavors of SQL.

Like "C[0-9][0-9][0-9][0-9]" is the same as Like "C####" but the first
version works with both flavors of SQL.

Of course, that doesn't solve the two flavors of wild cards "*" versus "%"
and "?" versus "_"

Good idea. And dgy could use the ALike operator instead of Like, which is
like "LIke" but always evaluated according to the ANSI-92 rules. That way
it wouldn't matter whether ADO or DAO is used.

 

[dgy]

Thanks immensely Dirk & John.
You solved my problems, it does appear to have been a compatibility problem
between Access & ANSI-92 SQL.

In response to your questions & suggestions:
1. The SQL statements don't have LIKE anywhere they are just:
INSERT INTO tblTarg SELECT tblSouce.* FROM tblSource

2. Switching to DAO from ADO did make the problem go away (but I'd prefer to
use ADO for consistency with the rest of the system).

3. Using "[0-9]" instead of "#" in the Like predicates also removed the
problem (I knew about the wildcard differences between the two SQL dialects
but hadn't realised that digit specification could also be different). I'll
use this solution in all the cases I've got at the moment which I think all
involve requiring digits.

4. I wasn't aware of ALIKE. I couldn't find anything about it in the Access
help reference. I did eventually track down some discussion in one (and only
one) of the books in my Acess library ("Expert Access 2007 Programming"). It
talked about using it when dealing with ANSI-92 compatible databases. The
Access databases I am working on _don't_ have the ANSI-92 compatible mode set
(they are just ordinary Access Jet ones). I assume there is no problem using
ALIKE in setting validation rules for them? If there isn't, that will take
care of any cases where wildcards have been used in the validation rules.

Thanks again,

dgy

You could try changing the validation rule so that is conforms to both
flavors of SQL.

Like "C[0-9][0-9][0-9][0-9]" is the same as Like "C####" but the first
version works with both flavors of SQL.

Of course, that doesn't solve the two flavors of wild cards "*" versus "%"
and "?" versus "_"

Good idea. And dgy could use the ALike operator instead of Like, which is
like "LIke" but always evaluated according to the ANSI-92 rules. That way
it wouldn't matter whether ADO or DAO is used.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks immensely Dirk & John.
You solved my problems, it does appear to have been a compatibility
problem
between Access & ANSI-92 SQL.

You're welcome. I never ran into that before in a validation rule, so I
learned something from your post. I'll have to check if this is a new
behavior with Access 2007.

4. I wasn't aware of ALIKE. I couldn't find anything about it in the
Access
help reference.

I wonder if they really don't want us to use it. In Access 97 help, I
believe, it was well documented. In Access 2003 help, there is one passing
reference that I can see, but it's really hard to find. I can't find it in
the Access 2007 help.

I did eventually track down some discussion in one (and only
one) of the books in my Acess library ("Expert Access 2007 Programming").
It
talked about using it when dealing with ANSI-92 compatible databases. The
Access databases I am working on _don't_ have the ANSI-92 compatible mode
set
(they are just ordinary Access Jet ones).

Yes, but when you use ADO to query it, I think the query is processed in
ANSI-compatibility mode.

I assume there is no problem using
ALIKE in setting validation rules for them? If there isn't, that will take
care of any cases where wildcards have been used in the validation rules.

I just made a simple test and it worked fine. I'll leave it to you to test
it when invoking the query via ADO.