recordset returns zero records

  • Thread starter Thread starter dchendrickson
  • Start date Start date
D

dchendrickson

I am using Access 2002/XP and using ADO.

My recordset keeps returning no records. The SQL string
that the recordset is based upon is big and ugly, but it
works just fine if I past it into a query... In fact that
is how I generated it to begin with. Somewhere I am
missing something, but I cannot figure it.

The code looks like this:

Dim rstCard As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strCard as String

Set cnn = CurrentProject.Connection

Let strCard = "SELECT tblIOCardPinOccurrence.IOCardPinID,
tblIOCardPinOccurrence.PinMatesTo, "
Let strCard = strCard & "tblIOCardPin.IOCardPinLabel "
Let strCard = strCard & "FROM tblIOCardOccurrence INNER
JOIN ((tblIOCardConnector INNER JOIN "
Let strCard = strCard & "tblIOCardPin ON
tblIOCardConnector.IOCardConnectorID = "
Let strCard = strCard & "tblIOCardPin.IOCardConnector)
INNER JOIN tblIOCardPinOccurrence ON "
Let strCard = strCard & "tblIOCardPin.IOCardPinID =
tblIOCardPinOccurrence.IOCardPinID) ON "
Let strCard = strCard
& "tblIOCardOccurrence.IOCardOccurrenceID = "
Let strCard = strCard
& "tblIOCardPinOccurrence.IOCardOccurrenceID "
Let strCard = strCard & "WHERE
tblIOCardOccurrence.IOCardOccurrenceID = " & intOccurID
& " AND "
Let strCard = strCard
& "tblIOCardConnector.ConnectorLabel Like '*2';"


rstCard.Open strCard, cnn, adOpenStatic, adLockOptimistic


The SQL string looks like this when I send it to the
Debug window:

SELECT tblIOCardPinOccurrence.IOCardPinID,
tblIOCardPinOccurrence.PinMatesTo,
tblIOCardOccurrence.IOCardOccurrenceID,
tblIOCardPin.IOCardPinLabel FROM tblIOCardOccurrence
INNER JOIN ((tblIOCardConnector INNER JOIN tblIOCardPin
ON tblIOCardConnector.IOCardConnectorID =
tblIOCardPin.IOCardConnector) INNER JOIN
tblIOCardPinOccurrence ON tblIOCardPin.IOCardPinID =
tblIOCardPinOccurrence.IOCardPinID) ON
tblIOCardOccurrence.IOCardOccurrenceID =
tblIOCardPinOccurrence.IOCardOccurrenceID WHERE
tblIOCardOccurrence.IOCardOccurrenceID = 21 AND
tblIOCardConnector.ConnectorLabel Like '*2';

If I paste this string into a blank query, I get just
what I want for a result.

Also, I am opening another recordset in the same
procedure. That recordset is based on the same table and
has a nearly identical SQL string - only the WHERE
portion is changed. That recordset opens just fine. The
line of code for that recordset is:

rstBP.Open strBP, cnn, adOpenStatic, adLockOptimistic

Both .Open statements are identical except the SQL string.

I looked in the table and the records are there - or else
they wouldn't show up in the query...

Can anyone see anything in my string or otherwise in my
code that causes you to say 'hmmmm?'

As always, thanks so much for taking a look and helping.

-dc
 
I think I found the problem, but I don't understand why
it is a problem.

The very last part of my WHERE statement is:

Like '*2'

The '*' wildcard is the culprit... I could have sworn
that I have used wildcards in SQL statements before. Am I
delerious?

I have a workaround for the time, but I would still like
anyone's input on the use of a wildcard in a SQL string.

Thanks,

-dc
 
And now I think I have answered my question again...

It looks like SQL wildcards need to be in ANSI-92. So my
old standby '*' should actually be a '%'. Go figure.

Thanks... I can say thanks to myself can't I?

-dc
 
You might also want to do away with all the "Let" syntax.
strCard = "SELECT tblIOCardPinOccurrence.IOCardPinID,
tblIOCardPinOccurrence.PinMatesTo, "
strCard = strCard & "tblIOCardPin.IOCardPinLabel "
strCard = strCard & "FROM tblIOCardOccurrence INNER JOIN
((tblIOCardConnector INNER JOIN "

Or
strCard = "SELECT tblIOCardPinOccurrence.IOCardPinID, " & _
"tblIOCardPinOccurrence.PinMatesTo, tblIOCardPin.IOCardPinLabel " &
_
"FROM tblIOCardOccurrence INNER JOIN ((tblIOCardConnector INNER JOIN
" & _
 
Back
Top