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
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