Stored Procedures

T

tsluu

Hi All,
I am using Access 2002 as the database from my VB programs. I like to know
how to create Stored Procedures and how do I view them after I create them.

Thanks.
 
P

Paul Overway

As Dirk indicated, stored procedures are not a feature of the Jet database
engine. However, you could install MSDE/SQL Server Desktop and create
stored procedures. MSDE isn't part of the default install, but it should be
on your Office CD(s).
 
D

Dirk Goldgar

tsluu said:
Hi All,
I am using Access 2002 as the database from my VB programs. I like to
know how to create Stored Procedures and how do I view them after I
create them.
Click to expand...

Jet databases (Access .mdb files) have no stored procedures, in the same
sense as, say, SQL Server databases. The closest analog is the stored
query, accessible via the DAO QueryDef object. Unlike SQL Server's
stored procedures, Jet stored queries may consist only of a single SQL
statement.
 
P

Paul Overway

Dirk:

Not to quibble, but you don't use nested SQL statements, i.e.,

SELECT x FROM tblA WHERE x In(SELECT y FROM tblB).

You can't have triggers on tables in Jet and that can be a big drawback for
some applications. Nor does Jet by itself have any equivalent to T-SQL.
However, Jet and VB/VBA/Whatever can do much of what you might do with
T-SQL.
 
V

Van T. Dinh

Beware that there is a "CREATE PROCEDURE ... " in the JET SQL Reference.

I haven't eperimented with it though.
 
D

Dirk Goldgar

Paul Overway said:
Dirk:

Not to quibble, but you don't use nested SQL statements, i.e.,

SELECT x FROM tblA WHERE x In(SELECT y FROM tblB).
Click to expand...

Yes, you can use nested SQL statements in the form of subqueries and
derived tables. By "a single SQL statement" I was thinking of one
primary statement, but I can't think of a better way to describe it than
that.
You can't have triggers on tables in Jet and that can be a big
drawback for some applications. Nor does Jet by itself have any
equivalent to T-SQL. However, Jet and VB/VBA/Whatever can do much of
what you might do with T-SQL.
Click to expand...

True. Unfortunately, you can't call VB/VBA functions from the query
itself, except when the query is called by an Access application.
 
Top