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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 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.
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
@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.
<<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.
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
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
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
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
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
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
ASKER
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/
https://www.mssqltips.com/sqlservertip/5079/sql-server-identity-system-function-comparison/
ASKER
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.
ASKER
My apologies Pat.
No problem. Jim can have my points.
Tell us what the BE is and we can provide more detail.