ADO SQL Select Problem

[Laura]

Hello, all,

Can anyone tell me what's wrong with this code (from an Access 2002
database):

Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText

cmd.CommandText = _
"SELECT tblData.fldCycle, tblData.fldTIN, tblData.fldTxPd " _
& "FROM tblData " _
& "LEFT JOIN tblHistory " _
& "ON (tblData.fldTxPd = tblHistory.fldTxPd) " _
& "AND (tblData.fldTIN = tblHistory.fldTIN) " _
& "AND (tblData.fldCycle = tblHistory.fldCycle) " _
& "WHERE (((tblHistory.fldCycle) Is Not Null) " _
& "AND ((tblHistory.fldTIN) Is Not Null) " _
& "AND ((tblHistory.fldTxPd) Is Not Null));"

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
Set rst = cmd.Execute

All I want to do is get a RecordCount from this recordset. But it
keeps returning a ForwardOnly recordset, which has a RecordCount of
-1.

Any help will be appreciated!

Laura

 

[Brendan Reynolds]

I don't use 'classic' ADO much, so if anyone knows differently please don't
hesitate to correct me, but I don't think you can change the type of
recordset returned by the Execute method of the Command object. If you need
a different type of recordset, I think you'll need to use the Open method of
the Recordset.

If all you want is the number of records, consider something like the
following instead of retrieving all records and using the RecordCount
property ...

Public Function CountRecords() As Long

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT Count(*) AS TheCount FROM SomeTable WHERE
SomeField = 'SomeValue'"
.Open
CountRecords = .Fields("TheCount")
.Close
End With

End Function