Creating a stored procedure (query) in access

T

Tor Inge Rislaa

Creating a stored procedure (query) in access

In SQL Server I would write something like the syntax below to create a
stored procedure. Is there a similar way to create a query in access, by
code?

CREATE PROCEDURE qryMyquery AS SELECT My_Id, MyDescription From MyTable

TIRislaa
 
M

Michel Walsh

Hi,

Yes, but that will be almost the same functionality than for a query
with parameters (which is not standard in SQL, but the traditional way to do
it in Jet). You also have to use ADO, not DAO, not the query designer. Here,
in the Immediate Debug Window, you can use:


CurrentProject.Connection.Execute "CREATE PROCEDUTE toto( arg LONG) AS
SELECT * FROM myTable WHERE myField=arg ;"


and, instead of using a command object, you can simply use, in your code:


Set myRecordseet= CurrentProject.Connection.Execute("EXECUTE toto 22" )

and still beneficiate of an eventual advantage of a precompiled "query" (the
procedure toto).




Hoping it may help,
Vanderghast, Access MVP
 
T

Tor Inge Rislaa

This work just as I wanted. My next quetion is if there is a similar way to
remove an existing query, in the purpose of replacing a query with a new
with the same name.
 
M

Michel Walsh

Hi,


CurrentProject.Connection.Execute "DROP PROCEDURE toto"


will drop the procedure (not procedute, as in my first message, sorry for
the typo).



Hoping it may help,
Vanderghast, Access MVP
 
Top