Stupid MS Access Tricks

You've probably read that MS Access doesn't support stored procedures.  The real answer that it does, but not as you would expect to use it in ColdFusion.  When you issue a create procedure such as below;

Note: Below code was run on CFMX 7 with the cfbookclub MSAccess db.

<cfquery datasource="cfbookclub" >
CREATE PROCEDURE spByAuthor
AS
SELECT AUTHORS.FIRSTNAME, AUTHORS.LASTNAME, BOOKS.BOOKID, BOOKS.TITLE
FROM AUTHORS INNER JOIN BOOKS ON AUTHORS.AUTHORID=BOOKS.AUTHORID
WHERE AUTHORS.LASTNAME LIKE [@authorlast];
</cfquery>

You end up with a saved query.  With ColdFusion you can call queries in Access just as you would a table.  The problem occurs when the saved query has a parameter.  The standard cfstoredproc tag has a problem calling the stored procedure/query in Access and will result in an error.

The way to call the saved query with a param is as follows;

<cfquery name="qryBooks" datasource="cfbookclub" >
        EXECUTE spByAuthor <cfqueryparam cfsqltype="cf_sql_varchar" value="M%">
</cfquery>