Form using stored procedure does not work in Access Runtime

[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

 

[CyberDwarf]

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

 

[Bonno]

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

 

[CyberDwarf]

Bonno

Have you tried this with a query yet?

Steve

 

[Vadim Rapp]

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

 

[Uwe Ricken]

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