Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ijwilliamson
Starting Member
2 Posts |
 Posted - 2005-05-31 : 14:55:44
|
| I have a vba script in access which uses DAO to add a new record to a table on the SQL 2000 server. The table has a autonumber field(int identity method)Once I have added the new record, I need to imediatly read the autonumber generated for it. However, I always end up with the first record. Is there a way to leave the new record as the current record?Ian |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2005-05-31 : 16:55:45
|
| You should be able to execute select @@IDENTITY using your already open connection to the database. This works in access as well as sql and will return the last id created by your connection.Mike"oh, that monkey is going to pay" |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-05-31 : 17:01:03
|
| If Access has SCOPE_IDENTITY() like SQL Server does, use that instead.Tara |
|
|
|
ijwilliamson
Starting Member
2 Posts |
Posted - 2005-05-31 : 19:22:50
|
| Thanks, I will try both of these in the morning.Ian |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-06-01 : 08:52:16
|
| Yes, Access does have the scope_identity() function, it works better. Because it works within the scope of that transactionAfrika |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-06-01 : 09:14:27
|
| This all depends on how you are using DAO to add the rows to your table. Are you using a Recordset object and adding new rows to it, or are you using Execute() to add new rows using SQL statements? How about a brief snippet of relevant sample code so we can see what you've tried?Either way, I encourage you to use Stored Procedures to do this, and to use ADO since DAO should really only be used when you are working strictly with MDB (Access) databases and not links to SQL Server.- Jeff |
|
|
|
|
|
|
SQL int Identity & Access