Working with Access Projects in Access 2007 - Creating a Stored Procedure in the Project
(Page 3 of 4 )
With the database objects added you will be able to find the stored procedure icon in the Other section, right next to the Reports as shown here.
;
Clicking on the Stored Procedure Icon, you will be able to create a New Object, a stored procedure.
This opens the Add Table window where you can add one or more tables (use the shift key) to the fashioning of your stored procedure which uses a Query Editor.
When you close the above window after choosing the table or tables, the objects will be added to the design pane. This works very much like the query editor in other, older versions of MS Access. You can pick the columns you want, sort them and order them and impose constraints to filter rows using criteria. Right clicking an empty area in the design pane lets you access the Grouping option as well as a few other tasks you can do as shown.
We will be developing a stored procedure which asks for the name of a city and displays related information for that city. What was typed in was a question mark(?) in the criteria column which becomes =@Param1, the parameter for a search query.
When you right click the design area you can access the Properties window for this stored procedure as shown. You can choose to display all columns or the chosen ones; you may, if you want, choose to show only distinct values; and so on. Choices made here will go into a stored procedure SQL script as seen later. The description is optional.
If a Default is chosen for the @Param1 as shown in the Stored Procedure Parameters tab, then the stored procedure takes a hard coded value for the parameter and displays just the selected columns for that procedure.
The Data tab can be used while scripting a stored procedure which depends on multiple tables with Master/Child relationships.
Before you see the results of the procedure you need to save the procedure.
The stored procedure is saved as ByCity in the project.
When you click on the OK button the stored procedure will be saved and it immediately appears under the All Access Objects window as shown under Queries.
If you right click the query named ByCity, you will pop open a drop-down where you can see the design view.
When the query is highlighted using the View drop-down you can access various views, one of which is the SQL View. When this is chosen, a tab opens with the SQL script of the stored procedure. This SQL appears to be the case when the default city Bern was chosen. You can alter this SQL by removing =N'Bern' and reverting back to the parameterized query where you need to provide a parameter at run time.
When the SQL View is chosen you can access other related icons for task management as shown in the next picture, like Verifying SQL Syntax, running the procedure, and so on.
You may also right click on the tab of the tabbed window and access the various views as shown.
With the parameterized version, if you "run" the procedure you will be asked for a parameter in the Enter Parameter Value box.
When you enter "London" for the city and click on the OK button you will retrieve the columns for the "City" filter as shown.
Next: Stored Procedure in SQL Server >>
More Microsoft Access Articles
More By Jayaram Krishnaswamy
/ 12 
