How to get data out of a recordset in excel?

Question

I have the following code:

Dim cn As Object
Dim rs As Object
Dim strSql As String
Dim strConnection As String
Dim AppPath As String
Set cn = CreateObject("ADODB.Connection")
AppPath = Application.ActiveWorkbook.Path

Set rs = CreateObject("ADODB.RecordSet")

strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & AppPath & "\Masterlist_Current_copy.accdb;"

strSql = "SELECT [Neptune Number],[Description],[Manufacturer],[Manufacturer P/N] FROM [All Components];"
cn.Open strConnection
Set rs = cn.Execute(strSql)

'Need Code here to get Info out of recordset

I am trying to get information out of the recordset that has the query result being dumped into it. I'm trying to figure out how to query the recordset and get the number of rows with a specific value in the "Neptune Number" field. I will then insert the correct number of rows into the worksheet I'm modifying. After that I need to get the data for that value and insert it into the worksheet.

Note: I don't care if recordset, datatable or anything else is used I simply need to be able to do what I described above. Please show code.

Answer 1

The easiest way to get where you I think you are asking to go is to modify your SQL statement changing

strSql = "SELECT [Neptune Number],[Description],[Manufacturer],[Manufacturer P/N] FROM [All Components];"

to

strSql = "SELECT [Neptune Number],[Description],[Manufacturer],[Manufacturer P/N] FROM [All Components] WHERE [Neptune Number] = 'Specific Value' ;"

This will force the sql query to only return the records you need. the .find method can be used for filtering the recordset, but i have avoided using it in this instance as it is cleaner to just ask the db for only the information that you want.

to process the recordset you can use the following

with rs
    'will skip further processing if no records returned
    if not (.bof and .eof) then
        'assuming you do not need the headers
        'loop through the recordset
        do while not .eof
            for i = 0 to .fields.count -1
                'assuming the active sheet is where you want the data
                cells(row, i + colOffset) = .fields(i).value
            next
            Rows(Row & ":" & Row).Insert
            .movenext
        loop
    end if
end with

Where row is the starting point of your data and colOffset is the starting column of your data. Note that this code does not do things in the exact order you specified in your question (I am inserting rows as needed instead of calcualting the number of records up front.)

I have avoided using .recordcount because I find depending on the database used it will not return a correct record count.