Link to home
Start Free TrialLog in
Avatar of Dustin Stanley
Dustin Stanley

asked on

How To Retrieve the Identity Value While Inserting Records into Access Database When Using a Append Query

When I am inserting records into my table called Customers with a Append Query how can I retrieve the CustomerID Autonumber Value Access has created.

I have thought about the "DLast Function" but this Append Query will be running Programmatically (On its Own) and what if another user inserts a record in between the "DLast Function" and the Append Query firing???? 1 in a 1,000,000 chance but it can happen and then that could cause HUGE issues.

Thanks for the help.
Avatar of PatHartman
PatHartman
Flag of United States of America image

You can't do this using an append query.  You can only do it using DAO (or ADO).  If you are using Jet or ACE, You will have access to the ID as soon as the .AddNew executes.  However, if you are using SQL Server, you will need to use the @@Identity function to retrieve the last identity column inserted on this thread.

Tell us what the BE is and we can provide more detail.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dustin Stanley
Dustin Stanley

ASKER

Pat you know I have seen these remarks (Jet, ACE, DAO, ADO) mentioned before but I truthfully have no knowledge in this area yet.
I am just using a (.accdb) file on a windows PC.

Thanks Jim. For the link. I am hoping for some time tonight  to work on this project.

Thanks for the help!
I'm not sure that the suggestion will work.  I haven't tested it but when you add a record using an append query, it is NOT part of the recordset instantiated by the form, nor will it become part of it unless you requery the form.  Once you requery the form, you also will bring in any record added by another user ans so you will not necessarily get the ID you need.

Here is some code from one of my apps.  Originally it was linked to an .accdb and used the method you need (look at the line that is commented out).  When I changed it to link to SQL Server, I switched it to use the @@Identity function.  You can't actually see where the ID is used because it isn't relevant but it is being passed to a function that is doing a rate change.

    'add new record
    Set db = CurrentDb()
    Set td = db.TableDefs!tblProviderProcedures
    Set rs = td.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    
    DoCmd.RunMacro "mWarningsOff"
    For Each i In Me.lstProviders.ItemsSelected
        'update current record to close it
        strSQL = "UPDATE tblProviderProcedures SET tblProviderProcedures.TermDT = #" & Me.txtTermDT & "#, tblProviderProcedures.ChangeBy = " & Forms!frmLogin!txtEmpID
        strSQL = strSQL & ", tblProviderProcedures.ChangeDT = Now()"
        strSQL = strSQL & " WHERE tblProviderProcedures.ProvProcID = " & Me.lstProviders.Column(0, i)   'same as me.lstProviders.ItemData(i)
        DoCmd.RunSQL strSQL
        'add new record
        rs.AddNew
            rs!ProcedureID = Me.lstProviders.Column(5, i) 'Me.cboProcedureID
            rs!ProviderID = Me.lstProviders.Column(6, i) 'Me.ProviderID
            rs!EffDT = Me.txtNewEffDT
            rs!PricePerUnit = Me.txtNewPricePerUnit
            rs!AppliedDT = Date                 'update providerprocedures to record that rate change was applied
            rs!ChangeBy = Forms!frmLogin!txtEmpID
            rs!ChangeDT = Now()
''''             NewProvProcID = rs!ProvProcID   '''''' use for Jet/ACE back ends
        rs.Update
 
            Set rsScope = db.OpenRecordset("SELECT @@IDENTITY as NewID")	''''  Use for SQL Server Back ends

            NewProvProcID = rsScope!NewID
            
        If Me.fraWhatToUpdate = 2 Then
            RecCount = RecCount + ApplyRateChange(Me.lstProviders.ItemData(i), NewProvProcID, Me.txtNewEffDT)
        End If
    Next i

Open in new window

@Pat,

<<I'm not sure that the suggestion will work.  I haven't tested it but when you add a record using an append query, it is NOT part of the recordset instantiated by the form,>>

 As you already pointed out, it won't with an append query.   The thread I pointed to shows using the .LastModified property against the forms recordset, and that works with both ACE and SQL.  It also showed using the SELECT @@IDENTITY technique.   That thread also mentioned that you can grab the AN before the .Update if your using JET/ACE, so it hit all the points.

@Dustin,

<<Pat you know I have seen these remarks (Jet, ACE, DAO, ADO) mentioned before but I truthfully have no knowledge in this area yet. >>

   JET/ACE is the default database engine that you get with Access out of the box.   Many apps use SQL Server or mySQL to store data as well with Access apps.

   DAO and ADO are Data Access Libs, with PEMs (Properties, Events, and Methods) to let you access data.  DAO works well for JET/ACE and can be used for SQL and other ODBC data sources.

  ADO works better with other types of sources (OLEDB sources, spreadsheets, files, ODBC sources, etc), but it can be used for JET/ACE as well.  It's not as efficent as DAO when doing so though.   DAO was written specifically to support the JET/ACE database engine.

Jim.
Jim,
My bad.  I stopped reading after the first accepted answer.
Hi,

just to mention, in case of SQL Server you should better use the function "Scope_Identity" instead of "@@IDENTITY" as the function returns the ID of the same session where @@IDENTITY could return a value of another user.

See here:
https://docs.microsoft.com/en-US/sql/t-sql/functions/scope-identity-transact-sql

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

It is also a good idea to enclose the INSERT and the grabbing of the value inside of a transaction to make sure no one else could do something in between.

Cheers,

Christian
I could never get Scope_Identity to work and simply stopped fighting with it.  I think if you read further, the only problem with @@Identity is if you have triggers that insert rows into other tables.  Since I don't have that situation and I'm guessing that it is pretty rare out there in the wild, I decided that it didn't really matter enough to keep fighting with the @@Identity method..
Hi Pat,

don't understand why you have problems with that. I work exclusively only with Scope_Identity and it never failed.

Cheers,

Christian
For me it always generated an error.  I work exclusively with linked tables.  I believe that you work with ADp's.  There may be a difference.

Here's the two lines of code:

            Set rsScope = db.OpenRecordset("SELECT @@IDENTITY as NewID")
            '''Set rsScope = db.OpenRecordset("Select Scope_Identity as NewID") -- doesn't work
Hi Pat,

that's mainly not a problem of ADPs but the way you're executing it. Scope_Identity works absolutely only in the SAME batch. If you create a new recordset opening it with "OpenRecordset" that is a new batch which has nothing to do with a previously executed one. So in this case "@@IDENTITY" gives you the return value of the last inserted record - but that doesn't mean that it was your record, it could also be one of another user. Scope_Identity could in this case not return anything as you didn't insert a value into the table in the same batch.

If you want to see how it works you should for example create a stored procedure on the server and use this to insert a record within a transaction, then use Scope_Identity inside of this transaction immediately after the INSERT command to get exactly your ID back. If the transaction fails for any reason and you made a normal rollback then the ID is lost but you would not get a value back.

You can achieve the same if you create a Pass-Through-Query which consists of both commands, INSERT and SELECT Scope_Identity. Both are then executed inside of the same batch and then the value is also correct. The value you get back with "SELECT @@IDENTITY" can be anything and is more or less correct only by random if nobody else was faster between your INSERT and SELECT, especially when no Transaction was used.

Cheers,

Christian
This is a LOT of good info. I have been extremely swamped and not been able to focus on this but I REALLY enjoy the discussion as there is a lot to learn from it and to give me more food for thought. Thank you all for the input and help. I need to make some time to be able to focus and get this part done.
This is a really detailed explanation of how @@Identity and Scope_Identity work and I believe it shows that @@Identity works as people expect provided the original insert as I said, didn't set off a trigger that inserted a row in a different table.  It shows that if you check after one insert, you see the ID of that table.  If you do two inserts, you see the ID of the second table.  If you have one insert that triggers another,you see the ID of the second table.  This is where the two statements return different results.@@Identity will show the last ID inserted by the procedure but the scope extends to encompass the triggered insert whereas Scope_Identity doesn't follow the trigger.

https://www.mssqltips.com/sqlservertip/5079/sql-server-identity-system-function-comparison/
Seeing how I am using Access out of the box (JET/ACE) then I have chosen Jims Answer. But Pat that info is very interesting for if I scale my operation to SQL. Thanks a lot everyone for the help!
If you are using Jet/ACE, I gave you the answer you needed in my first post without making you follow a link.  You just didn't recognize it.
My apologies Pat.
No problem.  Jim can have my points.