Using a Stored Procedure as a MS Access form Recordsource

Question

Right now, I have a database, with a SQL Server backend, and a MS Access frontend. On one form, my users are experiencing slow load times for it to display. The form essentially looks up to see who the user is (based on their login ID, this part works), sees their access rights (readonly, v. update, etc.; this also works), and then pulls the projects they are allowed to see based on their access rights (this is the part I believe is running slow).

To make this form load faster, I feel that moving the last part, the part that pulls the projects they are allowed to see, to an SSMS Stored Procedure should make the form faster to load.

I have the stored procedure written, in SSMS, and I have the code on the 'Form Load' event that calls the stored procedure. The issue I am facing is having the results of the stored procedure become the Recordsource of the form. I've tried Me.RecordSource, but that doesn't seem to be working. Below is a copy of the code that calls the Stored Procedure:

This is different than having code to call an SP, because I already have the code to call it, however, I need the results of the SP to be the recordsource of an MS Access form.

Any help, or ideas will be appreciated! (e.g. I thought of trying to use a temp table to serve as the recordsource...)

Dim rs1 As ADODB.Recordset

Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter

cn.ConnectionString = "DRIVER=SQL Server;SERVER=SERVERNAME;APP=Microsoft     Office XP;WSID=MYCOMPUTER;DATABASE=dbname;Trusted_Connection=Yes;"

cn.Open


Set cmd = New ADODB.Command
Set rs1 = New ADODB.Recordset
With cmd
    .ActiveConnection = cn
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.ProcProjectSelection"
    Set prm = .CreateParameter("@xID", adVarChar, adParamInput, 10, Me.txtNetworkID)
    .Parameters.Append prm
        End With

If I put: Set Me.RecordSource = rs1 Before the "end with", I get an Error Message saying "Invalid Use of Property"

Possible duplicate of [MS Access call SQL Server stored procedure](http://stackoverflow.com/questions/18800727/ms-access-call-sql-server-stored-procedure) — M.Hassan, Jul 27 '16 at 18:26
This is different than having code to call an SP, because I already have the code to call it, however, I need the results of the SP to be the recordsource of an MS Access form. — revelations, Jul 27 '16 at 19:15
May be the parameter is not valid.Can you test the stored procedure with same parameters as pass through query. — M.Hassan, Jul 28 '16 at 23:08

score 1 · Answer 1 · edited Jan 30 '23 at 14:14

Solution 1:

Assign the Recordset returned from stored procedure to Form.Recordset Object

add this line at the end of your code

Set Me.Recordset = cmd.Execute

Solution 2:

You can create pass-through query based on ODBC data source, and use it as record source

Then, in the property sheet of the form , set the properties:

record source = your_pass_through_query
recordset type = dynamic

Edit:

My advice use solution 2 to avoid many setting for ado and runtime errors

Example: Pass through using northwind , and stored procedure

Bind your controls in the form with the field name of result set of the query

Private Sub Form_Open(Cancel As Integer)
   test
End Sub

Sub test()
   Dim qdf As DAO.QueryDef, rst As DAO.Recordset
   Set qdf = CurrentDb.CreateQueryDef("")
   qdf.Connect = "ODBC;Driver=SQL Server;Server=xxxx;database=northwind;Trusted_Connection=Yes;"
 
   ' stored procedure with paramete
   qdf.SQL = "exec [CustOrderHist] 'ALFKI'"

   qdf.ReturnsRecords = True
   Set rst = qdf.OpenRecordset
 
   Set Me.Recordset = rst
End Sub

Thanks for the help...I appreciate it. When I run it, I get no results, in my form (and when I go to Design View/Properties, recordsource is blank). I do expect to get results when I run it. (This is with solution 1). — revelations, Jul 28 '16 at 19:17
I advice use solution 2 , for you can use recordsource property. i edit my answer for example. — M.Hassan, Jul 29 '16 at 18:18

score 1 · Answer 2 · answered Apr 12 '19 at 18:19

Solution: Use a Pass through Query to get data from your Stored Procedure into a SubForm

Add the following code to your SubForm RecordSource field
Select * from YourPassThruQueryName

In your Form_Load()
Dim qdf as DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("YourPassThruQueryName") ' If it exist
Set qdf = CurrentDb.CreateQueryDef("YourPassThruQueryName") ' If you have to create one

You must have a default Select statement so use something like:
qdf.sql = "Select * from anyTable where 0=1"
Set qdf = Nothing

Important!
You must use the following code to refresh your subform, as requery will not work:
Me.frm_Your_Sub_Form_Name.SourceObject = "frm_Your_Actual_Sub_Form_Name"

Final!
Use the the following to update the subform when you make your selection that's needed for your Stored Procedure in your Submit button:

Private Sub btn_Submit_Click()
Dim rs_tmp as DAO.Recordset
Dim qdf as DAO.QueryDef
Dim sp as String
sp = "EXEC DBO.USP_Your_SQL_Server_Stored_Procedure_Name '" & prevdate & "','" & currdate & "'"
Set qdf = CurrentDb.QueryDefs("YourPassThruQueryName")
qdf.Connect = adoConnectionString ' This is your Connection string for SQL Server
qdf.sql = sp
qdf.ReturnsRecords = True
Set rs_tmp = qdf.OpenRecordset
If Not rs_tmp.EOF Then
Me.frm_Your_Sub_Form_Name.SourceObject = "frm_Your_Actual_Sub_Form_Name"
rs_tmp.Close
End If
Set rs_tmp = Nothing
Set qdf = Nothing
End Sub

Using a Stored Procedure as a MS Access form Recordsource

2 Answers2

Linked