Form using stored procedure does not work in Access Runtime

  • Thread starter Thread starter Bonno
  • Start date Start date
B

Bonno

Hi there,

I am using a parameterised stored procedure as a recordsource of a form.
When the form is opened on a machine with full Access everything is working
fine. However on a machine with only the Access runtime the form will not
open. In both situations for the same SQL account. What can be wrong?

I am using an Access 2000 adp-form and SQL Server 2000.

The stored procedure looks like this:

ALTER PROCEDURE dbo.ProductsFiltered
@CategoryID int
AS
SET NOCOUNT ON
SELECT * FROM Products
WHERE CategoryID = @CategoryID
RETURN

The form properties looks like this:

Recordsource: dbo.ProductsFiltered
Inputparameters: @CategoryID=1

Has anybody have any ideas?

Thanks in advance,

Bonno Hylkema
 
This may be barking up the wrong tree, but doesn't the form need a
Query/Table as its datasource, not a stored procedure?

Your SP can be easily rewritten as a dynamic query/view.

HTH

Steve
 
Thank you for your reaction. I am using a stored procedure with parameters,
in order to filter the number of records retrieved from the database.
Otherwise I could have used a table or query as you suggested.

Using a stored procedure works fine in standard Access, but not in the
RunTime and that's my problem!

Regards, Bonno
 
B> I am using a parameterised stored procedure as a recordsource of a form.
B> When the form is opened on a machine with full Access everything is
B> working fine. However on a machine with only the Access runtime the form
B> will not open.

Should be working. Any error messages?

Also, I would remove SET NOCOUNT ON. This is essentially select statement,
so why not to tell Access how many records are returned.

Vadim Rapp
 
Hallo Bonno,

does the sp fire?
Check this with the profiler to see what action will be provided on the
server!

As Vadim has mentioned - should work.
But before we don't know whether the sp is fired we should not look
to any "possible" bugs in the sp.

HTH ;-)

--
Gruß, Uwe Ricken
MCP for SQL Server 2000 Database Implementation

db-Berater GmbH i. G. 64390 Erzhausen
http://www.db-berater.de
http://www.memberadmin.de
http://www.conferenceadmin.de
____________________________________________________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm
 
Back
Top