none
Return an ADO Recordset from a VBA Function RRS feed

  • Question

  • I have written the following VBA Function to interrogate an SQLServer database and return a RecordSet:

    Function AXQuery(ByRef sSQL As String) As Object
    '
    '
    '===============================================================================
        Dim conn As Object
        Dim rs As Object
        Dim sConnString As String
    '-------------------------------------------------------------------------------
        '~~> Create the Connection and Recordset objects.
        Set conn = CreateObject("ADODB.Connection")
        Set rs = CreateObject("ADODB.Recordset")
        '~~> Create the connection string.
        sConnString = "Provider=SQLOLEDB;" & _
                      "Server=XXXSQL;" & _
                      "Initial Catalog=XXXX_Live;" & _
                      "Integrated Security=SSPI;"
        '~~> Open the connection and execute.
        conn.Open sConnString
        Set rs = conn.Execute(sSQL)
        '~~ Check we have data returned.
        If Not rs.EOF Then
            Set AXQuery = rs
        End If
        '~~> Clean up
        conn.Close
        Set conn = Nothing
        Set rs = Nothing
    End Function
    

    This works exactly as I hoped it would except that the following Sub which calls the above Function cannot open/use the returned Recordset object:

    Sub GetDBData(sSQL as string)
        Dim A
        Set A = AXQuery(sSQL)
        Range("A2").CopyFromRecordset A
    end Sub

    I get the following error message at line 4 for of this Sub:

    Please can anyone advise me how to gain access to the contents of this returned Recordset?

    As ever, all answers and advice will be very gratefully received.

    Paul J

    Tuesday, May 19, 2015 2:23 PM

Answers

  • Please can anyone advise me how to gain access to the contents of this returned Recordset?

    That is not possible, because the connection is closed.

    The local "conn" object variable which establish the connection is not longer available if the function ends. This variable must be global and the connection must open.

    Andreas.
    Wednesday, May 20, 2015 7:15 AM

All replies

  • Please can anyone advise me how to gain access to the contents of this returned Recordset?

    That is not possible, because the connection is closed.

    The local "conn" object variable which establish the connection is not longer available if the function ends. This variable must be global and the connection must open.

    Andreas.
    Wednesday, May 20, 2015 7:15 AM
  • Thank you for your response, Andreas.

    So, if I have understood you correctly, the ADO Recordset object is not a container holding the results of my SQL enquiry but a "mechanism" for accessing those results - but only if the Connection remains open?

    I understand your recommendation to use a global Recordset object and this is solution I shall use - thank you.

    However, for future reference, is it possible to have a "disconnected" Recordset object and, if so, how would you create it?

    Rgds,

    Paul J

    Wednesday, May 20, 2015 8:04 AM
  • However, for future reference, is it possible to have a "disconnected" Recordset object and, if so, how would you create it?

    Hi Paul,

    that is not possible. When you want to have the connection opened as short as possible, you can read the data from the RecordSet into a global array variable, sample from one of my sources:

    Sub Import_SL(ByVal Nummer As String, ByVal Filename As String) Dim objConnection As ADODB.Connection Dim rsData As ADODB.Recordset Dim Table As String Application.Cursor = xlWait Set objConnection = CreateObject("ADODB.Connection") objConnection.Open ConnectionString(Filename) Set rsData = New ADODB.Recordset Table = "Stücklisten" If FileType(Filename) = 1 Then Table = Table & "$" rsData.Open "SELECT * FROM [" & Table & "] WHERE (Fertigungsartikel LIKE '" & MakeSQLWildcards(Nummer) & "')", objConnection If rsData.EOF Then MsgBox "Artikelnummer '" & Nummer & "' nicht gefunden", vbInformation + vbMsgBoxHelpButton, "Import_SL", ThisHelpFile, 4 GoTo ExitPoint End If Header = RecordSet_GetHeader(rsData) Arr = rsData.GetRows Arr = Transpose(Arr)

    ExitPoint:
    rsData.Close
    objConnection.Close
    Application.Cursor = xlDefault
    End Sub

    After that you can copy the related parts from the array into the sheet. That is as fast as if read the data directly into the sheet.

    Andreas.

    Wednesday, May 20, 2015 10:14 AM
  • Hi,

    Just for the record, you can open a ADOD.Recordset and then 'disconnect' it. You need to specify the recordsets' CursorLocation = adUseClient and then set its' ActiveConnection to nothing.

    rs.CursorLocation = adUseClient
    set rs.ActiveConnection = nothing

    You can also re-connect a disconnected recordset (to carry out updates or whatever), although I don't recall ever have done that, personally.

    Wednesday, May 20, 2015 1:19 PM