Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

@@Identity in Access

Status
Not open for further replies.

Dzovan

Programmer
Joined
Feb 7, 2007
Messages
1
Location
YU
Hi,

I am trying to insert a person info into the access table and to return it's new ID (that is autonumber of course) using SELECT @@identity statement. I'm using OleDb command with two statements in batch (Visual Studio 2005), but I get an exception. Ok, I understand it is not possible to have two statements in batch in Access, but how can I get ID of newly inserted item ??

thx,

Dzovan
 
I don't know anything about Visual Studio 2005, but you could create a query that returns just the Max value of your Autonumber, and then grab that value from the query. You would have to run the query after the update to get the latest value, but it might be a place to start.


Paul
 
Here is an example:

Code:
Const db = "C:\Docs\Tek-Tips.mdb" 

Set cn = CreateObject("ADODB.Connection")
  
cn.Open _
   "Provider = Microsoft.Jet.OLEDB.4.0; " & _
   "Data Source =" & db
   
set rs=CreateObject("ADODB.Recordset")

'tblA has an autonumber primary key
strSQL="Insert Into tblA (TextField) Values ('abc')"

rs.open strSQL, cn

strSQL="Select @@identity as NewID from tblA"

rs.open strSQL, cn

msgbox rs.fields("NewID")
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top