SUMPRODUCT Isn't The Only SP Kid In Town

Bob Phillips — Fri, 19 Feb 2010 18:12:00 GMT

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

     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?

Excel Do, Dynamic Does : Excel, Access

SUMPRODUCT Isn't The Only SP Kid In Town