Get id after insert - MS Access 2

[44nato44]

Hi

I got a MS Access DB which I use, I would like to get the AutoID after my Insert command

I would like to use "SELECT @@IDENTITY", but cannot get it to work, I hope there is somebody who can help.

I know I can use a new select and then find my record but I would like to use the Identiy which is more elegang to use )

Cheers

This is how I conntect to my DB:

'Set Conn = Server.CreateObject("ADODB.Connection")
DSNTA = "DRIVER={Microsoft Access Driver (*.mdb)}; "
DSNTA = DSNTA & "DBQ=" & Server.MapPath ("/_private/DB/Database.mdb")

Here is my Insert String:

SQLRsAddUser = "INSERT INTO tblTriSecActis (UserName, Password, FullName, Handle, Email, TriActis) VALUES ('" & varUserName & "', '" & varPassword & "', '" & varFullName & "', '" & varHandle & "', '" & varEMail & "', " & cbool(varTriActis) & ")"
conn.execute(SQLRsAddUser)

 

[vicvirk]

use a stored procedure, or in access a query, to do your insert.

It should return the record you just added on set rs = conn.execute and you can retrieve it by rs("myIdentityField")




--------

GOOGLE is a great resource to find answers to questions like "how do i..."

http://www.google.com/webhp?complete=1&hl=en

--------

 

[foxbox]

I situations where i know i am the only one adding records i usually do this:

cSQL = "INSERT INTO MyTable ..etc.."
conn.execute(cSQL)
cSQL = "SELECT max(<autonumber field>) FROM MyTable"
set rs =conn.execute(cSQL)
response.Write "Last id = " & rs(0)

 

[guitarzan]

I would like to use "SELECT @@IDENTITY", but cannot get it to work

Define "cannot get it to work"... are you getting an error? The code below should work:

sql = "INSERT INTO ...."
Conn.Execute sql

sql = "SELECT @@IDENTITY AS NewID"
rs.Open sql, Conn
NewID = rs("NewID")
rs.Close

 

[44nato44]

thanks a lot.. just what I needed

 

[tsuji]

>just what I needed
Why is that? The advice of guitarzan is good, no problem. But you have ms Access. Does it support @@identity? I think, no? not true now?!

If it is the alias bit you did not know, you can always do the same thing like this.
[tt]
sql = "SELECT @@IDENTITY"
rs.Open sql, Conn
NewID = rs(0).value
[/tt]
But since, Access does not support that approach of @@identity, that's the reason why you are also advised by foxbox to use max() on the auto-increment primary key. Does this clear up why you've received different approaches?

 

[foxbox]

http://www.iknowkungfoo.com/blog/index.cfm/2008/6/1/Please-stop-using-SELECT-MAX-id

The Jet 4.0 provider supports @@Identity. The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection.

This last bit is important, because it means that the Connection object used for the Insert query must be re-used without closing it and opening it up again. Access doesn't support batch statements, so each must be run separately.

http://databases.aspfaq.com/general.../autonumber-value-for-the-row-i-inserted.html

 

[tsuji]

The problem is on Access rather than anything else. The script layout is a feasible approach. The conn object is not closed out in any of the intermediary steps.