Create Procedure query in Access.

[chrisgirl77]

Hello,
My appliction was connected to SQL SERVER, and was using a STORED
PROCEDURE (created using T-SQL) in SQL SERVER.
The code for the procedure is:

CREATE PROCEDURE proc_name
@par1 varchar(100),
@par2 varchar(100)
AS
BEGIN
SET NOCOUNT ON;
SELECT par1, par2 from table_name
where par1= @val1 and par2=@val2
END
GO

MY PROBLEM IS:
Am trying to create a stored procedure in MS Access (using query) to
function as above. But its giving me an error:
SYNTAX ERROR IN CREATE TABLE.

Tried to run as simple query too:
CREATE PROCEDURE procName as SELECT * FROM tabName.
Still a failure, with same error message.

As far as I came to know after Researching is that ACCESS doesnt
support 'CREATE PROCEDURE' table. Is there any other work-around? I
prefer calling the procedure from the application.

Any help appreciated...

 

[Douglas J. Steele]

Access doesn't support the SET NOCOUNT ON statement.

Realistically, you don't need a stored procedure: a parameter query should
be sufficient.

 

[chrisgirl77]

Thanx Doug,

This is just a sample. This is not what I want to use procedure for...

I wud like to know how to use a procedure in Access.

 

[Michel Walsh]

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

 

[Chris Smith]

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