@@IDENTITY for Access -- Is it thread safe?

[Siegfried Heintze]

Is @@IDENTITY thread safe in that it returns the autoincrement value for the
most recent insert for that thread or just the most recent insert by any
thread?

Tim says @@IDENTITY works with ADO and but not DAO. What about ODBC?

I'm looking at
http://msdn.microsoft.com/library/d...onComparingAccessSQLSSQLSyntax.asp?frame=true
and I'm very confused! I'm sure that MS Access does not, for example,
support stored procedures.

Tim says there are a lot of TSQL features supported in MS Access. Is there a
list somewhere?

I'm looking at Dino "Building Web Solutions with ASP.NET and ADO.NET" and I
see really fancy SQL like

SELECT
CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE '(Total)' END
AS AllCustomerSummary,
CASE GROUPING (os.orderid) WHEN 0 THEN od.orderid ELSE -1 END
AS IndividuallyCsutomerSummary,
FROM Orders o, [Order Details] od
GROUP BY o.customerid, od.orderid WITH ROLLUP

Will these fancy keywords like CASE and ROLLUP work in MS Access?

There used to be MSAccess SQL syntax at search.microsoft.com but I cannot
find it anymore.

Thanks,
Siegfried

 

[Tim Ferguson]

Is @@IDENTITY thread safe in that it returns the autoincrement value
for the most recent insert for that thread or just the most recent
insert by any thread?

Help file says it's per connection. I don't think database servers know
anything about threads.


Tim F

 

[peregenem]

Tim says @@IDENTITY works with ADO and but not DAO. What about ODBC?

Tim meant that @@IDENTITY is supported when using the Jet 4.0 OLE DB
provider with a Jet 4.0 database.

Tim says there are a lot of TSQL features supported in MS Access. Is there a
list somewhere?

The new features for Jet 4.0 were intended to bring Jet more in line
with SQL Server:

Description of the new features that are included in Microsoft Jet 4.0
http://support.microsoft.com/default.aspx?scid=kb;en-us;275561

OLE DB Provider for Microsoft Jet: SQL Support
http://msdn.microsoft.com/library/d.../oledb/htm/oledbprovjet_data_type_support.asp

Will these fancy keywords like CASE and ROLLUP work in MS Access?

AFAIK Jet has no CUBE (GROUPING, ROLLUP, etc) support but there may be
workarounds?

CASE is implemented in Jet using IIF() and/or SWITCH() constructs you
could look them up in the help ...

There used to be MSAccess SQL syntax at search.microsoft.com but I cannot
find it

.... but I'm pretty sure there are is no official documentation listing
which functions are supported/enabled in Jet. Basically, it is the VBA5
functions which return a single value, distinct from methods or
functions which return arrays etc. And I know CDec is broken
(http://support.microsoft.com/default.aspx?scid=kb;en-us;Q225931),
grrr! Brendan Reynolds MVP has been compiling a list:

http://brenreyn.blogspot.com/

If you want the Jet 4.0 language documentation, try here:

http://office.microsoft.com/en-us/assistance/CH062526881033.aspx

 

[Siegfried Heintze]

Ah hah! Thank you very much!

The specification and examples for "CREATE PROC" is really brief! All the
examples consist of a single statement and I don't see how to pass function
arguments or use multiple statements. I suspect it is not possible.

I tried @@IDENTITY and it seems to work! Where do I find the documentation
on @@IDENTITY? I looked under clauses in
http://office.microsoft.com/en-us/assistance/CH062526881033.aspx and could
not find it there. I looked under new features and it did not appear to be
there either. I guess Tim answer my question already by refering me to the
TSQL documentation. Is it fair to say @@IDENTITY is not documented in the
Access documenation?

Siegfried

 

[Tim Ferguson]

. I guess Tim answer my question already by refering me to the
TSQL documentation. Is it fair to say @@IDENTITY is not documented in the
Access documenation?

The ADO210.CHM does not give any of the SQL commands. Most of what I have
learned about the extended uses of ADO+Jet 4 has been found on these
groups! My guide to most T-SQL comes from SQL Server Books On Line, which
is downloadable from the MSDN web site, although it's not on this PC so I
have been searching microsoft.com itself. Google can finds lots of How-To-
In-SQL pages too.

All the best


Tim F

 

[peregenem]

INFO: Jet OLE DB Provider Version 4.0 Supports SELECT @@Identity
http://support.microsoft.com/default.aspx?scid=kb;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/default.asp?url=/library/en-us/dnacc2k/html/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/micr...7ddfc0c7d52/afa522639fe29876#afa522639fe29876

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/micr...210f1a4a102/35bfef122287c825#35bfef122287c825

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.