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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
@@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
|
|||
|
|||
"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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
"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
|
|||
|
|||
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 | |
|
|
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 |