CODEWITHSUNDEEP
sqlms-accessstored-procedures
user937450
user937450

Reputation: 733

Create Stored Procedure in Access 2007

Is it possible to create an Stored Procedure in Access 2007? If it is, how can i do it?

Thanks

Upvotes: 1

Views: 4625

Answers (2)

AlexD
AlexD

Reputation: 21

You can created stored procedures in Access. I use Access 2007. I'm not sure of the capability in Access 2003. I've created stored procedures using the following process:

  1. Create a query that performs the action you wish to take (Create(MakeTable), Read(Select), Update, Delete). Setup parameters if used. This query is for use as a template. You don't need to keep.

  2. Switch query to SQL View and copy the query.

  3. Use Cntl-G to switch over to Visual Basic. Use either the immediate window or create a subroutine using Insert | Procedure.

  4. Paste your query and setup the following syntax to create the stored procedure.

CurrentProject.Connection.Execute "CREATE PROC sp_storedProcedureName (parm1 Text, parm2 Integer, whatever other parms & datatypes) AS sqlStatementFromQueryGoesHere;"

  1. Run from immediate window or procedure. It should run clean if no errors.

  2. Then go back to the Navagation pane and refresh (the stored proc won't show originally without refresh). Once refreshed, you have your stored proc showing in the queries category

Upvotes: 2

Ezi
Ezi

Reputation: 2210

You could save queries with parameters that works much like a SP.

Upvotes: 4

Related Questions