access 2003 different result with VBA ADO sql and access select qu

[madhouse]

Hi

I hope someone can help,this is driving me mad...
I have an SQL statement that returns no records when executed in VBA with an
ADO open statement. The identical SQL (copied out of the SQL variable at run
time to be sure!) returns 1 record when executed as a direct SQL query in
queries!

aaaagggghhhh!

VBA code is:

SQL = "SELECT Count(ProFormaHdr.ProFormaID) as HowMany " & _
"FROM (ProFormaHdr INNER JOIN Company ON ProFormaHdr.CoID = Company.CoID)
Where " & _
strGlobalWhereClause & " and ProFormaHdr.ProFormaProducedDate is not NULL; "
ProFormaHdrData.Open SQL, Cnxn, adOpenForwardOnly, adLockOptimistic
HowMany = ProFormaHdrData!HowMany

HowMany is zero. If the Count is removed from the SQL, an empty recordset is
returned.

The value of the SQL variable is:

SELECT Count(ProFormaHdr.ProFormaID) as HowMany FROM (ProFormaHdr INNER JOIN
Company ON ProFormaHdr.CoID = Company.CoID) Where (( Company.CoName Like
"*DES*") OR ( Company.Soundex Like "DZ*")) AND
ProFormaHdr.ProFormaProducedDate is not NULL AND ProFormaHdr.ProFormaID in
(Select ProFormaItem.ProFormaID from ProFormaItem where ProFormaItem.ProdID
= 2) and ProFormaHdr.ProFormaProducedDate is not NULL;

This returns HowMany with value of 1 when run directly in queries.

The data tables are the same in both cases.

I must be missing something. Please please help

Thanks
Madhouse

 

[Douglas J. Steele]

ADO uses % as the wildcard character, not *.

 

[madhouse]

Thanks Douglas, I find this after 6 hours of trying different sql statements.
Interestingly my research turned up the MS KB article
http://support.microsoft.com/kb/q225048/ which suggests real advantages to
staying with DAO. However the general advice seems to be to use ADO and
indeed MS encourage this. So ADO or DAO?

Madhouse

 

[Douglas J. Steele]

If you're only going to be using Jet databases (i.e. MDB files), there is no
reason to move from DAO. DAO was specifically designed for Jet databases, so
you'll almost always get better performance.

If you may eventually migrate from a Jet backend to some other DBMS (such as
SQL Server or Oracle), a case could be made to use ADO now. However, in my
experience the migration will involve significant other changes such that
the use of ADO alone won't actually save you that much in the conversion
effort.

You might also be interested in reading what MichKa (one of the most
knowledgable people I know w.r.t. Access) has to say at
http://www.trigeminal.com/usenet/usenet025.asp

 

[Brendan Reynolds]

Microsoft *used* to encourage people to use ADO. Now they are encouraging
people to use ADO.Net, which has little more in common with 'classic' ADO
than three letters in its name, and is not supported by Access.

If you're used to ADO, then I see nothing wrong with using it as long as it
works. But don't hesitate to use DAO if/when you encounter something that
can't be done, or is very difficult to do, in ADO. Don't let anyone tell you
that you should use ADO instead of DAO because DAO is 'obsolete'. DAO is no
more 'obsolete' than 'classic' ADO.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.