Tim Ferguson wrote:
Is it fair to say @@IDENTITY is not documented in the
Access documenation?

INFO: Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity
http://support.microsoft.com/default...b;en-us;232144

Most of what I have
learned about the extended uses of ADO+Jet 4 has been found on these
groups!

I've learned by playing with the thing, trying things out and
discovering things by mistake.

MSDN a few gems but they are hard to find. This is the best:

Intermediate Microsoft Jet SQL for Access 2000
http://msdn.microsoft.com/library/de...l/acintsql.asp

In CREATE PROC, you can define more than one parameter. See this thread
for an example which uses multiple parameters (plus the IIF you were
asking about):

http://groups.google.com/group/micro...a522639fe29876

CREATE PROCEDURE Proc1 (
:value INTEGER,
perator_code INTEGER
) AS
SELECT * FROM Test WHERE SWITCH(
perator_code = 1, IIF(data_col = :value, 1, 0),
perator_code = 2, IIF(data_col :value, 1, 0),
perator_code = 3, IIF(data_col :value, 1, 0),
perator_code = 4, IIF(data_col = :value, 1, 0),
perator_code = 5, IIF(data_col = :value, 1, 0),
perator_code = 6, IIF(data_col :value, 1, 0),
TRUE, 0) = 1;

Note that Jet 4.0 supports the use of a colon ( prefix on a parameter
name, as used by Standard SQL, whereas TSQL compels the use of @.
However, the colon is not supported in DAO and will cause a run-time
error. It's best to write code to support ADO and DAO if possible, a
point a lot of people miss when they use a wildcard specific to one
only (i.e. % for ADO, * for DAO).

The parameter list must be in parentheses, as with Standard SQL (but
TSQL is more flexible, you can use parens or omit them). I don't think
Jet supports output parameters.

And despite what the documentation may suggest, you can assign default
values to parameters. See this thread with demonstrates that that a
default value is recognized and used by Jet:

http://groups.google.com/group/micro...bfef122287c825

CREATE PROCEDURE TestProc
(arg_data_col VARCHAR(10) = 'N/A') AS
SELECT Key_col, data_col
FROM Test
WHERE data_col = _
IIF(arg_data_col IS NULL,
data_col, arg_data_col);

Extracting the default from the schema is something I'm working on.