VBA SQL query to a range

Question

I want the resulting query to my destiny range without define it in my function, I mean... can I call the destiny range?

Hello, I want to make a query to sql without needing to define a range within the parameters of the function, that is:

Range ("A1") = sqlQuery ("DSN = myODBC;", "SELECT * FROM Table1", TodaLaConsulta)

When executing this line I only load one record from that range and I want the whole table, the code is:

Private Enum TipoConsulta
    SoloUnRegistro = 1
    TodaLaConsulta = 2
    EjecutarQuery = 3
    GuardarArray = 4
End Enum

Private Function sqlQuery(ByVal CadenaCnx As String, ByVal Consulta As String, ByVal Tipo As TipoConsulta) As Variant

    Dim Cnx As New ADODB.Connection   'Connection
    Dim RecSet As New ADODB.Recordset   'Recordset

    Cnx.Open CadenaCnx

    Select Case Tipo
        Case TipoConsulta.SoloUnRegistro

            RecSet.Open Consulta, Cnx
            sqlQuery = RecSet(0)    'JUST ONE RECORD

        Case TipoConsulta.TodaLaConsulta
            RecSet.Open Consulta, Cnx
            'HERE I WANT THE RESULTING QUERY TO MY DESTINY RANGE WITHOUT DEFINE IT IN MY FUNCTION, I MEAN... CAN I CALL THE DESTINY RANGE??
            Range.CopyFromRecordset RecSet

        Case TipoConsulta.EjecutarQuery
            Cnx.Execute Consulta    'EXECUTE

        Case TipoConsulta.GuardarArray
            RecSet.Open Consulta, Cnx   'TO ARRAY
            sqlQuery = RecSet.GetRows

    End Select

    RecSet.Close
    Cnx.Close

    Set RecSet = Nothing
    Set Cnx = Nothing    

End Function

Tim Williams · Accepted Answer · 2019-05-08 23:00:02Z

You can update your function parameter list to include an optional Range-type parameter:

Private Function sqlQuery(ByVal CadenaCnx As String, ByVal Consulta As String, _
                          ByVal Tipo As TipoConsulta, _
                          Optional rng As Range = Nothing) As Variant

     'dump result to rng if needed


End With

And then call like this:

sqlQuery "DSN = myODBC;", "SELECT * FROM Table1", TodaLaConsulta, Range("A1")

************ original answer (I was looking at GetRows) *******

GetRows returns a 2-D array, so you should be able to do something like

Dim res 

res = sqlQuery ("DSN = myODBC;", "SELECT * FROM Table1", TodaLaConsulta)

Range("A1").Resize(ubound(res,1) + 1, ubound(res,2) + 1).Value = res

but I don't think you can do it in a single line, since you need to resize the destination range, and to do that you need the size of the result array.

Note that GetRows returns an array which is [column, row], so you might need to transpose that before putting it on the worksheet.

Collectives™ on Stack Overflow

VBA SQL query to a range

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related