Return ADO recordset as a function

[bradyboyy88]

So I was wondering if you are able to pass a recordset as a function and if you assign this returned recordset as a value then when variable type is that record set. Such as:

UsernameRecordset = DatabaseMethods.SQLQueryDatabase(SQLQueryCode, DatabaseDirectory, 0)

I did Dim UsernameRecordset As Variant but that draws a mismatch error. And Dim UsernameRecordset As ADODB.Recordset draws an error as well.

The code for the function is :

Public Function SQLQueryDatabase(SQLQuery As String, StrDBPath As String, EngineType As Integer, Optional ByVal FieldNumber As Integer)




    'Declare Variables
    Dim oConn        As Object
    Dim oRs          As Object
    Dim sConn        As String
   
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
    If EngineType = 0 Then
    
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;"
    
    'Excel Support for engine type
    ElseIf EngineType = 1 Then
        
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=YES;ReadOnly=0;"";"
    
    End If
    
    'Create Connection
    Set oConn = CreateObject("ADODB.Connection")
   
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
    
    'Connect to the database
    oConn.Open sConn
    
    'Define SQL String
    'SQLQuery = "SELECT * FROM Username_Table"
    
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn
    
    'Return single value or null if it doesnt exists
    If oRs.EOF And oRs.BOF Then


        SQLQueryDatabase = Null
        
    Else
        
         If FieldNumber = "" Then
            
            SQLQueryDatabase = oRs
            
         Else
         
            SQLQueryDatabase = oRs(FieldNumber).Value
         
         End If
        
    
    End If


    'Close Connection
    oConn.Close
    
    'Clear Memory
    Set oConn = Nothing
    Set oRs = Nothing




End Function

 

[V_Malkoti]

Just comment/remove the oConn.Close and oConn=Nothing statements.

Using the Close method to close a Connection object also closes any active Recordset objects associated with the connection. (https://msdn.microsoft.com/en-us/library/ms675814(v=vs.85).aspx)

 

[bradyboyy88]

Just comment/remove the oConn.Close and oConn=Nothing statements.

Using the Close method to close a Connection object also closes any active Recordset objects associated with the connection. (https://msdn.microsoft.com/en-us/library/ms675814(v=vs.85).aspx)

That did the trick!! So my program actually queries the database a lot from various users. Is there anyway to close the connection (inside the function) to ensure it doesnt just stay open but not lose the recordset I just passed to a variable ?

 

[bradyboyy88]

I guess this would also answer my question of if you can have more than one recordset at a time since I plan on repetatively using this function and passing the recordsets to different variables which I will use later in my program.

 

[Ombir]

Is there anyway to close the connection (inside the function) to ensure it doesnt just stay open but not lose the recordset I just passed to a variable ?

You can use the disconnected recordset. Check below link.

https://support.microsoft.com/en-us/kb/184397

 

[bradyboyy88]

You can use the disconnected recordset. Check below link.

https://support.microsoft.com/en-us/kb/184397

Hey thanks for the link. I have been trying to incorporate it into my function but I cannot seem to get it to work. I added set oRs.ActiveConnection = nothing right after running the query which looked like that created the disconnected recordset. I thought that could be then returned by the function but maybe not?

I even included the adUseClient setting for cursorlocation and still draws an "Item cannot be found in the collection corresponding to the requested name or ordinal" which is the error I had before i commented out closing the recordset and connection which was the solution previously.

 

[bradyboyy88]

So I got the disconnected recordset to work!! Only strange thing is commented in the last part of my code which is why I cannot close the current recordset even after I have technically passed it to a variable as a function. Thats a mystery to me but here it is in case anyone needs to use this:

'Return a query as a recordset
Public Function SQLQueryDatabaseRecordset(SQLQuery As String, StrDBPath As String, EngineType As Integer) As Variant


    'Declare Variables
    Dim oConn        As Object
    Dim oRs          As Object
    Dim sConn        As String


    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
    If EngineType = 0 Then
    
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;"
    
    'Excel Support for engine type
    ElseIf EngineType = 1 Then
        
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=YES;ReadOnly=0;"";"
    
    End If
    
    'Create Connection
    Set oConn = CreateObject("ADODB.Connection")
   
    'Create RecordSet and set as clientside for disconnected recordset
    Set oRs = CreateObject("ADODB.Recordset")
    oRs.CursorLocation = adUseClient
    
    'Connect to the database
    oConn.Open sConn
       
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn
    
    'Disconnect the recordset
    Set oRs.ActiveConnection = Nothing
        
    'Return recordset
    Set SQLQueryDatabaseRecordset = oRs


    'Close Connection
    oConn.Close
    'oRs.Close For disconnect recordset this should not be closed for some reason despite I passed it on to variable when calling function. Not sure why..
    
    'Clear Memory
    Set oConn = Nothing
    Set oRs = Nothing




End Function