Retrieving a newly created Primary key

B

Bob

In Access 97 code (DAO). I create a new record in a table by using an insert
statement and this is being done inside a transaction. How do I retrieve the
newly created PrimaryKey for further use? The primary key is an autonumber
and is not included in the insert statement.

For instance
Dim sql as string
Dim MyPk as long
Currentdb.begintrans
'some code that executes
sql = "Insert into MyTable (Fielddata1) Values (1)
Currentdb.execute (sql, dbfailonerror)
'at this stage I need to retrieve the newly created Primary and place it
in variable MyPK

'finishing code and closing transaction if successfull
 
A

Allen Browne

Bob, I don't think you can do that in A97.

The best solution would be to OpenRecordset() and AddNew, which gives you
access to the new number.

Typically you BeginTrans on the Workspace rather than the Database. In any
case:
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT * FROM MyTable;", dbOpenDynaset,
dbAppendOnly)
rs.AddNew
rs!FieldData1 = 1
Debug.Print rs!ID 'This works with JET tables
rs.Update
rs.Bookmark = rs.LastModified
Debug.Print rs!ID 'This works with other tables also.

In JET 4 (Access 2000 and later) you could do it like this:
db.Execute "INSERT INTO MyTable ( Fielddata1) Values (1);"
Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
Debug.Print rs!LastID
 
B

Bob

Thanks

Allen Browne said:
Bob, I don't think you can do that in A97.

The best solution would be to OpenRecordset() and AddNew, which gives you
access to the new number.

Typically you BeginTrans on the Workspace rather than the Database. In any
case:
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT * FROM MyTable;", dbOpenDynaset,
dbAppendOnly)
rs.AddNew
rs!FieldData1 = 1
Debug.Print rs!ID 'This works with JET tables
rs.Update
rs.Bookmark = rs.LastModified
Debug.Print rs!ID 'This works with other tables also.

In JET 4 (Access 2000 and later) you could do it like this:
db.Execute "INSERT INTO MyTable ( Fielddata1) Values (1);"
Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
Debug.Print rs!LastID
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can't seem to retrieve primary key number of newly created record in VB 1
How to get the primary key back after you done a single line insert? 2
Modify a primary key 5
Listbox to open a form (primary key text) 2
How to carture the newly autonumber in the primary field? 1
How do I get back a newly created Primary key 3
getting lastModified in access2007 1
Retrieve value after insert 3

Top