By using vba.booksticle.com you agree to our cookie policy, We and our partners operate globally and use cookies, for multiple purposes

948 Steps to 6 Figures by Learning Excel-VBA and Other Skills



Step 455 - GetRecodset (sSQL, Optional maxRecords) as ADODB.Recordset '



Function GetRecordset(sSQL As String, Optional cn, Optional QueryOrSQL, Optional p1, Optional p2, Optional p3, Optional p4) As ADODB.Recordset
Dim rs As New ADODB.Recordset
'Dim rs1 As New ADODB.Command
Dim cmd As New ADODB.Command

If IsMissing(cn) Then
    Set cn = gADO
End If

If IsMissing(QueryOrSQL) Then
    QueryOrSQL = "S"
End If


If cn Is Nothing Then
    'conADO.Open "Data Source=" & datasource
    Set cn = GetConnection()
'Else
'   Set conADO = ADO
End If




'myConnection.Open

' Determine if we conected.
If cn.State = adStateOpen Then
   'MsgBox "Welcome to the Biblio Database!"
Else
  'MsgBox "The connection could not be made."
  Set cn = GetConnection()
End If

If QueryOrSQL = "S" Then
    Set rs = cn.Execute(sSQL)
    Set GetRecordset = rs
    Exit Function
    
Else
    With cmd
        Set .ActiveConnection = cn
        .CommandText = sSQL
        .CommandType = adCmdStoredProc
        
        If IsMissing(p1) Then
                
        ElseIf IsMissing(p2) Then
            .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, Len(p1), p1)
        ElseIf IsMissing(p3) Then
            '2 parameters
            .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, Len(p1), p1)
            .Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, Len(p2), p2)
        ElseIf IsMissing(p4) Then
            .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, Len(p1), p1)
            .Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, Len(p2), p2)
            .Parameters.Append .CreateParameter("p3", adVarChar, adParamInput, Len(p3), p3)
            
        Else
            
            .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, Len(p1), p1)
            .Parameters.Append .CreateParameter("p2", adVarChar, adParamInput, Len(p2), p2)
            .Parameters.Append .CreateParameter("p3", adVarChar, adParamInput, Len(p3), p3)
            .Parameters.Append .CreateParameter("p4", adVarChar, adParamInput, Len(p4), p4)
        End If
    End With
    rs.Open cmd, , adOpenKeyset, adLockOptimistic
    Set GetRecordset = rs
    Exit Function
    'qtbData.Parameters(1) = p2
End If


'@todo handle timeout -2147467259
'On Error Resume Next

    'Set rs = cn.Execute(sSQL) '@todo: I got -2147467259 and [DataDirect][ODBC Sybase Wire Protocol driver]Timeout exceeded.
    


End Function

   Table of Contents | Send us your feedback | © 2025 All Rights Reserved | Edit