A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old October 5th, 2005, 04:10 PM
Siegfried Heintze
external usenet poster
 
Posts: n/a
Default @@IDENTITY for Access -- Is it thread safe?

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/de...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


Could someone kindly point me to the URL on the SQL syntax for
MSAccess and specifically where the IDENTITY keyword is documented?


Quite right: it's @@IDENTITY. Look here

http://doc.ddart.net/mssql/sql70/globals_7.htm

or search the MSDN site too.

B Wishes


Tim F

"Siegfried Heintze" wrote in
:

I'm confused! This is for T-SQL (aka MS SQL Server). Does this also
work for MSAccess?


Yes, as long as you use the ADO library (not DAO, which was never updated
to Jet 4). I'm not completely sure that ADO/T-SQL handles the entire SQL
syntax, but most everyday things like @@IDENTITY, CHECK constraints,
DEFAULT values and so on are there.

That is why the answer to the question "Is DAO better for Access + Jet than
ADO" is, "Yes, except for..." Nothing in life is ever straightforward!

Best wishes


Tim F




  #2  
Old October 5th, 2005, 05:34 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"Siegfried Heintze" wrote in
:

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

  #3  
Old October 6th, 2005, 12:21 PM
external usenet poster
 
Posts: n/a
Default


Siegfried Heintze wrote:
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...b;en-us;275561

OLE DB Provider for Microsoft Jet: SQL Support
http://msdn.microsoft.com/library/de...pe_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...;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 he

http://office.microsoft.com/en-us/as...526881033.aspx

  #4  
Old October 6th, 2005, 04:23 PM
Siegfried Heintze
external usenet poster
 
Posts: n/a
Default

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/as...526881033.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




  #5  
Old October 6th, 2005, 06:01 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"Siegfried Heintze" wrote in news:#H3W5noyFHA.1564
@TK2MSFTNGP10.phx.gbl:

. 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


  #6  
Old October 7th, 2005, 08:23 AM
external usenet poster
 
Posts: n/a
Default


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.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sort descending by THREAD Michael G. Scott General Discussion 1 August 27th, 2004 08:53 PM
outlook 03 hangs, error and won't open even in safe mode Dan Installation & Setup 25 August 3rd, 2004 01:16 AM
Can you delete a thread? The Old Timer Outlook Express 6 June 15th, 2004 07:46 AM
Saving posts thread in OE 6? Michael Drach Outlook Express 0 May 30th, 2004 09:23 AM
Office XP Ed Lester Setup, Installing & Configuration 1 May 27th, 2004 09:30 AM


All times are GMT +1. The time now is 02:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.