Query incorrectly returning no records in ADO

[Julia B]

All

I've got a piece of code to loop through a recordset on a split database.
I'm opening a query, but the code always fails because the recordset
recordcount is always -1. However when I run the query directly in Access it
returns 26 records. The only thing I can think is that the sql syntax for the
query is not good in ADO??

Here's the code:

Dim rs As New ADODB.Recordset
Dim db As ADODB.Connection
Set db = CurrentProject.Connection
rs.Open "_TempPNSFormattedIncorrectly", db, adOpenKeyset,
adLockOptimistic, adCmdTableDirect
'fails here
rs.MoveFirst

The sql syntax for query _TempPNSFormattedIncorrectly is:

SELECT PartNumbers.PNStored
FROM PartNumbers
WHERE (((PartNumbers.PNStored) Like "**/**/****"));

Any help greatly appreciated.

Thanks

Julia

 

[Julia B]

All

Just worked it out. I need to change my query syntax to replace the * with
%. So even though, when I run the query directly from access to test it, it
shows no records, it does work if run via ADO.

Julia

 

[david]

You can change the mode of Access so that it expects
all queries to use % instead of *. Then this query would
also work in Access, but none of your * queries would.

(david)