Another variation

[Guest]

Hi,


Procedure in Jet can only be made of one statement and are close cousins
of Jet "parameter" queries. To create a procedure (sometimes, the syntax of
using it would be much more friendly than filling a queryDef.Parameters
collection) is to use ADO (not the query designer, which is based on DAO).
You can use the Debug Immediate Window to create the proc:


CurrentProject.Connection.Execute "CREATE PROC thisIsIt(param1 TEXT ,
param2 LONG) AS SELECT .... "


Note that the parameters do not require the @.


Set ... = CurrentProject.Connection.Execute("EXECUTE PROC thisIsIt
'DPM', 0 ")


is a way to use the created procedure (easier than filling a
queryDef.Parameter, I repeat myself).



Hoping it may help,
Vanderghast, Access MVP

Vanderghast,
The documentation may need some maintenance.
The "CREATE PROCEDURE Statement" topic in the local Jet4.0 SQL
documentation has this:

Syntax
CREATE PROCEDURE procedure
[param1 datatype[, param2 datatype[, ...]] AS sqlstatement

I was able to get good justice with something like

CREATE PROCEDURE procedure AS PARAMETERS
param1 TEXT; INSERT INTO target_table( param1 ) VALUES ( [param1] );

Neither my approach nor yours matches the documentation, which is otherwise
quite good.
Furthermore, I couldn't locate the Jet4.0 SQL reference on msdn.microsoft.com.
Otherwise, I would have done my barking chihuaha thing there.
Best,
Chris

 

[Michel Walsh]

Hi,


I transmit the observation. Note that you can report bugs from the Microsoft
web page, under Contact Us (at the bottom of the page), if memory serves.

I don't know where is Jet 4.0 doc, me neither...



Vanderghast, Access MVP

Hi,


Procedure in Jet can only be made of one statement and are close
cousins
of Jet "parameter" queries. To create a procedure (sometimes, the syntax
of
using it would be much more friendly than filling a queryDef.Parameters
collection) is to use ADO (not the query designer, which is based on
DAO).
You can use the Debug Immediate Window to create the proc:


CurrentProject.Connection.Execute "CREATE PROC thisIsIt(param1 TEXT ,
param2 LONG) AS SELECT .... "


Note that the parameters do not require the @.


Set ... = CurrentProject.Connection.Execute("EXECUTE PROC thisIsIt
'DPM', 0 ")


is a way to use the created procedure (easier than filling a
queryDef.Parameter, I repeat myself).



Hoping it may help,
Vanderghast, Access MVP

Vanderghast,
The documentation may need some maintenance.
The "CREATE PROCEDURE Statement" topic in the local Jet4.0 SQL
documentation has this:

Syntax
CREATE PROCEDURE procedure
[param1 datatype[, param2 datatype[, ...]] AS sqlstatement

I was able to get good justice with something like

CREATE PROCEDURE procedure AS PARAMETERS
param1 TEXT; INSERT INTO target_table( param1 ) VALUES ( [param1] );

Neither my approach nor yours matches the documentation, which is
otherwise
quite good.
Furthermore, I couldn't locate the Jet4.0 SQL reference on
msdn.microsoft.com.
Otherwise, I would have done my barking chihuaha thing there.
Best,
Chris