Creating a stored procedure (query) in access

  • Thread starter Thread starter Tor Inge Rislaa
  • Start date Start date
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
 
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
 
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.
 
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
 
Back
Top