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!

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

Get id after insert - MS Access 2

Status
Not open for further replies.
44nato44

44nato44

Programmer
Dec 12, 2008
115
NL
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 :eek:)

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)
 
Sort by date Sort by votes
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..."


--------
 
Upvote 0 Downvote
I situations where i know i am the only one adding records i usually do this:
Code: 
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)

 
Upvote 0 Downvote
44nato44 said:
I would like to use "SELECT @@IDENTITY", but cannot get it to work
Click to expand...
Define "cannot get it to work"... are you getting an error? The code below should work:
Code: 
sql = "INSERT INTO ...."
Conn.Execute sql

sql = "SELECT @@IDENTITY AS NewID"
rs.Open sql, Conn
NewID = rs("NewID")
rs.Close
 
Upvote 0 Downvote
>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?
 
Upvote 0 Downvote


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.


 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
204
penguinspeaks
penguinspeaks
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
504
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
Classic ASP multiple record update with Access
Replies
7
Views
218
dhookom
dhookom
Krus1972
  • Locked
  • Question
msxml3.dll error '80072f7d' - An error occurred in the secure Channel support
Replies
7
Views
346
Krus1972
Krus1972
Xsi
  • Locked
  • Question
Get FolderPath
Replies
2
Views
89
ChrisHirst
ChrisHirst

Part and Inventory Search

Sponsor

Back
Top