VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "clsDataAccess"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Compare Database
Option Explicit

' for input/output parameters, use addParameterToCollection function as follows:
'    Dim dcDataAccess As clsDataAccess, rs As Recordset, collParam As Collection
'    Set collParam = New Collection
'    Set dcDataAccess = New clsDataAccess
'    dcDataAccess.addParameterToCollection collParam, "{parameter name WITHOUT @ symbol prepended}", adVarChar, adParamInput, 255, strID
'    Set rs = dcDataAccess.rsSPwithParams({connection string}, "{stored procedure name}", collParam)

Function addParameterToCollection(ByRef collParameters As Collection, _
                                                                pName As String, _
                                                                Optional pType As DataTypeEnum = adEmpty, _
                                                                Optional pDirection As ParameterDirectionEnum = adParamInput, _
                                                                Optional pSize As Long, _
                                                                Optional pValue As Variant)
    Dim cmd As New ADODB.Command, param As ADODB.Parameter
    Set param = cmd.CreateParameter(pName, pType, pDirection, pSize, pValue)
    collParameters.Add param
End Function

' generic function returns a recordset from a parameterized stored procedure
Function rsSPwithParams(strConnection As String, strProcName As String, collParam As Collection) As Recordset
    Dim cmd As New ADODB.Command
    Dim param As New ADODB.Parameter
    With cmd
        .CommandText = strProcName
        .CommandType = adCmdStoredProc
        .ActiveConnection = strConnection
        For Each param In collParam
            .Parameters.Append param
        Next param
        Set rsSPwithParams = .Execute
    End With
End Function

' this generic function adds cursor control;
' if CursorLocation is adUseClient, 
' the returned recordset will have its ActiveConnection property set to CurrentProject.Connection
' allowing it to be assigned to an Access form's Recordset property
Function rsSPwithParamsAndCursor(strConnection As String, strProcName As String, collParam As Collection, enumCursorLocation As CursorLocationEnum) As Recordset
    Dim cmd As New ADODB.Command
    Dim param As New ADODB.Parameter
    Dim rs As New ADODB.Recordset
    rs.CursorLocation = enumCursorLocation
    With cmd
        .CommandText = strProcName
        .CommandType = adCmdStoredProc
        .ActiveConnection = strConnection
        .ActiveConnection.CursorLocation = enumCursorLocation
        For Each param In collParam
            .Parameters.Append param
        Next param
        
        Set rs = .Execute
        If rs.CursorLocation = adUseClient Then
            Set rs.ActiveConnection = CurrentProject.Connection
        End If
        Set rsSPwithParamsAndCursor = rs
    End With
End Function

' use this function to retrieve a value from a local MS Access table
Function strValFromLocalTable(strTableName As String, strUniqueID As String, strUniqueIDField As String, strValField As String) As String
    Dim conn As Connection, strSQL As String, rs As Recordset
    strSQL = "SELECT " & strValField & " AS returnval FROM " & strTableName & " WHERE " & strUniqueIDField & " = " & strUniqueID & ""
    Set conn = CurrentProject.Connection
    Set rs = conn.Execute(strSQL)
    If Not rs.BOF And Not rs.EOF Then
        strValFromLocalTable = rs!returnVal
    Else
        strValFromLocalTable = ""
    End If
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Function

' generic function returns a collection of output parameters from a parameterized stored procedure
Function collSPWithIOParams(strConnection As String, strProcName As String, collInputParam As Collection, collOutputParam As Collection) As Collection
    Dim cmd As New ADODB.Command
    Dim param As New ADODB.Parameter
    With cmd
        .CommandText = strProcName
        .CommandType = adCmdStoredProc
        .ActiveConnection = strConnection
        For Each param In collInputParam
            .Parameters.Append param
        Next param
        For Each param In collOutputParam
            .Parameters.Append param
        Next param
        .Execute , , adExecuteNoRecords
    End With
    Dim collRetOutputParams As New Collection
    For Each param In collOutputParam
        collRetOutputParams.Add param.Value, param.Name
    Next param
    Set collSPWithIOParams = collRetOutputParams
End Function

' this function should be deprecated by generic collSPWithIOParams function
Function intSPUpdateWithThreeParamsAndOutput(strConnection As String, _
                                                                                strProcName As String, _
                                                                                strParamName1 As String, strParam1 As String, _
                                                                                strParamName2 As String, strParam2 As String, _
                                                                                strParamName3 As String, strParam3 As String, _
                                                                                strOutputParamName As String) As Integer
    Dim cmd As New ADODB.Command
    Dim param1 As New ADODB.Parameter
    Dim param2 As New ADODB.Parameter
    Dim param3 As New ADODB.Parameter
    Dim outputparam As New ADODB.Parameter
    With cmd
        .CommandText = strProcName
        .CommandType = adCmdStoredProc
        .ActiveConnection = strConnection
        Set param1 = .CreateParameter(strParamName1, adVarChar, adParamInput, 255, strParam1)
        .Parameters.Append param1
        Set param2 = .CreateParameter(strParamName2, adVarChar, adParamInput, 255, strParam2)
        .Parameters.Append param2
        Set param3 = .CreateParameter(strParamName2, adVarChar, adParamInput, 255, strParam3)
        .Parameters.Append param3
        Set outputparam = .CreateParameter(strOutputParamName, adInteger, adParamOutput)
        .Parameters.Append outputparam
        .Execute , , adExecuteNoRecords
    End With
    intSPUpdateWithThreeParamsAndOutput = cmd.Parameters(strOutputParamName)
End Function

' THIS FUNCTION NEEDS TO GO AWAY
Function SPUpdateWithTwoParams(strConnection As String, strProcName As String, strUniqueID As String, strParam1 As String, strParam2 As String) As Recordset
    Dim conn As Connection
    Set conn = New Connection
    conn.ConnectionString = strConnection
    conn.Open
    Set SPUpdateWithTwoParams = conn.Execute(strProcName & " '" & strUniqueID & "', '" & strParam1 & "', '" & strParam2 & "'")
    Set conn = Nothing
End Function
Function OLD_SPInsertWithTwoParams(strConnection As String, strProcName As String, strParam1 As String, strParam2 As String) As Recordset
    Dim conn As Connection
    Set conn = New Connection
    conn.ConnectionString = strConnection
    conn.Open
    'Set SPInsertWithTwoParams = conn.Execute(strProcName & " '" & strParam1 & "', '" & strParam2 & "'")
    Set conn = Nothing
End Function
Function intSPInsertWithTwoParamsAndOutput(strConnection As String, _
                                                                                strProcName As String, _
                                                                                strParamName1 As String, strParam1 As String, _
                                                                                strParamName2 As String, strParam2 As String, _
                                                                                strOutputParamName As String) As Integer
    Dim cmd As New ADODB.Command
    Dim param1 As New ADODB.Parameter
    Dim param2 As New ADODB.Parameter
    Dim outputparam As New ADODB.Parameter
    With cmd
        .CommandText = strProcName
        .CommandType = adCmdStoredProc
        .ActiveConnection = strConnection
        Set param1 = .CreateParameter(strParamName1, adVarChar, adParamInput, 255, strParam1)
        .Parameters.Append param1
        Set param2 = .CreateParameter(strParamName2, adVarChar, adParamInput, 255, strParam2)
        .Parameters.Append param2
        Set outputparam = .CreateParameter(strOutputParamName, adInteger, adParamOutput)
        .Parameters.Append outputparam
        .Execute , , adExecuteNoRecords
    End With
    intSPInsertWithTwoParamsAndOutput = cmd.Parameters(strOutputParamName)
End Function
Function intSPDeleteWithTwoParams(strConnection As String, _
                                                                                strProcName As String, _
                                                                                strParamName1 As String, strParam1 As String, _
                                                                                strParamName2 As String, strParam2 As String, _
                                                                                strOutputParamName As String) As Integer
    Dim cmd As New ADODB.Command
    Dim param1 As New ADODB.Parameter
    Dim param2 As New ADODB.Parameter
    Dim outputparam As New ADODB.Parameter
    With cmd
        .CommandText = strProcName
        .CommandType = adCmdStoredProc
        .ActiveConnection = strConnection
        Set param1 = .CreateParameter(strParamName1, adVarChar, adParamInput, 255, strParam1)
        .Parameters.Append param1
        Set param2 = .CreateParameter(strParamName2, adVarChar, adParamInput, 255, strParam2)
        .Parameters.Append param2
        Set outputparam = .CreateParameter(strOutputParamName, adInteger, adParamOutput)
        .Parameters.Append outputparam
        .Execute , , adExecuteNoRecords
    End With
    intSPDeleteWithTwoParams = cmd.Parameters(strOutputParamName)
End Function