SEO

Hello,

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?

Any direction would be great.

Thanks!

Try:

select
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:1fB_f.527$7a.323@pd7tw1no...
Hello,

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?

Any direction would be great.

Thanks!

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

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

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

Tom Moreau (tom@dont.spam.me.cips.ca) writes:
> 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().

Then again, there are cases where @@identity will give you the correct
result, and scope_identity() will not.

Now, I don't know how DAO works, but the suggestion to use scope_identity()
relies on the somewhat risky assumption that .AddNew performs a straight
insert. If DAO sets up a prepared query, run sp_executesql, or runs some
temporary stored procedure, scope_identity will not work. Since DAO is
a fairly old API, I would not expect it to be too sophisticated. Then
again, using scope_identity() means that you rely on the implementation
of something that could change with a service pack or a new release. (Not
that such are bloodly likely for DAO.)

Using @@identity is better, because it relies at least only on your
own application and schema which you have more control over.

--
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)
writes:
> I wound up researching Scope_Identity and that lead me to @@identity. I
> wound up changing my DAO code as follows;
>...
> 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.

Not only that, DAO is an API that has been deprecated for a long time.
The recommended API for an Access application today, I guess still is
ADO. (Which, I will have to admit, is an API that I don't like very
much at all.)

--
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

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 Erland

> Then again, there are cases where @@identity will give you the correct
> result, and scope_identity() will not.

Could you give an example of when this might occur?

--
-Dick Christoph
dchristo@mn.rr.com
612-724-9282
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97A3F2A2F1723Yazorman@127.0.0.1...
> Tom Moreau (tom@dont.spam.me.cips.ca) writes:
>> 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().
>
> Then again, there are cases where @@identity will give you the correct
> result, and scope_identity() will not.
>
> Now, I don't know how DAO works, but the suggestion to use
> scope_identity()
> relies on the somewhat risky assumption that .AddNew performs a straight
> insert. If DAO sets up a prepared query, run sp_executesql, or runs some
> temporary stored procedure, scope_identity will not work. Since DAO is
> a fairly old API, I would not expect it to be too sophisticated. Then
> again, using scope_identity() means that you rely on the implementation
> of something that could change with a service pack or a new release. (Not
> that such are bloodly likely for DAO.)
>
> Using @@identity is better, because it relies at least only on your
> own application and schema which you have more control over.
>
> --
> 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

DickChristoph (dchristo99@yahoo.com) writes:

>> Then again, there are cases where @@identity will give you the correct
>> result, and scope_identity() will not.
>
> Could you give an example of when this might occur?

CREATE TABLE #xyz(a int IDENTITY, b int NOT NULL)
go
EXEC sp_executesql N'INSERT #xyz(b) VALUES(@b)', N'@b int', 12
SELECT scope_identity(), @@identity
do
DROP TABLE #xyz

While the example may look contrived, many client API uses sp_executesql
or similar under the hood. scope_identity() returns the latest generated
identity value in the current scope, so if you call back a second time
from the client to get the value, you can only hope the both commands
excecuted in the top scope of the connection.



--
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