Return a recordset stored locally through a function

Question

I have a functional script in VB6 that I need to migrate to VBScript. As part of the reorganization of the code, I decided I was interested in utilizing a function to return a record set. Want to dispose of the connection to the database so the transaction closes so writing the recordset to an in memory object made sense to me. Way to many iterations trying to determine exactly what is wrong in my syntax, i'm not sure which would be the most compelling.

Sub Main()
    clientRecordset(" Exec [dbo].[StoredProc] ")

End Sub

Private function clientRecordset(sqlQuery) 

    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adUseClient = 3
    
    Dim objConnection, objRecordset
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    Set clientRecordset = CreateObject("ADODB.Recordset")
    objConnection.Open _
    "{connection properties}"
    
    
    clientRecordset = objConnection.Execute(sqlQuery) ' Error: operation is not allowed when object is closed
    clientRecordset = objRecordset.Open sqlQuery, objConnection, adOpenStatic, adLockOptimistic 'Error: Expected end of statement 
    objRecordset.close()
    objConnection.close()
    SET objRecordset = Nothing
    Set objConnection = Nothing
End Function

Answer 1

I have used something like this in production for years. Once I retrieve the RecordSet I loop over it and concatenate it into a String using a Field delimiter (FDel) and a Row delimiter (RDel) and split them into an array as needed after.

objRecordSet.Open(Query, objConnection, adOpenStatic, adLockOptimistic)
objRecordSet.MoveFirst
Dim LCount = 0
Dim RCount = objRecordSet.RecordCount
Dim FCount = objRecordSet.Fields.Count
Dim FDel = "|"
Dim RDel = "$"
Dim Rows = ""
Do Until LCount = RCount
    For i = 0 To (FCount - 1)
        Fields = ""
        Fields = objRecordSet.Fields.Item(i)
        Rows = Rows & Fields & FDel
        Next
        Rows = Rows & RDel
        objRecordSet.MoveNext
        LCount = LCount + 1
    Loop
    Output = Rows