Access 2000 stored procedures

[David]

Does anyone use Access 2000 stored procedures? I did not
know they existed until I read an Access book I have.
Will the upsizing wizard convert them to SQL Server stored
procedures or will it only convert regular queries?

 

[Michel Walsh]

Hi,

A Jet 4.0 Stored Procedure is very similar to its "parameter" query, and
is limited to one statement. One of the advantage of a Jet 4.0 sp is, with
ADO, to provide an easier way to access them than by using an ADO-command
object.

MyConnection.Execute" EXEC PROCEDURE procname paramList "

is, sometimes, easier than to fill the parameters of a command object.


It has only marginal benefits over a DAO QueryDef, in my opinion. Note
that a sp won't work in DAO, neither in the query designer, and I doubt it
would work under DoCmd either (haven't check, on the other hand).


Hoping it may help,
Vanderghast, Access MVP

 

[Albert D. Kallal]

Since the JET stored procedures are only limited to ONE statement, and you
don't have sql variables in the stored procedure, you really don't get
anything different then just saving a query in the query builder.

Since you can't have any kind of code in the stored procedure like you can
with sql server, then it is stretching things a good deal to call the ONE
statement a procedure.

However, it does more follow the mental model that people have when using
sql server. And, as mentioned, it does mean that your coding approach to the
application in general will be MORE convertible to sql server when/if you
make the jump.

It also means that JET more closely follows the standard sql ddl when
making/storing quires. Since that same code will likely work with sql
server, then it has it place.

So, if sql server is to be used in the future, then I would consider using
JET stored procedures. However, you can't see/use them in the query builder,
so this is one big reason to avoid them.