Incorrect record ID returned using @@Identity

[GaP42]

Hi. I am logging an import process and after creating the import record in the log table I want to use the id of that record later. I thought I could do this using @@identity and used the following code, however the value of autoID, when executed, is not tblTfrLog - it is an ID from another table. No other users on this db.

Where / what needs to be fixed?

    strSQLLog = "INSERT INTO tblTfrLog ( TfrSubType, TfrType, ImportStage, DateInitiated)" _
    & " VALUES ('" & LogSubType & "' , 'Import', 'Stage 3 Start', #" & Format(LogDate, "yyyy-mm-dd hh:mm:ss AM/PM") & "#)"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblTfrLog")
'    Debug.Print strSQLLog
    DoCmd.RunSQL strSQLLog
    
    'Retrieve the last inserted ID
    'Store the ID in a variable
    Set rs = db.OpenRecordset("SELECT @@IDENTITY")
    autoID = rs(0)
    Debug.Print autoID
    
    rs.Close

 

[arnelgp]

you can also use DMax("ID", "tblTfrLog"), to get the latest id.

 

[GaP42]

Thanks arnelgp - I had seen that as an alternate however I read elsewhere that it was less reliable (?) and that this will require all records to be scanned (retrieved to the workstation) to provide a result. Certainly a fall back position.

And yes - using DMAX did return the correct record ID.

 

[Isaac]

This question should be in the sql server forum for best channeling

 

[GaP42]

Thanks Isaac - but I am not using SQL Server - just the Access database - accdb.

 

[theDBguy]

Hmm, what's the purpose of setting rs as a recordset of the table but not using it? You would use @@identity when using sql. But, you can also find out the autoid from using the recordset you already created.

 

[arnelgp]

try:

Set rs = db.OpenRecordset("Select @@IDENTITY As Expr1 From tblTfrLog")

 

[cheekybuddha]

@@IDENTITY is scoped to the connection. You set up with Change CurrentDb (edit: fix phone's autocorrupt) but then execute with DoCmd.RunSQL.

Try:

' ...
    Set db = CurrentDb
'    Set rs = db.OpenRecordset("tblTfrLog") ' <- unnecessary
'    Debug.Print strSQLLog
    db.Execute strSQLLog, dbFailOnError
  
    'Retrieve the last inserted ID
    'Store the ID in a variable
    Set rs = db.OpenRecordset("SELECT @@IDENTITY")
    autoID = rs(0)
' ...

 

[ebs17]

@cheekybuddha addresses it correctly (dbFailOnError instead of dbFailOnErrot).

Why aren't many people bothered by this unspeakable DoCmd.RunSQL? DoCmd has no real references to objects, it does its actions (which mostly come from what is offered in the menus) where it thinks it is currently active. Therefore, there is also no connection with the recordset. Both instructions move in different worlds and do not know each other.

 

[June7]

DMax() would be unreliable if there are multiple simultaneous users.

 

[cheekybuddha]

(dbFailOnError instead of dbFailOnErrot).

Thanks, fixed.

 

[GaP42]

Thanks everyone. I have applied the cheekybuddha suggestion (with typo fix / ebs17 ) and confirm this works. As an amateur I appreciate how much you guys know and can spot the errors made. I was not getting anywhere with this for sometime.

 

[Isaac]

Thanks Isaac - but I am not using SQL Server - just the Access database - accdb.

Regardless, This is a sql server function and will be best known by those who traffic in SQL server. Anyway not trying to be picky, you're obviously getting to help you need it anyway but just letting you know.

 

[Pat Hartman]

If you are inserting a single record, use DAO and open a recordset so you can use .AddNew. You will always be able to get the generated autonumber by simply referencing it in the recordset. If you display your autonumber on a form, you will see it populate as soon as the form is dirtied. Same thing happens in DAO. In the line before the .Update, just copy the autonumber to autoID.

autoID = rs.MyAutonumber

PS - if any of you who use SQL server know the correct syntax for using Scopt_Identity, please post it. I've never been able to get it to work.

Why aren't many people bothered by this unspeakable DoCmd.RunSQL?

Access documentation sucks. A97 was the last version that came with good hard copy documentation. The manuals were available online for a while after that. And I kept the help system for A97 around long after I stopped using A97 just to be able to use the table of contents. Then, MS stopped supporting the help system used to create that documentation and the functionality died. Since then, the documentation, where it exists, is written by people who have no clue what a developer needs to know, ESPECIALLY a novice. The books that came with A97 are worth buying even today if you can find them, especially if you can get the full set. It is amazing how a good table of contents can help a novice to zero in on what he needs. The biggest problem novices have is that they don't know the right terminology. If you don't know the name of what you are looking for, how can you ever find it?

 

[Isaac]

PS - if any of you who use SQL server know the correct syntax for using Scopt_Identity, please post it. I've never been able to get it to work.

Scope_Identity? There's very little to it. I just wrote and executed this SQL and it worked fine, returning the value of 1:

drop table if exists #temp
create table #temp
   (
   ID int identity(1,1),
   OtherColumn varchar(1)
   )
 
 insert #Temp
     (
     OtherColumn
     )
values
    (
    'Y'
    )
    
select scope_identity()

--returned 1

 

[ebs17]

Why aren't many people bothered by this unspeakable DoCmd.RunSQL?

I maintain that habit formation is not based on reading aids that are getting worse, but primarily on copying from role models and from experts. As a newcomer, I imitate what I see, especially what I see frequently.

Now I ask the question: What am I offered as a newcomer and helpless questioner? The fact that the offered solution (only) works cannot be everything. Because the drowning man grasps every straw and is grateful for it. The one-eyed man becomes king for the blind man.

 

[Pat Hartman]

@Isaac I am quite capable of building the pseudo code. I have working code for using @@Identity. However Scope_Identity doesn't work in the following context. I don't remember the error message. I can't run the code right now since I don't have the database loaded any more.

        'add new record
        rs.AddNew
            rs!ProcedureID = Me.lstProviders.Column(5, i)
            rs!ProviderID = Me.lstProviders.Column(6, i)
            rs!EffDT = Me.txtNewEffDT
            rs!PricePerUnit = Me.txtNewPricePerUnit
            rs!AppliedDT = Date                 
            rs!ChangeBy = Forms!frmLogin!txtEmpID
            rs!ChangeDT = Now()
'            NewProvProcID = rs!ProvProcID   ''  use for Jet/ACE
        rs.Update
 
            Set rsScope = db.OpenRecordset("SELECT @@IDENTITY as NewID")
            '''Set rsScope = db.OpenRecordset("Select Scope_Identity as NewID") -- doesn't work
            NewProvProcID = rsScope!NewID