Can I use a SQL Procedure as an MS Access Forms Recordsource?

It seems like from google searches this is possible but so far I'm getting an error "Error Database6--> Object Required" when I try to execute the sub in access.

Here is the SQL that I'm trying right now:

Public Sub UpdateProc()

On Error GoTo ErrHandler:
 
 Dim objConn As New ADODB.Connection
 Dim objCmd As New ADODB.Command
 Dim objParm1 As New ADODB.Parameter
 Dim objRs As New ADODB.Recordset
 
 objCmd.CommandText = "[dbo].[spTest]"
 objCmd.CommandType = adCmdStoredProc
 
 objConn.CommandTimeout = 0
 objConn.Open "Driver={SQL Server};Server=SE10SQL0;Database=EpicorLive10;Uid=***;Pwd=***;"
 objCmd.ActiveConnection = objConn
 objCmd.CommandTimeout = 0
 objCmd.Parameters.Refresh
 Set objRs = objCmd.Execute
 Form_Form1.Recordset = objRs
 
 objRs.Close
 objConn.Close
 Set objRs = Nothing
 Set objConn = Nothing
 Set objCmd = Nothing
 Set objParm1 = Nothing
 Exit Sub
 
ErrHandler:
 If objRs.State = adStateOpen Then
 objRs.Close
 End If
 
 If objConn.State = adStateOpen Then
 objConn.Close
 End If
 
 Set objRs = Nothing
 Set objConn = Nothing
 Set objCmd = Nothing
 Set objParm1 = Nothing
 
 If Err <> 0 Then
 MsgBox Err.Source & "-->" & Err.Description, , "Error"
 End If


End Sub

Open in new window


Here is the Procedure from SQL Server:
USE [EpicorLive10]
GO
DROP PROCEDURE [dbo].[spTEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spTEST] 
AS
BEGIN
	SET NOCOUNT ON;
	SELECT JobNum FROM Erp.JobHead
END
GO

Open in new window


Thanks for any advice/help!
LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?
 
Jarred MeyerConnect With a Mentor Production ManagerAuthor Commented:
Sorry all, completely scrapped that method and went with a pass through query. Seems to be working well with this method for now.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You don't say where it fails, but in looking at it, the only thing I would change is in place of:

 Set objRs = objCmd.Execute
 Form_Form1.Recordset = objRs

do:

 Me.Recordset = objCmd.Execute


assuming this is executing in the forms module.  If the sub is not in the forms module, then I would pass an explicit reference in for the form.

Jim.
0
 
Jarred MeyerProduction ManagerAuthor Commented:
I just changed it to what you recommended. Still the same result. It fails at " Me.Recordset = objCmd.Execute"

This time my error is "Database6--> Object variable or With block variable not set"
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Do:

 objCmd.CommandText = "[dbo].[spTest]"

as:

 objCmd.CommandText = "spTest"

 and see if that works.

Jim.
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Changed that and same error, same spot.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
huh.....does your SP work in SSMS?

 Jim.
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
from:
http://oakdome.com/programming/MSAccess_StoredProcedureDataSource.php

it looks like the objRS needs an active connection as well.
0
 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
Try the following
set Form_Form1.Recordset = objRs

Open in new window

The recordset is an object, and as such you need to use SET.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Funny, I didn't even catch that; good catch!.    But even so, it should be done this way:

 objRS.Open objCmd,, adOpenKeySet, adLockOptimistic, adCmdStoredProc

 the open allowing you to specify the cursor type and locking.   I had to go back and look at some old stuff and this is the way I did it.  Digging back, it seems the default is a forward-only, read-only recordset if you use the Execute method.

 There's also the cursor location on the connection object, and can be client side (adUseClient) or server side (adUseServer).

 Also be aware that what you request might not be what you get back.   It depends on the provider used and whether it's JET's OLEDB, SQL's, or the MSDataShape server.    I don't remember all the in's and out's of that (been a few years actually) but there are something like 30 or more variations of recordsets that you can request, but that's not always what you get.   I have some notes on that somewhere.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Sorry all, completely scrapped that method and went with a pass through query. Seems to be working well with this method for now.>>

 Not a problem, just glad you have a solution.

 I typically use pass-through's with DAO myself.   I never really bit into ADO.

Jim.
1
 
Jarred MeyerProduction ManagerAuthor Commented:
Could not get the other method to work and got some assistance setting this up as a pass through query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.