Question @@IDENTITY in Access (1 Viewer)

yonelay · Dec 2, 2008

How do I get the ID of the last record entered in access?

DJkarl · Dec 2, 2008

Access records are stored randomly, unless you are date/time stamping the records as they are added, or using some type of incrementing ID number there is no reliable method to get the last record added.

RuralGuy · Dec 2, 2008

From MVP Allen Browne

Code:

Function ShowIdentity() As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = DBEngine(0)(0)
    db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"

    Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
    ShowIdentity = rs!LastID
    rs.Close

    Set rs = Nothing
    Set db = Nothing
End Function

yonelay · Dec 2, 2008

Can you please let me know from SQL stored procedure?

RuralGuy · Dec 2, 2008

Sorry but I do not have any idea.

KenHigg · Dec 2, 2008

I would think you could just open the table as a recordset and use a .movelast - ?

boblarson · Dec 2, 2008

KenHigg said:
I would think you could just open the table as a recordset and use a .movelast - ?

Not reliable because, as has been mentioned, Access does not necessarily return records in the same order each time when you open a table view (unless you are using a query instead and with a specific sort order).

pbaldy · Dec 2, 2008

You can use @@IDENTITY within the stored procedure, and have it passed back to Access as a return value (output parameter). I typically use the ADO Command object to execute the stored procedure and get the value back. Look in VBA Help at that (and BOL for the SP side) and see if it gets you on the right track.

KenHigg · Dec 2, 2008

Have you tested this theory or just read it somewhere?

boblarson · Dec 2, 2008

http://www.access-programmers.co.uk/forums/showpost.php?p=108482&postcount=3

KenHigg · Dec 2, 2008

boblarson said:
http://www.access-programmers.co.uk/forums/showpost.php?p=108482&postcount=3

Hum... That's interesting. I did a simple table with an integer pk. Then entered 5 records with pks 10, 20, 30, etc. Then closed they table and re-opened it an they were in order of the pk. So I entered a 6th record with a pk of 5. Closed the table and when I reopened it the 6th record I entered was at the top. Then when I used recordsets it did the same thing. Thanks for the tip Bob - And disregard my recordset suggestion...

Pat Hartman · Dec 2, 2008

DMax() will get you the value of the highest key or datetime if you have a last update date field. However, as has already been mentioned, this is unreliable in a multi-user environment since you cannot be sure who entered the last record. If you log the userID as well as the datetime, looking for the max() datetime field for the current user should get the record you want as long as users only log onto a single PC at a time. But, with SQL server, using the @@Identity is preferable.

datAdrenaline · Dec 2, 2008

@@Identity is completely reliable in JET/ACE (Access) too ... However, the @@Identity query must be executed by the SAME db session (DAO.database or ADODB.Connection object. Note that each call to CurrentDb produces a different db 'session') as the INSERT query and will reveal the last value the db session has inserted ... even if other users have inserted records prior to the @@Identity query execution ...

datAdrenaline · Dec 2, 2008

Hello Ken ...

>> That's interesting. I did a simple table with an integer pk. Then entered 5 records with pks 10, 20, 30, etc. Then closed they table and re-opened it an they were in order of the pk. So I entered a 6th record with a pk of 5. Closed the table and when I reopened it the 6th record I entered was at the top. <<

That is because if no ORDER BY exists when retrieving records, the recordset will be in the order to the PK, if one exists, if no PK exists, I am fairly certain that the records will be returned in time-of-entry order ...

Check out the following article on JET ..
http://support.microsoft.com/kb/137039/en

Specifically the 4th bullet point ... I know the article is regarding JET 3.0, however I have varified with a developer on the Access team that the same methodologies apply in JET 4.0 and ACE.

datAdrenaline · Dec 2, 2008

Hello DJKarl,

>> Access records are stored randomly, <<

Check out the link I provided in response to Ken ... my understanding of the reading is that records are NOT stored randomly.

Pat Hartman · Dec 2, 2008

"That is because if no ORDER BY exists when retrieving records, the recordset will be in the order to the PK, if one exists, if no PK exists, I am fairly certain that the records will be returned in time-of-entry order ..."
The records only appear to be returned in PK order due to the fact that when a database is compacted, table rows are reordered in key sequence. As soon as you do an add/change/delete, you disturb the "clustered" nature of the sequence and so all bets are off.

DCrake · Dec 3, 2008

When using autonumbers in Access as primary keys Access will generate the next ID as soon as the user types somthing into an empty field on the new record. However SQL Server assigns the new number at the point the record is saved. That's my interpretation of it anyway. So if a user is part way through adding a new record in Access and another user requests the identity they should in theory get the next number after the one being edited. Having said that if the user then aborts the addition after the other user has saved their record there should in essence be a gap in the numbers even though there is no missing record (theoretically speaking, not tested).

When requesting the @@IDENTITY it is best to do so at the latest possible time and not hold onto it as a variable. In the past I used to generate an intended invoice number based on the @@IDENTITY and display it on the form. The user then completed the form and saved the invoice. In the meantime someone else did the same thing but faster and duplicate invoice numbers were generated.

David

datAdrenaline · Dec 3, 2008

Hello David ...

>> When requesting the @@IDENTITY it is best to do so at the latest possible time <<

With a JET back end the @@IDENTITY value returned has nothing to do with what other users (sessions) are doing with the table. @@IDENTITY will return the LAST value that was inserted by that session ...

To show this, use the following code in TWO separate MDB's that open a back end data file... when you get to 'Stop' statement in one, start the same routine in the other db to similate multi user INSERT statements ... (comment out the STOP in the second db) ... once the second db is complete, then single step the first, you will see that the first db kept track of what it inserted, and the second kept track of what it inserted ... {Note: the table structure is assumes to be: RecordID (AutoNumber/PK), Somefield (text,50)}

Code:

Public Sub IdentityTestJET()
    
    Dim strSQL As String
    Dim db As DAO.Database
    
    Set db = OpenDatabase("C:\SomeFolder\SomeDB.mdb")
    
    strSQL = "INSERT INTO tblSometable (SomeField)" & _
             " VALUES ('My New Value: "
    
    'Insert a record
    With db
        .Execute strSQL & Timer & "')", dbFailOnError
        Stop
        With .OpenRecordset("SELECT @@IDentity")
            Debug.Print "Just inserted: " & .Fields(0)
            .Close
        End With
    End With
    
End Sub

datAdrenaline · Dec 3, 2008

Pat Hartman said:
The records only appear to be returned in PK order due to the fact that when a database is compacted, table rows are reordered in key sequence. As soon as you do an add/change/delete, you disturb the "clustered" nature of the sequence and so all bets are off.

Well ... I stand by my original statement in that the order of the records will be in PK order if no ORDER BY clause is involved with the retrieval of the records..... But ..... I will add that the key to whether or not the PK ordering is applied has to do with the type of recordset that is being populated by the retrieval process. If the recordset type is dbOpenDynaset, then the PK order will be applied if no ORDER BY clause is part of the retrieval request. With any other recordset type, the records will be returned in time of entry order.

To demonstrate, lets go through this excercise ...

tblSometable
------------
RecordID (Number/Long, PrimaryKey)
SomeField (Text (50))
TimeOfEntry (Date/Time, Default Value = Now())

Then enter some records (through the datasheet view of the tabledef) ...

RecordID, SomeField, <accept default of Now() for reference>
10, First Entry, <timestamp>
20, Second Entry, <timestamp>
30, Third Entry, <timestamp>
40, Fourth Entry, <timestamp>
5, Fifth Entry, <timestamp>

Now, close the datasheet view ... DO NOT COMPACT AND REPAIR ...

Now ... open the datasheet view again, you will see the fifth entry is on top (listed first) because the PK index will be used to order the records upon retrieval since no ORDER BY has been specified with the retrieval and the recordset type retrieved by the datasheet view is dbOpenDynaset (2). This is true regardless of whether or not the clustering (storage order) matches what is displayed... (This is just as Ken described his post) ...

Now if you go to code and retrieve the data as a recordset ...

Code:

With CurrentDb.OpenRecordset("SELECT * FROM tblSometable")
    Do Until .EOF
        Debug.Print .Fields(0)
        .MoveNext
    Loop
    .Close
End With

You will see that RecordID 5 is still on top ... note that the default recordset type is dbOpenDynaset, when that type can be acheived by the OpenRecordset method.

Now switch it up a bit ... make the recordset a snapshot ...

Code:

With CurrentDb.OpenRecordset("SELECT * FROM tblSometable", dbOpenSnapshot)
    Do Until .EOF
        Debug.Print .Fields(0)
        .MoveNext
    Loop
    .Close
End With

NOW ... since the returned recordset is a snapshot, the records returned are in order based on their time of entry.

.....

Now ... do the same exercise with a table that does NOT have an PrimaryKey (or any other index). The order of the records will be in entry sequence. Do take note that this example did NOT involve deleting of records, re-entry and such .. but I feel confident about the PK order being used for a dynaset recordset if no ORDER BY is specified. When I have time, I will play with the NON PK'd table ... but from what I gather in that JET 3.0 information, the records will come back in the order in which JET/ACE saves them.

Pat Hartman · Dec 4, 2008

Well ... I stand by my original statement in that the order of the records will be in PK order if no ORDER BY clause is involved with the retrieval of the records.....

You can't make that as an unequivical statement. As soon as a record is modified so that its length changes and so it needs to be moved, key sequence will not be maintained. You are working with recordsets too small to show this situation. You need to have records that are long enough and enough total records so that the whole set doesn't fit into a sector - or whatever is the physical block size Access uses. Depending on how long since the table was compacted, a record may need to be moved a substantial distance from its original position if its size changes when it is updated. If Jet were to return the recordset in key sequence, it would need to use the primary key index to locate each record which is an inefficient method of retrieval. Keep in mind that jet does not reuse waste space when records are deleted or moved. This space is not recovered until the database is compacted.

Question @@IDENTITY in Access (1 Viewer)

New member

Registered User.

AWF VIP

New member

AWF VIP

Registered User

Smeghead

Wino Moderator

Registered User

Smeghead

Registered User

Super Moderator

AWF VIP

AWF VIP

AWF VIP

Super Moderator

Remembered

AWF VIP

AWF VIP

Super Moderator

Similar threads

Users who are viewing this thread