CODEWITHSUNDEEP
exceladodbrecordsetvba
cosmarchy
cosmarchy

Reputation: 686

How do you perform a SQL query on a recordset in VBA?

I am using Excel VBA in where I have located in different locations across different sheets which I will need to run some SQL queries on.

So to start with, I have bashed together some 'simple' functions to try for proof of concept so see whether I can get this working. So far, I have this:

    Private rstADO As ADODB.Recordset

Private Function StartEre()

    Call SetupRecordset
    Call Add("123", "ab", "ba")
    Call Add("321", "ba", "ab")

    Dim rs As ADODB.Recordset
    Set rs = search("123", rstADO)

End Function

Private Function search(emp As String, oRecordset As ADODB.Recordset) As ADODB.Recordset

    Dim strSQL As String
    Dim cn As New ADODB.Connection
    Dim rs As ADODB.Recordset

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes"";"

    strSQL = "SELECT EmployeeID FROM oRecordset"
    Set rs = cn.Execute(strSQL)

    Set search = rs

End Function

Private Function Add(emp As String, first As String, last As String)

    Dim fieldsArray(2) As Variant
    Dim values(2) As Variant

    fieldsArray(0) = "EmployeeID"
    fieldsArray(1) = "FirstName"
    fieldsArray(2) = "LastName"
    values(0) = emp
    values(1) = first
    values(2) = last

    Call rstADO.AddNew(fieldsArray, values)

End Function


Private Function SetupRecordset()

    Dim fld As ADODB.Field

    Set rstADO = New ADODB.Recordset

    With rstADO
        .Fields.Append "EmployeeID", adInteger, , adFldKeyColumn
        .Fields.Append "FirstName", adVarChar, 10, adFldMayBeNull
        .Fields.Append "LastName", adVarChar, 20, adFldMayBeNull

        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockPessimistic
        .Open
    End With

End Function

My issue is in the search function at the point where I have my SQL string:

strSQL = "SELECT EmployeeID FROM oRecordset"

How do I get the FROM clause to point to the recordset supplied to this function? This is only a simple function, but eventually there will be (if I can get this to work) a number of functions with different SQL queries performing different actions.

Edit: Having been identified as a possible duplicate from Performing SQL queries on an Excel Table within a Workbook with VBA Macro I want to add clarity.

The post mentioned queries a range from a sheet whereas I want to query a recordset which is pre-filled. My search function has an argument - oRecordset which contains all the data I want to query using SQL. I do not want to query the sheets directly as in the previously mentioned link.

Upvotes: 2

Views: 14546

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

I realise this is an old thread but as it stands unanswered I will post my rather clunky solution.

The basic principle is...

  1. Create an empty sheet (tempRS) within the workbook for temporary use
  2. (optional) Loop thru the recordset fields and write to the first row of our tempRS sheet starting at A1, so our new recordset will have column names to work with.
  3. Paste the recordset into the new sheet with ThisWorkbook.Sheets("tempRS").Range("A2").CopyFromRecordset rs
  4. You now have a sheet that you can query as normal with something like SELECT * FROM [tempRS$]
  5. optionally, if your original recordset has a client side cursor (rs.CursorLocation = 3), use this to calculate the range for the SELECT statement, rather than the entire sheet so you would end up with SELECT * FROM [tempRS$A1:N30]
  6. Remove the tempRS sheet

There may be a better solution but I'm not aware of any other way of querying a recordset directly without additional libraries.

Upvotes: 3

Related Questions