Stored Procedure in Access?

[qwert231]

I have a Union query I want to use, but it needs to accept a parameter. I want to store the query in my access database and pass the parameter to it. I don't know how to do that. How should I write it in access what kind of OleDb command should I use?

Example query: SELECT * FROM tblOrderDetails WHERE ORDERID = ?

 

[jfrost10]

Hey qwert,

have you done stored procs with sql before? If you have, doing it in Access is exactly the same: when you specify the command.CommandText = , the text you pass will be the name of the query in the database.

You fill the parameters in teh same was as if you were doing SQL. The only real difference is that you're using the OLEDB library instead of the SQL one.

hth, and let me know if I wasn't clear enough.


D'Arcy

 

[qwert231]

So, when I create the commmand, I put the query name in the CommandText, and then add parameters to the command? I've done that with update and insert queries for adapters.

But for a select query? How do I enter the parameters in the query? I tried with ? marks, but got a parameter error. Haven't had much time to play with it yet tho.

Here's what I was playing with:

SELECT Package, Sum(Quantity) AS TotalQuantity, OrderID
FROM [select P1 as Package, Q1 as Quantity, OrderID from tblProofOrders where P1 is not null and Q1 is not null UNION ALL select P2 as Package, Q2 as Quantity, OrderID from tblProofOrders where P2 is not null and Q2 is not null UNION ALL select P3 as Package] AS [%$##@_Alias]
WHERE OrderID = ?
GROUP BY Package, OrderID
ORDER BY OrderID

 

[qwert231]

Sorry, dinking around with it I found my syntax errors. I got the query asking for the parameters now. I will try to work my code with it now. This will be good!