Retrieving autonum / IDENTIFIER value from SQL table using DAO.

[Erland Sommarskog]

Re: Retrieving autonum / IDENTIFIER value from SQL table using DAO.

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:[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[rkc]

Re: Retrieving autonum / IDENTIFIER value from SQL table using DAO.

Rico wrote:[color=blue]
> 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.OpenRecordse t("MyTable")
> rst.AddNew
> rst!MyTextfield ="My New Text"
> MyNewID=rst!IDf ield ' (this is the autonum field from the previous Access
> db)
> rst.Update
>
>
> I changed the code to
>
> dim MyNewID as long
> set rst = db.OpenRecordse t("MyTable")
> rst.AddNew
> rst!MyTextfield ="My New Text"
> rst.Update
>
> MyNewID=db.Open Recorset("SELEC T @@Identity").Fi elds(0)[/color]

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.

[Lyle Fairfield]

Re: Retrieving autonum / IDENTIFIER value from SQL table using DAO.

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