Programming Samples

Click here to go to:

Excel VBA
MS Access
T-SQL
SSIS
SSRS
Crystal Reports
C# Code
ASP .NET Code
Oracle PL/SQL

Back to Home Page

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.

SQL Query Updated SQL Query used as Recordset in 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.

Subform Warning

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.

Access Form

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

Continuous Form

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.

Design View TextBox Properties

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.

Form View