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?
Hi Bob,
I’m no DB expert, I’ve mostly called saved quries, and thought of these as a poor mans SP. What is the advanage of doing it this way?#
Also:
>>o 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.
How would that work/what would it look like? 1 mdb and 2 xls/xla?
Thanks
Ross
I normally build all the Queries in Access and call them from Excel using a simple Select* From QueryName
Whats the advantage of this over SP
On the advantages, you have no inline SQL, so no chance of code injection. Probably isn’t a real issue with saved queries,but as I said, I don’t use the Access GUI, and it becomes simpler to move up to a real database (SQL Server) when Access fails on you.
@Ross, if you are asking about the creator app, watch this space, I have a blog in the pipeline for that.