Getting the number of new that has just been inserted ?

[babe1898]

Hello Everyone,

I'm using Micorsoft Access and the key of one of the table,say ID, is automatically generated. So, after inserting a row, i would like to get the row number(ID) of the row that has been inserted.What is the SQL command for this? Could anyone help me on this? Thanks

 

[hneal98]

select max(ID) from tableName

 

[cmmrfrds]

If this is a multi-user application and other users can be inserting data then doing a select on max id is not a good idea since another user may have just inserted a record. If this is Access 2000 or above then use @@identity - it will work in all cases. Do a search on the Forum since this has been discussed before on tek-tips. If you cannot find a good example then post back.

 

[cmmrfrds]

Microsoft explanation of how to use @@identity in Access.

http://support.microsoft.com/defaul...port/kb/articles/Q232/1/44.ASP&NoWebContent=1

 

[KenReay]

Hi

If you are using SQL statement, then @@Identity is the way to go as stated in previous post by cmmtfrds,

If you are inserting using recordset code rather than SQL (ie using .AddNew .Update), then something like

...
Rs.Update
Rs.Bookmark = Rs.LastModified
LastId = Rs!Id

will do it

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[babe1898]

But i'm using SQL queries(INSERT..etc.)...and also how should i use the @@identity?

tHANKS

 

[cmmrfrds]

The is the essential code in the Microsoft example.

Dim yourSQL as string, cn as New ADODB.Connection
Dim rs as New ADODB.Recordset

Set cn = CurrentProject.Connection
yourSQL = "Insert etc........"
cn.Execute yourSQL
'-- could make sure there were no errors
yourSQL = "Select @@identity"
Set rs = cn.Execute yourSQL
Debug.print "Your new identity = " rs(0)

There is something called identity scope in SQL Server, but unless you are dealing with triggers you should get the id of the record you just inserted.