Passing parameters into stored procs

[jottiwell]

Hello,

I'm trying to convert an existing database from .mde frontend / .mdb backend to .adp front / SQL 7 back.

I'm having a huge amount of problems trying to pass parameters to stored procedures. I can pass it through adodb.command exec calls, but not any other way. Is it possible to pass them through the

Docmd.OpenStoredProcedure call in Access?,

I need to be able to display the result set on screen, as you would with a very simple access query. I can modify the stored procedures to create permanent or even temporary tables, obviously I'd prefer to use temp tables for the results sets, but I can't open the temp tables from VBA in access, is this possible or do they only exist within the bounds of SQL?

I've tried creating an activeX control in VB, but again if I'm trying to use an ADO control to populate a DBGRID, I can't work out how you pass the parameters within the stored procedure dropdown, ( i.e. where it says '<storedprocedurename>; 1' where 1 is the number of parameters the stored proc expects.

Please help, anything'd be helpful.

Thanks

John

John,

I'm working on a project that's almost identical to yours. When you're working with temporary tables, you've got to remember their lifespan: they only exist while that proc is executing and then they are destroyed.

Here's a simple test I was working with yesterday:

[CODE}
CREATE PROCEDURE test @varIn int AS

CREATE TABLE #Foo
(ID int NOT NULL,
Name char NOT NULL,
)

INSERT into #Foo (ID, Name) values (@varIn, "K")

SELECT * FROM #Foo
[/CODE]

The most important part for you is the "Select" statement at the end. This will cause the proc consisting of * from the temporary table. What you do with it after that is your business.

In Access, create a SQL-pass through query and run it, and you'll see you got the recordset back even though it's already gone from the database.

And No, I'm pretty sure you can't pass parameters with OpenStoredProcedure.

John

[jottiwell]

Thanks for the reply,

Still stuck though, your code does appear to compile and run successfully in a stored procedure, but the SELECT statement at the end doesn't bring anything back onto the screen, which is what I need it to do. Itsimply tells me
'The stored procedure run successfully but did not return any records'. Then that's it, I need it to display the result grid on screen. Please get back to me again, any other ideas'd be great.

Also, I've managed to get BV to recall the contents of a global temporary table, but I can only have one instance of the table at any one time. Therefore if I've got two users who need to create two temporary tables ( this is if I can't get the local temp tables to work ) , would you have any idea whether it's possible to provide the name of the target global temporary table as a parameter, therefore

CREATE PROCEDURE TESTPROC

@targettablename 'what do I define the type as'

SELECT * from [whatiwant] INTO @targettablename

Don't know whether this is possible or not,

Again thanks for the help

John

[monte96]

Post some code so I can see how you are calling the stored procedure and setting your recordset reference.