Retrieving last inserted record in dB 2

[barkerj]

Is there a way of retrieving the last record inserted into a database?

I use the usual sqlstatement INSERT INTO myDeliveries .......
and then use the Execute statement to process the query.

Some background:
When a new record is created in myDeliveries, MS Access auto allocates a unique random +ve or -ve integer for the ID field.

Unfortunately there are instances where a number of records have all the same field values (other than their ID).

I need to distinguish between these records so that I can retrieve the ID of the record that I last created.

eg: I have just created 5 records to which Access allocated the following IDs: -32, 46, 23, 11, -15.

The ID, -15, is the one I want to retrieve since it belongs to the last record I created.

Is there something I can do (other than a schema change)?

 

[zemp]

If you have Access 2002 you can use the @@Identity field. I am not sure if it is available in earlier versions. Search msdn for more information. Thanks and Good Luck!

zemp

 

[barkerj]

Thanks zemp.

I have checked out @@Identity but it will not be able to help me as I am interfacing with Access 97. My understanding is that this property is useful for when SQL Server is involved.

Any other ideas?

 

[zemp]

I thought it was also only in SQL Server as well. But aparantly it has been added to Access 2002.

The only other method I have used is to make another call to the database getting all records and hope that the newest one is the last one (rs.movelast).

If you use the rs.addnew method then after you update the recordset you can get a value for the field that is an autonumber (Access 2000).

Not much help, but maybe it will jog someones memory and they can give you better advice. Thanks and Good Luck!

zemp

 

[barkerj]

Zemp,

I have previously tried using MoveLast but with inconsistent results.

However, with AddNew it looks like I can bookmark the record last modified for later access in my program. Top stuff!

eg: rs.Bookmark = rs.LastModified

Thanks for your all your help.

 

[SemperFiDownUnda]

Access is a front end application. The actual database for Access 97 is JET
for 2000 it can be JET or MSDE (which is a SQL 7 engine)

people, including me just generically call JET databases as access but it is not really the case completely.

It has been a while but as I remember JET always tacks on records to the physical end of the database even if you delete a record that space is wasted until you compact the database. The fact that your "MoveLast" might not work is that because of some sorting order. If you have a createdon field you could use that to sort DESC and grab the first one maybe but then this only would work reliably in single user environments. Best way is to use as much info from the original insert to select that record by itself.