I am in a similar situation to you and am trying the following:

theRecord.AddNew
' new data values
theRecord.Update
theRecord.Bookmark = theRecord.LastModified
theNewID = theRecord("ID")

I expect the experts will find this wanting but, so far, it seems to
work. I suppose that there might be a timing issue immediately after
the Update.

Lyle, this isn't a ground up application, this is converting a clients
legacy application. The bean counters have better things to do with their
budget than build a new version of something they are already using.

I never said I wouldn't learn about stored procedures, but don't have the
time in this case.

"Lyle Fairfield" <lylefairfield@aim.com> wrote in message
news:1144906536.025890.26030@j33g2000cwa.googlegro ups.com...
> It is enormously absurd to use DAO with MS-SQL Server.
> It is enormously absurd for the OP to say he will not learn about
> Stored Procedures.
> It is enormously absurd to use ODBC and DAO with MS-SQL.
> I KNOW, knowledgeable insiders say that is the route to take.
> I say the knowledgeable insiders say so because they want to promote
> Access as a front end for MS-SQL to those who are too lazy or and or
> too stupid to learn MS-SQL and ADO.
> Moreover, to those who are offended by this I say, "Get off you ass and
> learn your trade and then you won't be!"
>

Hi Tom,

Just so you know, triggers and other server side operations will not affect
the @@identity result and hence, will not return an incorrect result.

Rick


"Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message
news:Pwa%f.3830$L.26943@news20.bellglobal.com...
> Don't use @@IDENTITY. You can have incorrect results if your INSERT fires
> a
> trigger which itself inserts into a table with an identity. Use
> SCOPE_IDENTITY().
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN
> CAPS> wrote in message news:sG9%f.5965$WI1.5577@pd7tw2no...
> Thanks Tom and Erland,
>
> I wound up researching Scope_Identity and that lead me to @@identity. I
> wound up changing my DAO code as follows;
>
> Instead of....
>
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> MyNewID=rst!IDfield ' (this is the autonum field from the previous Access
> db)
> rst.Update
>
>
> I changed the code to
>
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> rst.Update
>
> MyNewID=db.OpenRecorset("SELECT @@Identity").Fields(0)
>
> This seems to work in every case, since the @@Identity line gets the last
> ID
> created on your specific connection whether someone else updates the
> database as the same time or not. In other words, if I update the
> database
> at the same time another user updates the database, the @@Identity will
> never pass me back the other users ID field since that wasn't created on
> my
> connection.
>
> Although my tests have proven successful, if anyone has exprience using
> this
> with DAO and has had any failures, please let me know.
>
> Erland, I wish I knew more about creating stored procedures, because I'd
> like to centralize as much of this kind of thing as I can, but at this
> point
> I have to stick with what I know. Thanks for the info.
>
> Rick
>
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns97A2F243F7168Yazorman@127.0.0.1...
>> Rico (r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN
>> CAPS)
>> writes:
>>> I am in the midst of converting an Access back end to SQL Server
>>> Express. The front end program (converted to Access 2003) uses DAO
>>> throughout. In Access, when I use recordset.AddNew I can retrieve the
>>> autonum value for the new record. This doesn't occur with SQL Server,
>>> which of course causes an error (or at least in this code it does since
>>> there's an unhandled NULL value). Is there any way to retrieve this
>>> value when I add a new record from SQL server or will I have to do it
>>> programmatically in VB?
>>
>> It's better to use stored procedures to add data, rather than relying on
>> ADO generating code behind your back. It's easy for the Jet provider
>> to populate the Autonumber for you, because all operations are in your
>> process space. But since SQL Server is on the other end of the wire,
>> there is an extra roundtrip to get the value.
>>
>> Also, with SQL Server, make sure that all your cursors are client-side.
>>
>> A sample stored procedure:
>>
>> CREATE PROCEDURE insert_tbl @a int,
>> @b datetime,
>> @c varchar(23),
>> @id int AS
>> INSERT tbl (a, b, c)
>> VALUES (@a, @b, @c)
>> SELECT @id = scope_identity
>>
>>
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/pro...ads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
>

"Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN
CAPS> wrote in news:caw%f.9156$P01.6110@pd7tw3no:

> Hi Tom,
>
> Just so you know, triggers and other server side operations will not
> affect the @@identity result and hence, will not return an incorrect
> result.
>
> Rick

That seems to be the opposite of what this exert from SQL 2005 BOL says.
I have made two sections UpperCase.

----------
"SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions
because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a
specified table. IDENT_CURRENT returns the value generated for a specific
table in any session and any scope. For more information, see
IDENT_CURRENT (Transact-SQL).

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are
generated in any table in the current session. However, SCOPE_IDENTITY
returns values inserted only within the current scope; @@IDENTITY is not
limited to a specific scope.

For example, there are two tables, T1 and T2, and an INSERT trigger is
defined on T1. WHEN A ROW IS INSERTED TO T1, THE TRIGGER FIRES AND
INSERTS A ROW IN T2. This scenario illustrates two scopes: the insert on
T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and
SCOPE_IDENTITY will return different values at the end of an INSERT
statement on T1. @@IDENTITY WILL RETURN THE LAST IDENTITY COLUMN VALUE
INSERTED ACROSS ANY SCOPE IN THE CURRENT SESSION. THIS IS THE VALUE
INSERTED IN T2. SCOPE_IDENTITY() will return the IDENTITY value inserted
in T1. This was the last insert that occurred in the same scope. The
SCOPE_IDENTITY() function will return the null value if the function is
invoked before any INSERT statements into an identity column occur in the
scope.

Failed statements and transactions can change the current identity for a
table and create gaps in the identity column values. The identity value
is never rolled back even though the transaction that tried to insert the
value into the table is not committed. For example, if an INSERT
statement fails because of an IGNORE_DUP_KEY violation, the current
identity value for the table is still incremented."

------------

A session is described as:

By default, a session starts when a user logs in and ends when the user
logs off. All operations during a session are subject to permission
checks against that user.


--
Lyle Fairfield

Hmmm,

My mistake. Never believe what you read the first time I guess. I got the
info from an MSDN forum page, but didn't bookmark the page, so I'll have to
find it again. I did find reference to something similar in the MSDN
library which mentions returning the expected Identity value after a trigger
has fired on a table without an identity field. Luckily there are no
triggers on this DB at this point, so that will at least buy me some time
until we can get something mapped out for the client.

Rick

"Lyle Fairfield" <lylefairfield@aim.com> wrote in message
news:Xns97A48FC10EA65lylefairfieldaimcom@216.221.8 1.119...
> "Rico" <r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN
> CAPS> wrote in news:caw%f.9156$P01.6110@pd7tw3no:
>
>> Hi Tom,
>>
>> Just so you know, triggers and other server side operations will not
>> affect the @@identity result and hence, will not return an incorrect
>> result.
>>
>> Rick
>
> That seems to be the opposite of what this exert from SQL 2005 BOL says.
> I have made two sections UpperCase.
>
> ----------
> "SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions
> because they return values that are inserted into identity columns.
>
> IDENT_CURRENT is not limited by scope and session; it is limited to a
> specified table. IDENT_CURRENT returns the value generated for a specific
> table in any session and any scope. For more information, see
> IDENT_CURRENT (Transact-SQL).
>
> SCOPE_IDENTITY and @@IDENTITY return the last identity values that are
> generated in any table in the current session. However, SCOPE_IDENTITY
> returns values inserted only within the current scope; @@IDENTITY is not
> limited to a specific scope.
>
> For example, there are two tables, T1 and T2, and an INSERT trigger is
> defined on T1. WHEN A ROW IS INSERTED TO T1, THE TRIGGER FIRES AND
> INSERTS A ROW IN T2. This scenario illustrates two scopes: the insert on
> T1, and the insert on T2 by the trigger.
>
> Assuming that both T1 and T2 have identity columns, @@IDENTITY and
> SCOPE_IDENTITY will return different values at the end of an INSERT
> statement on T1. @@IDENTITY WILL RETURN THE LAST IDENTITY COLUMN VALUE
> INSERTED ACROSS ANY SCOPE IN THE CURRENT SESSION. THIS IS THE VALUE
> INSERTED IN T2. SCOPE_IDENTITY() will return the IDENTITY value inserted
> in T1. This was the last insert that occurred in the same scope. The
> SCOPE_IDENTITY() function will return the null value if the function is
> invoked before any INSERT statements into an identity column occur in the
> scope.
>
> Failed statements and transactions can change the current identity for a
> table and create gaps in the identity column values. The identity value
> is never rolled back even though the transaction that tried to insert the
> value into the table is not committed. For example, if an INSERT
> statement fails because of an IGNORE_DUP_KEY violation, the current
> identity value for the table is still incremented."
>
> ------------
>
> A session is described as:
>
> By default, a session starts when a user logs in and ends when the user
> logs off. All operations during a session are subject to permission
> checks against that user.
>
>
> --
> Lyle Fairfield

Rico (r c o l l e n s @ h e m m i n g w a y . c o mREMOVE THIS PART IN CAPS)
writes:
> Lyle, this isn't a ground up application, this is converting a clients
> legacy application. The bean counters have better things to do with their
> budget than build a new version of something they are already using.

Nevermind the stored procedures, but not ripping out DAO while you're
at it, seems wrong to me. I don't know much about DAO, but since it is
a deprecated interface, there is risk that you will run into issues in
SQL Server that are not supported when you use DAO. (The most typical
example would be new data types.)




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Rico wrote:
> Thanks Tom and Erland,
>
> I wound up researching Scope_Identity and that lead me to @@identity. I
> wound up changing my DAO code as follows;
>
> Instead of....
>
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> MyNewID=rst!IDfield ' (this is the autonum field from the previous Access
> db)
> rst.Update
>
>
> I changed the code to
>
> dim MyNewID as long
> set rst = db.OpenRecordset("MyTable")
> rst.AddNew
> rst!MyTextfield="My New Text"
> rst.Update
>
> MyNewID=db.OpenRecorset("SELECT @@Identity").Fields(0)

If you use an ADODB.Recordset with the correct property settings
the the new record will be added to the recordset you have open
and the newly added record will be the current record.

!!!!!
LOL
What fun is there is you give good smart simple answers?