SQL Stored Procedure as Recordset

Topic

New Reply

Hello,
I am having trouble with a SQL Server 2000 SP that accepts input parameters. I am using an Access 2007 accdb front end. I can run update and insert SP’s no problem, but cannot get select SP’s to return a record set. When doing a record count after running the code, I get a value of ‘-1’. Obviously, when I then try to set this as the recordset for the form, it fails (no error checking at the moment, but the issue is the SP is not returning any rows). I know the SP works as executing it with the same parameters from Enterprise Manager returns over 1000 records. The code I’m using is:

Code:

        Dim cnn As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim rs As ADODB.Recordset
        
        Set rs = New ADODB.Recordset
        Set cnn = New ADODB.Connection
        Set cmd = New ADODB.Command
        
        cnn.Open "Provider=sqloledb;Data Source=PLATINUM2SMNS;Initial Catalog=SMNS TEST;Integrated Security=SSPI;"

        With cmd
            Set .ActiveConnection = cnn
           .CommandType = adCmdStoredProc
           .CommandText = "qryClients_SQL"
           .Parameters.Append .CreateParameter("@Archive", adInteger, adParamInput, , 0)
           .Parameters.Append .CreateParameter("@CStatus", adVarChar, adParamInput, 10, "Active")
        End With

        rs.Open cmd, , adOpenStatic, , adCmdStoredProc
        MsgBox rs.RecordCount

        Set Forms!Form1.Recordset = rs

Note that I am hard-coding input parameters for testing purposes. Just for interest, the Stored Procedure is as follows:

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Gareth
-- Create date: 03-06-2010
-- Description:	Test select query
-- =============================================
ALTER PROCEDURE qryClients_SQL 
	@Archive int = 0,
	@CStatus varchar(10)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT Nursing_ID_Number, Surname, Given_Name
	FROM tblClientsCommon
	WHERE Archive = @Archive AND CStatus = @CStatus
	ORDER BY Surname ASC
	
END
GO

If I run the following code from Enterprise Manager I get values returned without any issues:

Code:

exec qryClients_SQL 0,"Active"

I’ve searched numerous different forums, tutorials and so forth and they all have variations on the above but I keep getting the same result – no returned rows. Moving to an ADP would solve pretty much all of these issues, but that is not an option in this database at present.

Anybody have any suggestions?

Thanks
toncc

Reply | Quote

Replies

Could you use a PASS THROUGH Query as the forms Recordsource,
and then just use code to change the passed parameters to the QueryDEF?

Added AW 19:23
The Query call in the pass through query would then be;

exec qryClients_SQL @Archive=0, @Status=’Active’

You could open the querydef easily in code and alter the parameters if necessary

Reply | Quote

I don’t see anything obviously wrong with what you are doing, but it’s not an approach I ever use. And I don’t think ADPs are the correct answer either – the support of ADPs is not being enhanced in Access 2010 – the preferred approach seems to be using an ODBC data source connection, which is what I normally do. Is there a specific reason why you want to use the stored procedure approach and set the record source for the form dynamically? I would probably link to the table tblClientsCommon, and then use a query with parameters – perhaps by modifying the querydef on the fly. We routinely do that sort of thing from tables with 500K records, and get subsecond response time – the ODBC driver converts the query to T-SQL, and the query gets run by SQL Server, not by Access.

Perhaps someone more familiar with calling Sprocs from Access can see an issue with what you’ve attempted.

Reply | Quote

G’Day,

The reason we’re moving this stuff onto the server is that we are finding when we do this we gain a massive increase in performance. As an example we have a table with close to 1 million records that would previously take somewhere in the vicinty of 30 minutes to run an update on if other people were logged into the database. Without users, we’d still be looking at 10 minutes plus. During this period we’d also find that the entire network would slow dramatically. Using this method (moving queries into SP’s), we have it down to under 3 minutes with no noticable performance hit on the network. There are various other queries we’ve moved that have seen a large performance increase but nothing quite as dramatic as that one. Generally we’ve found that we’re at least halving the time it takes to run queries by moving them into Stored Procedures on the SQL Server which has resulted in happier users (and hence, happier techs!). Of course the more we move onto the SQL server, the more load that is going to be under so we need to keep an eye that obviously otherwise we’ll be back where we started.

We’ll try the pass-through query and let you know how we go.

Cheers
toncc

Reply | Quote