MS Access Using SQL Stored Procedure
SQL Stored Proc as Access Form Recordset
This article will describe how to programmatically connect to a SQL Server Stored Procedure that returns a "table" and set it as the Recordset for a form to use.
Calling the SQL Stored Procedure in MS Access
SQL Server Stored Procedures can be called via MS Access SQL Passthrough or SQL Execute queries via DAO (to set the SQL code as shown below).
Private Sub Form_Load()
Dim qry As QueryDef
Dim rs as Recordset
Set qry = CurrentDb.QueryDefs("SQL_QueryData")
qry.SQL = "EXEC dbo.SalesRatings " & Forms!frmSalesPeople.SalesPersonID
Set rs = qry.OpenRecordset
rs.LockEdits = True
Set Me.Recordset = rs
qry.Close
rs.Close
Exit Sub
While this code will work and update the query, it will not work when this updated SQL query is used as a Recordset for a Subform.
The error message below that pops up (when the parent form is run) is not accurate (as it refers to a crosstab query when the query used is a SQL Data Definition Query), but it does convey that MS Access does not like what you are attempting to do in linking a subform tied to a SQL Stored Procedure.
This article shows 2 other methods of setting form data to a stored procedure.
Using SQL Stored Procedure in an MS Access Continuous Form
In SQL Server, the Stored Procedure is created in the AdventureWorks Database. It uses the view Sales.vSalesPerson in a temp table and the modifies the temp table with updates based on meeting a sales quota and then getting the Max SalesYTD - derived query - to get the Sales Leaders for each territory.
CREATE PROC dbo.SalesRatings
AS
BEGIN
SET NOCOUNT ON;
select SalesPersonID,
FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName as FName,
TerritoryGroup, SalesQuota, SalesLastYear,
CONVERT(varchar(50),null) as Leader, CONVERT(varchar(150),null) as Quota
INTO #tmpData
from Sales.vSalesPerson
UPDATE #tmpData
SET Quota = 'Met Quota'
WHERE #tmpData.SalesPersonID in
(SELECT SalesPersonID FROM Sales.vSalesPerson WHERE
ISNULL(SalesQuota,0)<ISNULL(SalesLastYear,0))
UPDATE #tmpData
SET Quota = 'Not Met Quota'
WHERE #tmpData.SalesPersonID in
(SELECT SalesPersonID FROM Sales.vSalesPerson WHERE ISNULL(SalesQuota,0)>ISNULL(SalesLastYear,0))
UPDATE #tmpData
SET Leader = TerritoryGroup + '-Leader'
WHERE SalesPersonID in
(SELECT SalesPersonID FROM Sales.vSalesPerson v inner join
(SELECT TerritoryGroup, Max(SalesYTD) mYTD FROM Sales.vSalesPerson GROUP BY TerritoryGroup) a
on
a.TerritoryGroup=v.TerritoryGroup
WHERE mYTD <= SalesYTD)
SELECT * FROM #tmpData
DROP TABLE #tmpData
END
A blank MS Access form is created with TextBox controls named for the columns returned by the temp table in the SQL Stored Procedure.
The following code is added to the formLoad event. It includes an ADODB Connection Object and an ADODB Command Object as well as a ADODB Recordset Object to assign to the form.
Private Sub Form_Load()
On Error GoTo errbox
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
cn.ConnectionString = "Provider=MSDataShape;Data Provider=SQLOLEDB;SERVER=YOURSQLSERVER;DATABASE=AdventureWorks;Integrated Security=SSPI"
cn.Open
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With cmd
.ActiveConnection = cn
.CommandText = "dbo.SalesRatings"
.CommandType = adCmdStoredProc
.Parameters.Refresh
End With
With rs
.ActiveConnection = cn
.CursorType = adOpenForwardOnly
.CursorLocation = adUseServer
End With
Set rs = cmd.Execute
Set Me.Recordset = rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
errbox:
If Err.Number > 0 Then
MsgBox Err.Description & " " & Err.Number
Exit Sub
End If
End Sub
Using SQL Stored Procedure as Recordset in a MS Access Single Form
Alternate Code using just the Connection Object and the Recordset Object
Private Sub Form_Load()
On Error GoTo errbox
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=MSDataShape;Data Provider=SQLOLEDB;SERVER=YOURSQLSERVER;
DATABASE=AdventureWorks;Integrated Security=SSPI"
cn.Open
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.CursorType = adOpenForwardOnly
.CursorLocation = adUseServer
End With
Set rs = cn.Execute("EXEC dbo.SalesRatings")
Set Me.Recordset = rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
errbox:
If Err.Number > 0 Then
MsgBox Err.Description & " " & Err.Number
Exit Sub
End If
End Sub
Getting a Subform to work using the Stored Procedure as the Recordset - Using a single form means we need a modified version of the stored procedure that will deliver only 1 record to the subform.
CREATE PROC dbo.SalesRatingsSingle
@salesPersonID int=null
AS
BEGIN
SET NOCOUNT ON;
select SalesPersonID,
FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName as FName,
TerritoryGroup, SalesQuota, SalesLastYear,
CONVERT(varchar(50),null) as Leader, CONVERT(varchar(150),null) as Quota
INTO #tmpData
from Sales.vSalesPerson
WHERE SalesPersonID = @salesPersonID
UPDATE #tmpData
SET Quota = 'Met Quota'
WHERE #tmpData.SalesPersonID in
(SELECT SalesPersonID FROM Sales.vSalesPerson WHERE
ISNULL(SalesQuota,0)<ISNULL(SalesLastYear,0))
UPDATE #tmpData
SET Quota = 'Not Met Quota'
WHERE #tmpData.SalesPersonID in
(SELECT SalesPersonID FROM Sales.vSalesPerson WHERE ISNULL(SalesQuota,0)>ISNULL(SalesLastYear,0))
UPDATE #tmpData
SET Leader = TerritoryGroup + '-Leader'
WHERE SalesPersonID in
(SELECT SalesPersonID FROM Sales.vSalesPerson v inner join
(SELECT TerritoryGroup, Max(SalesYTD) mYTD FROM Sales.vSalesPerson GROUP BY TerritoryGroup) a
on
a.TerritoryGroup=v.TerritoryGroup
WHERE mYTD <= SalesYTD)
SELECT * FROM #tmpData
DROP TABLE #tmpData
END
In the Subform, add textboxes with the name and controlsource of the columns in the stored procedure. Do not set the Link Child Fields and the Link Master Fields for the subform.
Set the code in the Parent Form to execute the Stored Procedure and set the Subform Recordset.
Private Sub Form_Current()
On Error GoTo errbox
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
cn.ConnectionString = "Provider=MSDataShape;Data Provider=SQLOLEDB;SERVER=YOURSQLSERVER;DATABASE=AdventureWorks;Integrated Security=SSPI"
cn.Open
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With cmd
.ActiveConnection = cn
.CommandText = "SalesRatingsSingle"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@salesPersonID") = Forms!frmSalesPeoples.SalesPersonID
End With
With rs
.ActiveConnection = cn
.CursorType = adOpenForwardOnly
.CursorLocation = adUseServer
End With
Set rs = cmd.Execute
Set [Forms]![frmSalesPeoples]![frmQuota].[Form].Recordset = rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
errbox:
If Err.Number > 0 Then
MsgBox Err.Description & "-" & Err.Number
Exit Sub
End If
End Sub
Run the Form to see that the data was populated.
