RE: OLEDBDataReader has no rows, yet query is fine in Access

Hi Gary,

Welcome to MSDN newsgroup!

For your current issue, I think Brendan's suggestion is right! Access uses
the Jet SQL statement but ADO.NET just can recognize the ANSI SQL statement.

I suggest you watch "Comparison of Microsoft Jet SQL and ANSI SQL" article
in Microsoft Office Access Help. In "Major Differences" section, there is
some description as below:

=====================================================================
Microsoft Jet SQL supports both ANSI SQL wildcard characters and Microsoft
Jet-specific wildcard characters to use with the Like operator. The use of
the ANSI and Microsoft Jet wildcard characters is mutually exclusive. You
must use one set or the other and cannot mix them. The ANSI SQL wildcards
are only available when using Jet 4.X and the Microsoft OLE DB Provider for
Jet. If you try to use the ANSI SQL wildcards through Microsoft Access or
DAO, then they will be interpreted as literals. The opposite is true when
using the Microsoft OLE DB Provider for Jet and Jet 4.X.

Matching character Microsoft Jet SQL ANSI SQL
Any single character ? _
(underscore)
Zero or more characters * %
======================================================================

I hope this information helps. If anything is unclear, please let me know.
I look forward to your reply.

Regards,

Yuan Ren [MSFT]
Microsoft Online Support

.

Relevant Pages

  • Re: Problems using "Like" in query feeding Mail Merge Document
    ... Jet SQL and ANSI SQL): ... Microsoft Jet SQL supports both ANSI SQL wildcard characters and Microsoft ... Has something changed in Access 2003 using "Like" in criteria of a query ...
    (microsoft.public.access.queries)
  • Re: User Defined Functions
    ... The Microsoft Jet 4.0 database engine permits you to call unsafe ... JET SQL does not support CREATE FUNCTION. ... them as a function from within the query. ...
    (microsoft.public.access.queries)
  • Re: SQL REPLACE function does not work thru ADO/DAO/OLE layer
    ... There is no 'REPLACE' function in Jet SQL. ... in Jet queries when they are executed outside of the Microsoft Access ... You may be able to achieve the same result in a query using ...
    (microsoft.public.data.ado)
  • Re: How to create an AutoNumber field with a SQL statement?
    ... "But to maintain backward compatibility with earlier versions of Microsoft ... Jet, the enhancements to SQL are available only when the database engine is ...
    (comp.databases.ms-access)
  • Re: Question for Joe Fallon--determining Data type
    ... then the data type for the column will be Text. ... means 255 characters max (that's a Jet limit). ... where MSDE or SQL Server is the main database (in these cases Jet ... Is 'Spreadsheet' an MS euphemism for 'Excel' or does it support other ...
    (microsoft.public.access.externaldata)