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

expertatexcel.com

 


Home
What can Excel do?
Learning Excel Books
Basic concepts Immediate window Macros 101 Syntax Use existing code Hello world Math formulas Functions Modifying Recorded Macros
Selecting Moving data Formatting Clipboard Range names Filters Sorting Fill Combining Hyperlinks Comments Options Vlookup Conditional Formatting Data validation Pivot Tables Charts Charts
Debugging Strings Active This vba statements VBA functions Run stuff
Workbook Sheet Cells Rows Column Range Selection Methods Properties Events Collections Chart
Variables Data types variables/scope Conditionals Loops functions/sub Arrays testing Batch Software dev techniques Backup/Recovery Documentation Wrappers webservice Refactor
#Errors Problems Too Slow/Optimize Error handling Problem Solving Method Someone else's sheet complex formula
Date/times Code Generation Random Shortcuts Forms Special Chars Customize your training Work and learn
Reporting Sample projects Reconcliation Adjusting data Generate Slides Special Characters
Outlook Web Database Files PPT Word API Objects Script host Dos Bloomberg JIRA Input Spreadsheets or files
Stocks Bitcoin Sports Job Productivity Project Management Tracking Health Games Programmer

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