SQL Query to Count Results

[hypnotoad]

I've been using this code (2nd code bit) for a while to retrieve data from an access database. I now need to count the number of records in the set before I write the data. I'm aware I could count the records by adding a count to the loop below, but I want to change the way this the whole thing works! I want to import my data to an array, so I would need to redim the array first. I have tried writing an SQL query that counts but I can't get the thing to work. Can I build it into the query below? I have to do two queries right? I'm actually extremely confused about how the whole thing works :|... I'm aware that the query to count the results would be the following but I can't seem to implement it. I want to store the number of results in the rCount variable. I'm lost..

<code>SELECT count(*) FROM Tracking  WHERE PARAM1 = 'TEMP' ORDER BY `Start Date` DESC"</code>

Sub MainBck()
    Dim Conn As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim Cmd As New ADODB.Command
    Dim sqlText As String
    Dim Row As Long
    Dim Findex As Long
    Dim Data As Worksheet
    Dim X As Long
    Dim myID As String
    
    Dim rCount  As Long     'result count

    Set Data = Sheets("Main")
    Data.Select
    Range("A:F").ClearContents
    Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & "Data Source=C:\Tracking.accdb;"
    Cmd.ActiveConnection = Conn
    Cmd.CommandType = adCmdText
    
    sqlText = "SELECT * FROM Tracking WHERE PARAM1 = 'TEMP' ORDER BY `Start Date` DESC"
    
    Cmd.CommandText = sqlText
    Set RS = Cmd.Execute
    
        
    For X = 0 To 10
        Data.Cells(1, X + 1) = RS.fields(X).Name
    Next
    Do While Not RS.EOF
       Row = Row + 1
       For Findex = 0 To RS.fields.Count - 1
         Data.Cells(Row + 1, Findex + 1) = RS.fields(Findex).Value
       Next Findex
       RS.MoveNext
    Loop

End Sub

 

[SydneyGeek]

You don't have to know how many rows you are importing: it can be done in one step.
Replace this...

    Do While Not RS.EOF
       Row = Row + 1
       For Findex = 0 To RS.fields.Count - 1
         Data.Cells(Row + 1, Findex + 1) = RS.fields(Findex).Value
       Next Findex
       RS.MoveNext
    Loop

with this...

Range("A2").CopyFromRecordset RS

Denis

 

[hypnotoad]

SydneyGeek, that's a great way of using RS. I will definitely use that in another portion of the workbook! Unfortunately, I still need to count the results because, when I figure this out, I won't be posting any results to a worksheet! I need to get all of the information into an Array, but the first step is knowing how many results I will have! I need to dimension the array...

 

[hypnotoad]

I just tried setting a variable equal to RS.RecordCount and that returns -1... Very confused.

 

[SydneyGeek]

RS.MoveLast
lngRecords = RS.RecordCount

If you don't go to the last record the recordset object doesn't know how many records there are. -1 is True, indicating that there are records.

Denis