Creating stored Procedure in Access

V

VMI

I need to create a stored procedure in my Access DB, but don't know how to.
I thought that typing "CREATE PROCEDURE usp_selectAudit AS select * from
audit" in an SQL view would work, but it doesn't. What else can I do?


This is the first part, since I need to create the SP from within C#..but
that's another post for another NG.

Thanks.
 
I

Immanuel Sibero

Hi VMI,

Access / Jet database doesnt not have that capability. But you can use VBA,
action and select queries, table relations to accomplish the same things.
As far as C#, you're right, you'll probably have better luck in another
newsgroup. However, stored procedures would still not be available.

HTH,
Immanuel Sibero
 
J

John Vinson

I need to create a stored procedure in my Access DB, but don't know how to.
I thought that typing "CREATE PROCEDURE usp_selectAudit AS select * from
audit" in an SQL view would work, but it doesn't. What else can I do?


This is the first part, since I need to create the SP from within C#..but
that's another post for another NG.
Click to expand...

Since Access (JET) databases do not support and do not run stored
procedures, you may be up the creek. Stored procedures work in
SQL/Server databases (and Access can use them if they exist), but they
don't work in Access proper.

John W. Vinson[MVP]
 
A

Albert D. Kallal

You should be able to do as you ask.

If you from the debug window type:

currentproject.Connection.Execute
"create PROCEDURE usp_selectAudit as select * from audit"

The problem is that the stored sql is dao, and dao to jet does NOT support
create view, and create procedure.

However, if you use the command line interface, and type in the above,..then
it does work.

Note that I would stick in general to using create VIEW, as when you do
this, it appears in the query list (since a saved query in ms-access is the
same thing as a view anyway).

Note that while action queries work like:

Currentdb.Execute "delete * from tableTest1"
the above is dao.

or, ado

currentProject.Connection.Execute "delete * from tableTest1"
the above is ado.

However, you must use the 2nd one (currentProject) if you are going to use
the create VIEW, or create PROCEDURE with jet.
 
Top