Question Unable to execute scope_identity() in access 2003

[jon_s]

Hello all!

I have just created my first access application ever using Access 2003 (also my first VB-experience). In a form i would like to find out which autoincrement-PK result I just had in the "after insert" event.

Im trying this:

Dim lpRowIdRecordSet As DAO.recordSet
    Set lpRowIdRecordSet = CurrentDb.OpenRecordset("SELECT SCOPE_IDENTITY() AS row_id")

But I get error 3085 "undefined function scope_identity()"
If I use @@IDENTITY I get zero all the time

Any ideas?

 

[DCrake]

You have not defined the table you are looking at.

 

[jon_s]

Thanks for an extremely fast answer!

Tried this instead:

    Dim lpRowIdRecordSet As DAO.recordSet
    Set lpRowIdRecordSet = CurrentDb.OpenRecordset("SELECT TOP 1 SCOPE_IDENTITY() AS row_id FROM tbl_activities")
    MsgBox lpRowIdRecordSet.Fields("row_id").Value

Still get the 3085 "undefined function scope_identity()"

More ideas?

 

[DCrake]

Here is a snipet of code doing what you are attempting

    ' Insert the data into table
    Set db = CurrentDb
    db.Execute SQL
    
    ' Get the ID of the record just added
    Set RS = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
    NewPersonID = RS!LastID
    RS.Close

 

[jon_s]

As I sated in my first post, @@identity always returns zero (is my code to pick up the value ok?). Second, I dont want to use @@identity, since it seems to have a global scope and might return wrong results if there are multiple simultaneous users.

Is there a master-DB or similar where I can investigate the wereabouts of the SCOPE_IDENTITY() function?

If I use an SQL-statement that excludes the FROM-part I get an error asking for an operator. I have found a lot of examples that excludes the FROM-part of this type of statements, so I guess that there situations when it works. But not for me.

Any additional ideas?

Edit: Tried this also (I am not yet allowed to post links): www dot pcreview dot co dot uk/forums/thread-3718186.php
Didnt help.

 

[gemma-the-husky]

I tried to use this and asked about it here (some time ago), and I can't quite recall the answer - but I think it was that the identity function only works in SQL back ends.

I ended up using an extra dmax to get the autonumber, and I think I then added more dlookup tests based on the autonumber, just in case someone else had updated the file in the few microseconds gap.

it is a pain, isn't it.

 

[jon_s]

gemma-the-husky:
It's kind of annoying. I have previously worked with SQLServer, Oracle, postgres and Firebird/InterBase, so i'm kind of used to a bit more complete toolboxes...

Anyhow it seems that i have to use the @@IDENTITY, since the SCOPE_IDENTITY() is not supported by the Access DB-engine (JET?). Issuing this command like this

    Dim lpRowIdRecordSet As DAO.recordSet
    Set lpRowIdRecordSet = CurrentDb.OpenRecordset("SELECT @@IDENTITY AS row_id;")
MsgBox "After insert @@IDENTITY = " & lpRowIdRecordSet.Fields("row_id").Value

executes without errors, but always returns zero, problably because the the object used by the form for posting the insert is not the same as CurrentDB.OpenRecordSet (which I use in the AfterInsert-event)

Im starting a new thread to discuss this rather different issue.

The answer to my original question is "SCOPE_IDENTITY() is not supported by JET/Access. Use @@IDENTITY"

The answer to my question about SELECT without FROM was I was missing a terminating ";" in my satement

 

[vbaInet]

I think with an ADO connection you can but probably not with DAO.

Here's how you get the newly added ID from a table in DAO:

    Dim rs As DAO.Recordset, db As DAO.Database
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select ID, [Field1], [Field2], ... [COLOR=Red][B]From TableName[/B][/COLOR];")
    
    With rs
        .AddNew
            ![Field1] = "Something"
            ![Field2] = "Another thing"
            .
            .
            .
        .Update
        
        .Bookmark = .LastModified
        
        msgbox "New ID is: " & !ID
    End With

    rs.Close
    Set rs = Nothing

 

[DCrake]

You can use @@Identity with DAO here is a pice of code that I have just tested and it worked fine

    DoCmd.SetWarnings False

    Dim varCtl As Variant
    Dim SQL As String
    Dim ControlArray, FieldNames As Variant
    Dim NewPersonID As Long
    Dim db As DAO.Database
    Dim RS As DAO.Recordset
    
   
    SQL = "insert into tblPeople("
    
    For Each ctl In Me.Controls
        If ctl.Tag <> "" Then SQL = SQL & ctl.Tag & ","
        Next ctl
    
    SQL = Left(SQL, Len(SQL) - 1) & ") values ("
    
    For Each ctl In Me.Controls
        If ctl.Tag <> "" Then SQL = SQL & "'" & ctl & "'" & ","  ' nb this assumes all fields are text
    Next ctl
    
    SQL = Left(SQL, Len(SQL) - 1) & ")"
    
    
    Debug.Print "Adding Delegate SQL:   " & SQL
    
    ' Insert the data into table
    Set db = CurrentDb
    db.Execute SQL
    
    ' Get the ID of the record just added
    Set RS = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
    NewPersonID = RS!LastID
    RS.Close
    
    Set RS = Nothing
    Set db = Nothing

 

[vbaInet]

You can use @@Identity with DAO here is a pice of code that I have just tested and it worked fine

Not tried it on my end but that looks like it works.

 

[jon_s]

DCrake:
I beleave your example is pretty much equivalent to my own example (posted in another thread, but reposted here) that I use to illustrate my problem. A relevant difference would be that I have not written any code for the insert; that is done by the From.

Dim lpRowIdRecordSet As DAO.recordSet
    Set lpRowIdRecordSet = CurrentDb.OpenRecordset("SELECT @@IDENTITY AS row_id;")
MsgBox "After insert @@IDENTITY = " & lpRowIdRecordSet.Fields("row_id").Value

Is there a significant difference beetween our examples? The code above executed by the AfterInsert-event for the form that issues the insert.

 

[DCrake]

It maybe that you are using a bound form whereby my method is using an unbound form.

 

[jon_s]

I have created my form with the wizard, making it represent a table. The Record Source is the table name in question, so I guess it is bound.

Having everything but the "insert" action bound would be nice. Is there a neat way to disable the automatic insert and do it manually instead, using your the example above?

 

[jon_s]

Solved it:

Me.row_id.Value

where "row_id" is the name of my AutoIncrement PK, returns what I need.

Thanks for all help!