Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Apr 2002
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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=PLATINUM2\SMNS;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

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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
    Andrew

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  4. #4
    Lounger
    Join Date
    Apr 2002
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •