SUMPRODUCT Isn’t The Only SP Kid In Town









If you are a frequent use of SQL Server, or any such enterprise database solution, you are bound to be aware of Stored Procedures (SPs) and the advantages that they bring. In my experience, using SPs is almost de-facto in the SQL Server world.


 


However, it does not seem so prevalent in the Access databases and applications that I have seen. Presumably, this is because Access applications are very closely bound to the Access database, and the rationale drops off somewhat. But I never use the Access UI myself, although I have created many applications where Access is my database with Excel serving as the client interface. In these applications, I communicate with the database using ActiveX Data Objects (ADO).


 


When I first started using Access databases, I could not see how to create SPs in Access, indeed as I recall, it was not initially possible. This meant that I had a ton of SQL code in my applications for communicating with the database, which is not where in belongs according to my design principles. There are queries in Access that can be called, but as I said, I don’t use the Access UI, and I also want to pass parameters to my SP.


 


Access does now support SPs. I am not sure from which version, but it is certainly true form Access 2003 onwards.


 


Suppose we had a simple database list that we wanted to use in a SP, SQL that looks like this


 


SELECT   RU.LoginID,
         RU.UserName,
         RU.Supervisor,
         RU.UserReports,
         RU.Salesman,
         RU.Deleted
FROM   refUsers AS RU
ORDER BY RU.UserName ASC;”


 


Assuming this SP is called spListUsers, the Excel code to bring this list back to a Recordset would be


 


Dim mpConnection As Object
Dim mpCommand As Object
Dim mpRS As ObjectDim mpConnection As Object
Dim mpCommand As Object
Dim mpRS As Object

    Set mpConnection = CreateObject(“ADODB.Connection”)
    mpConnection.Open = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
                        “Data Source=” & mcDatabase

    Set mpCommand = CreateObject(“ADODB.Command”)
    Set mpCommand.ActiveConnection = mpConnection
    mpCommand.CommandText = “spListUsers”
    mpCommand.CommandType = adCmdStoredProc

    Set mpRS = CreateObject(“ADODB.Recordset”)
    mpRS.Open mpCommand


 


And the great thing is that it is possible to create these SPs from within Excel using ADO. All that is needed is this code


 


Dim mpConnection As Object
Dim mpCommand As Object

    Set mpConnection = CreateObject(“ADODB.Connection”)
    mpConnection.Open = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
                        “Data Source=” & mcDatabase

    On Error Resume Next
        mpConnection.Execute “DROP PROCEDURE spListUsers”
    On Error GoTo 0
    mpConnection.Execute “CREATE PROCEDURE spListUsers ” & _
            “AS “ & _
            “SELECT RU.LoginID, “ & _
                  RU.UserName, “ & _
                   RD.DesignerID, “ & _
                   RU.Supervisor, “ & __
                   RU.UserReports, “ & _
                   RU.Salesman, “ & _
                   RU.Deleted “ & _
            “FROM   refUsers AS RU “ & _
            “ORDER BY RU.UserName ASC;”


 


Introducing parameters adds a certain level of complexity, but nothing too troublesome. The parameter has to be declared in the code to create the SP, and it has to be passed when executing the SP.


 


Creating an SP, spGetUser in this example, with a text parameter called prmUserName, would look something like this


 


Dim mpConnection As Object
Dim mpCommand As Object

    Set mpConnection = CreateObject(“ADODB.Connection”)
    mpConnection.Open = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
                        “Data Source=” & mcDatabase

    On Error Resume Next
        mpConnection.Execute “DROP PROCEDURE spGetUser
    On Error GoTo 0



     mpConnection.Execute “CREATE PROCEDURE spGetUser “ & _
        “(prmUserName Varchar (50)) “ & _
        “AS “ & _
        “SELECT RU.LoginID, “ & _
              RU.UserName, “ & _
               RD.DesignerID, “ & _
               RU.Supervisor, “ & __
               RU.UserReports, “ & _
               RU.Salesman, “ & _
               RU.Deleted , “ & _
               RU.UpdatedBy, “ & _
               RU.UpdatedOn “ & _
        “FROM   refUsers AS RU“ & _
               LEFT JOIN refDesigners AS RD“ & _
               ON RU.LoginID = RD.LoginID“ & _
        “WHERE  RU.UserName = prmUserName;”


 


And calling it would look like this


 


Dim mpConnection As Object
Dim mpCommand As Object
Dim mpParameter As Object
Dim mpRS As Object

    Set mpConnection = CreateObject(“ADODB.Connection”)
    mpConnection.Open = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
                        “Data Source=” & mcDatabase

    Set mpCommand = CreateObject(“ADODB.Command”)
    mpCommand.ActiveConnection = mpConnection

    mpCommand.CommandText = “spGetUser”
    mpCommand.CommandType = adCmdStoredProc

    Set mpParameter = mpCommand.CreateParameter(“prmUserName”, _
                                                adVarChar, _
                                                adParamInput, _
                                                Len(“Bob Phillips”)) ‘the parameter value
    mpParameter.Value = “Bob Phillips”
    mpCommand.Parameters.Append mpParameter

    Set mpRS = CreateObject(“ADODB.Recordset”)
    mpRS.Open mpCommand


 


If your SP requires multiple parameters, each parameter is defined separately, loaded and appended as in the single example above.


 


Clearly, your SPs are likely to be more complex than these trivial examples, but the principle holds out with more complex SPs.


 


So there you have it, you can remove all of the inline SQL from your applications, create a separate SP creator app that creates the SPs, have better structured code, and more maintainable. Does it get any better than that?


 

Typing May Be Good For You



I have written previously about User Defined Types, where I showed what I feel is a good, legitimate use of UDTs, namely the saving of application settings at the start of my procedures, then re-instating them at the end. I have since extended that technique to make it more generic, as follows


Public Type ApplicationValues
    AppEnableEvents As Boolean
    AppScreenUpdating As Boolean
    AppDisplayAlerts As Boolean
    AppCalculation As XlCalculation
End Type

Public Function DoSomeStuff()
Dim myAppSettings As ApplicationValues

    Call AppSettings(State:=”Set”, _
                AppType:=mpAppSettings, _
                AppEvents:=True, _
                AppScreen:=False, _
                AppAlerts:=False, _
                AppCalc:=-1)
    On Error GoTo func_error

 … main code
 
 func_exit:
    Call AppSettings(State:=”Reset”, _
                    AppType:=mpAppSettings, _
                    AppEvents:=True, _
                    AppScreen:=False, _
                    AppAlerts:=False, _
                    AppCalc:=-1)

func_error:
    ‘your error handler
    Resume func_exit
End Function


Public Function AppSettings( _
   ByVal State As String, _
   ByRef AppType As ApplicationSettings, _
   ByVal AppEvents As Boolean, _
   ByVal AppScreen As Boolean, _
   ByVal AppAlerts As Boolean, _
   ByVal AppCalc As XlCalculation) As Boolean

   With AppType

       Select Case State

           Case “Set”
                If AppEvents Then

                   .AppEnableEvents = Application.EnableEvents
                   Application.EnableEvents = False
               End If
               If AppScreen Then

                   .AppScreenUpdating = Application.ScreenUpdating
                   Application.ScreenUpdating = False
               End If
               If AppAlerts Then

                   .AppDisplayAlerts = Application.DisplayAlerts
                   Application.DisplayAlerts = False
               End If
               If AppCalc <> appNotEnabled Then

                   .AppCalculation = Application.Calculation
                   Application.Calculation = AppCalc
               End If

           Case “Reset”
               If AppEvents Then Application.EnableEvents = .AppEnableEvents
               If AppScreen Then Application.ScreenUpdating = .AppScreenUpdating
               If AppAlerts Then Application.ScreenUpdating = .AppScreenUpdating
               If AppCalc <> appNotEnabled Then Application.Calculation = .AppCalculation
       End Select
   End With
End Function


That in itself was a nice improvement for my code, but recently I have started to use UDTs more frequently, in the way that they were probably meant for, creating data types.


I have found myself frequently creating datasets, so I created UDTs to cover this. For this sake of this discussion, I will cover one specific type in my application, User data.


I began by creating a UDT like so


PublicType AppUser
   LoginID As String
   Name As String
   DesignerId As Long
   Supervisor As Boolean
   UserReports As Boolean
   NewSalesTarget As Double
   RepeatsTarget As Double
   Deleted As Boolean
   UpdatedBy As String
   UpdatedOn As Date
End Type   


This was fine, it worked well, but in reality my user data was much larger, there were more permission types, and more targets. As such, I found that I was still working through each variable individually. I also use classes a lot, so my user class had every data item as a separate property. Although I could pass the UDT around my application quite well, I still had to refer to every item separately, define it everywhere.


I then had an idea. Why not create another UDT, for permissions say, and nest them. The UDT definitions would then look like this


Public Type AppPermissions
   Supervisor As Boolean
   UserReports As Boolean
End Type

Public Type AppUser
   LoginID As String
   Name As String
   DesignerId As Long
   Permissions As AppPermissions
   NewSalesTarget As Double
   RepeatsTarget As Double
   Deleted As Boolean
   UpdatedBy As String
   UpdatedOn As Date
End Type


Then I can use the familiar dot notation to refer to it, for instance


Dim myUser As AppUser

   MsgBox myUser.Permissions.Supervisor


In my class, it simplifies code. So instead of this,


Private mcSupervisor As Boolean
Private mcUserReports As Boolean

Public Property Get Supervisor() As Boolean
   Supervisor = mcSupervisor
End Property
Public Property Let Supervisor(ByVal Status As Boolean)
   mcSupervisor = Status
End Property

Public Property Get UserReports() As Boolean
   UserReports = mcUserReports
End Property
Public Property Let UserReports(ByVal Status As Boolean)
   mcUserReports = Status
End Property


I now have the UDTs shown above defined in a standard module, and in my class module


Private mcPermssions As AppPermissions

Public Property Get Permissions() As AppPermissions
   Permissions = mcPermissions
End Property
Public Property Let Permissions(ByRef Values As AppPermissions)
   mcPermissions = Values
End Property


[Note, that for UDTs, the argument must be passed ByRef]


In itself this is much cleaner and clearer in my view, but it gets better when you use it. To set the permissions, the code would be similar to


Dim myUser As User

    Set MyUser = New User
    With myUser

        .LoginID = “Bob”
        .UserName = “Bob Phillips”
        .Permissions.Supervisor = True
        .Permissions.UserReports = True
        ‘etc
    End With


or even


Dim myUser As User

    Set MyUser = New User
    With myUser

        .LoginID = “Bob”
        .UserName = “Bob Phillips”
        With .Permissions

             .Supervisor = True
             .UserReports = True
        End With

         ‘etc
    End With


As you can see, we are now breaking the user object down into properties which are objects in their own right, and using the dot notation to navigate down to the actual property, just as the Excel object does. This provides clarity and an element of object documentation within your code. Intellisense is retained, and is actually better because it is focussed.


In reality, I have nested even further as I have a Reports UDT which is nested within my permissions UDT, so the full definition is as follows, first the standard module UDT definitions, then the user class properties


‘UDT Definitions
Public Type AppReports
   TaskList As Boolean
   FollowUp As Boolean
   TotalQuotes As Boolean
   Dashboard As Boolean
   Commission As Boolean
End Type

Public Type AppPermissions
   Supervisor As Boolean
   Reports As AppReports
End Type

Public Type AppSalesTargets
   SalesGoal As Double
   BonusGoal As Double
   GPMGoal As Double
   ClosingRatio As Double
End Type

Public Type AppUser
   LoginID As String
   Name As String
   DesignerId As Long
   Permissions As AppPermissions
   Targets As AppSalesTargets
   Deleted As Boolean
   UpdatedBy As String
   UpdatedOn As Date
End Type


[Note that UDTs defined within another UDT have to be defined first, no forward referencing allowed]


‘Class Properties
Private mcUser As AppUser
Private mcPermissions As AppPermissions
Private mcTargets As AppSalesTargets

Public Property Get LoginID() As String
   LoginID = mcUser.LoginID
End Property
Public Property Let LoginID(ByVal Id As String)
   mcUser.LoginID = Id
   Call SetUserDetails(True)
End Property

Public Property Get UserDetails() As AppUser
   UserDetails = mcUser
End Property
Public Property Let UserDetails(ByRef Values As AppUser)
   mcUser = Values
End Property

Public Property Get Permissions() As AppPermissions
   Permissions = mcPermissions
End Property
Public Property Let Permissions(ByRef Values As AppPermissions)
   mcPermissions = Values
End Property

Public Property Get Targets() As AppSalesTargets
   Targets = mcTargets
End Property
Public Property Let Targets(ByRef Values As AppSalesTargets)
   mcTargets = Values
End Property


I always find the mass of Get/Let property definitions an impediment when reading class code, albeit a minor impediment, but an impediment all the same. Using this approach removes that code clutter whilst fully retaining the application functionality.


This approach fits perfectly with my beliefs in strong, structured coding style, making sure that the code is maintainable as well as functioning correctly.


The only real disadvantage that I can see is that the UDTs are not defined within the class itself, where they really belong. We could define Private UDTs in the class, but then they would not be available within the main application code. We could define Private UDTs in the class, and Public UDTs in a standard module, but this would be rather pointless. This is one of the main failings of UDTs in my view, one that has stopped me using them to any great degree before. With this approach, I envisage using them far more (I may be slow Ross, but I catch on eventually….).