recordset returns 0 records

[dchendrickson]

I am using Access2002/ XP Pro and writing VBA primarily
using ADO methods.

I have developed a query that uses as one of its criteria
the value returned from a function. The function simply
returns the value of a global variable. I used the
function to return the global variable value since I
wasn't sure how to directly reference the global variable
in the criteria line of the query. (this isn't my
problem, but suggestions on a direct reference would help
clean things up).

Regardless, I can set the global variable either with
code or in the immediate window, then open the query and
it returns the records I expect.

The problem is with a sub routine that is opening a
recordset based on the query. The recordset keeps
returning 0 records. At first, I was creating an SQL
string to open a subset of what the query returned and
figured I must have something wrong in the syntax.

As a step to debug, I eliminated the SQL statement and
simply set the recordset source to the name of the query.

Double click on the query - records returned. Run the
code, no records returned. ?????

Here is my simple little code:

Public Sub recordsetsubset()
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
'Dim strSQL As String

Set cnn = CurrentProject.Connection
'next line forces the global constant for debug
Let gintIOCardIDCurrent = 3
rst.Open "qryPinConnectsToLabel", cnn, adOpenStatic,
adLockOptimistic

Debug.Print rst.RecordCount & " records returned"
End Sub

I thought I was getting good at working with recordsets,
but I have reduced this to pretty basic code and the
answer still eludes me. Your help is greatly appreciated.

-dc

 

[dchendrickson]

Well, I figured out what was tripping me up. You would
think I would have learned this one because it got me a
few months back...

The Criteria in the query also uses some wildcards. And
although Access' query builder will recognize the '*'
wildcard, SQL needs the '%' to accomplish the same thing.

I am sure there is a good reason for this inconsistancy,
but it wont get my 4 hours back...

-dc

 

[Tim Ferguson]

I am sure there is a good reason for this inconsistency,
but it wont get my 4 hours back...

Access is for people who came from DOS and other desktop environments, used
to * and ? wildcards.

SQL Server is designed for people who are database trained, and bring the %
and _ wildcards that have been standard since the sixties (longer than MS-
DOS).

HTH


Tim F

 

[Guest]

Tim,

Thanks, for the lesson. I was sure the origins were
valid. My first exposure to the world of computers only
dates back to the early '80s. So yes, I am a child of
MSDos.

-dc