Working with Stored Procedures in an MS Access Project - Does MS Access Project support the directionality of the parameters?
(Page 4 of 4 )
The stored procedures may take an input parameter and return the output of the procedure in another parameter. This is called the parameter's direction property. The Stored Procedure interface we saw earlier does not show any possible features which appear to be related to the "direction" of the parameter. The direction of the parameter may be inserted into the SQL statement and saved. But this is not available in the project as a feature of the wizard. You may place this under Tips and tricks. It is a tip but not a trick.
Now go back to the design view of the stored procedure and add a question mark for the Product name. When you step out, @Param2 will have been added to the stored procedure as shown.
The SQL of this stored procedure is shown next. Since the MS Access Project does not support returning the result of the procedure in a parameter, you will have to modify the SQL statement for this procedure and save. The saved stored procedure was named BasicInOut. The modified SQL is as shown in the next paragraph.
DECLARE @RC int
DECLARE @Param1 int
DECLARE @Param2 nvarchar(50)
-- Set parameter values
EXEC @RC = [parametersSQL].[dbo].[BasicInOut] @Param1,
@Param2 OUTPUT
Since this stored procedure is not supported by the project interface, the result of executing this stored procedure can be seen in the Query Analyzer by using the proper parameter input.
Now to test run this query we run it for an input parameter, @Param1='7'. When this query is run you will get the following output. As we have seen earlier, for the SupplierID=7 multiple outputs are possible, out of which only one of them is shown. In order to see all the values you will have to modify the SQL. This is outside the scope of this article, as it's more than likely that Microsoft Project does not seem to support returning the result in a parameter.
Summary
The tutorial shows the steps involved in creating stored procedures using MS Access 2000 with the back end SQL 2000 Server. It describes three kinds of stored procedures including procedures that return values in parameters. The interface does not support the parameter "out" directionality. The SQL View, however, can be used to create such a procedure, although it cannot be tested within MS Access. Microsoft did not support Access Projects in 2003, however it supports it in Access 2007, which is still in beta.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 11 
