How to write a stored procedure in MS Access?

Do  you want it to change on the fly, or is it static?

If you just want a static query why dont you try the wizard.
If you want to write a dynamic query you can build the string
in a function and open an ADO recordset based off of the query.


Code is like this:

 Dim strSQL As String
 Dim rst As Adodb.recordset

 set rst = new adodb.recordset
 rst.CursorType = YouChoose
 rst.LockType = YouChoose
 rst.ActiveConnection = CurrentProject.Connection
 
 strSQL = "SELECT * FROM someTable" 'you can build in other variables here
 
 'open the recordset
 rst.open(strSQL)

 'you can check to see if the recordset returned rows with EOF, and BOF
 if(rst.EOF AND rst.BOF) then
    'recordset is empty
 end if

 'dont forget
 rst.close
 set rst = nothing

Hope that helps somewhat! You can build that into a module and pass it the table name, parameters and open any table, it just depends on how you build the SQL string.

You have to have pre-defined queries in MS-Access to have performance-gain.
Goto the Queries Tab in the Main Window of the MS-Access, type the SQL Statements you want to run,
you can even have parameter query like

for example the pre-defined query may look like this:
__________________________________________________________________________________________________

PARAMETERS param1 Text, theDate DateTime;
select * from x where (((x.fld1)<[param1]) AND ((x.fld2)>=[theDate]))
___________________________________________________________________________________________________

'param1 and theDate are the Parameters you can give the values for at runtime.(only if you have any parameters)

'then give it a name and save it say 'qryTest'

'In your code
____________________________________________________________________________________________________
dim db as database
dim qrydf as querydef
dim rs as recordset
dim rs2 as recordset
Set db = CurrentDb()
dim qrydf2 as querydef

 Set qrydf = db.QueryDefs("qryTest")

 qrydf.Parameters(0) = me.text1.text 'this form leve variable or any variable value supplied to the query

'you can either say , if the query doesn't return any rows (like a update query)


qrydf.execute

'OR if the query returns rows then, say to take that in a recordset take the result of the query and use it in your next pre-defined query which again has some 'parameters, values for which will be supplied from the 'rs' object. This goes on and on until the final query.

 Set rs = qrydf.OpenRecordset()

'if your second pre-defined query in MS-Access is "qrytest2" then, if it has 2 parameters

 Set qrydf2 = db.QueryDefs("qryTest2")
 qrydf2.Parameters(0) = rs("fld1") 'result of the first query.
 qrydf2.Parameters(1) = rs("fld2") 'result of the second query.

 'now again you can say
 
Set rs2 = qrydf2.OpenRecordset() 'take the result of the 2nd pre-defined query in recordset2 and then use it in the next pre-defined query....on and on...