User Defined Functions

[JohnCreighton_]

From what I read
http://support.microsoft.com/kb/239482
Jet seems to support user-defined functions by I can't find any
examples on the web. I've found examples for other systems e.g.:

CREATE FUNCTION FROMPERSON (P PERSON)
RETURNS ROW (NAME VARCHAR(10), FIRSTNAME VARCHAR(10))
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN VALUES (P..NAME, P..FIRSTNAME)
http://publib.boulder.ibm.com/infoc...topic=/com.ibm.db2.udb.doc/admin/r0002426.htm

I've tried to use Jet stored procedures but I can't seem to execute
them as a function from within the query. I only seem to be able call
them as there own query. Could a parameterized query work? Is there a
way to pass parameters via the select statement?

 

[Marshall Barton]

http://support.microsoft.com/kb/239482
Jet seems to support user-defined functions by I can't find any
examples on the web. I've found examples for other systems e.g.:

CREATE FUNCTION FROMPERSON (P PERSON)
RETURNS ROW (NAME VARCHAR(10), FIRSTNAME VARCHAR(10))
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN VALUES (P..NAME, P..FIRSTNAME)
http://publib.boulder.ibm.com/infoc...topic=/com.ibm.db2.udb.doc/admin/r0002426.htm

I've tried to use Jet stored procedures but I can't seem to execute
them as a function from within the query. I only seem to be able call
them as there own query. Could a parameterized query work? Is there a
way to pass parameters via the select statement?

You missed an important part of that KB's summary:

"Jet 4.0 database engine permits you to call unsafe
<<Microsoft Visual Basic for Applications>> functions"

Since Access is an application development system (as
opposed to a datastore), it contains a complete(?)
programming language, VBA, which is available for use in Jet
queries through the Expression Service.

"Stored Procedures" in Access/Jet are just saved queries.
The queries can be data retrieval (Select), action (Update,
etc) or DDL (Alter ..., etc). There is no Jet equivalent of
Create Function.

 

[Allen Browne]

John, the Summary of the article you quotes says:
The Microsoft Jet 4.0 database engine permits you to call unsafe
Microsoft Visual Basic for Applications functions

The UDFs are VBA functions, not SQL functions.

AFAIK, JET SQL does not support CREATE FUNCTION.