Get the last inserted value

Posted on 2004-08-21
Last Modified: 2012-06-22
I would like to get the autonumber that was generated by an insert statement returned to my web application ( C#)

For example, in MS SQL I have a stored procedure that inserts the record and returns @@Identity as the output parameter.

In this way I am guaranteed that I am getting the identity generasted for the last inserted record - and not the value of another record that may have come in at the same time (such as would be the case if I did something like a SELECT MAX)

How is this done in Access?

Question by:mrichmon
  • 5
  • 5
  • 3
  • +1
LVL 45

Expert Comment

ID: 11862291
In my opinion, the closest you will get is to submit a second SQL statement to the MSAccess database, looking for the autonumber (key field) value for a unique combination of data values you inserted.

For instance, if the first three columns (Name, DOB, Gender) constitutes a unique identifier for a row, then submit the following:
Select ID
From Tablename
Where Name = "namevalueyouinserted"
And DOB = #date/value/youinserted#
And Gender = "gendervalueyouinserted"
LVL 35

Author Comment

ID: 11862518
that won't work.

I could come up with many examples where that would fail.

Additionally in the case I am working on there are few fields in one of the tables and therefore it is HIGHLY probable that there would be two inserted at the same time that had the same values.

In general I would not use access ... in fact I would tell most people to consider upgrading to a "real database".
I am working on that, but in the meantime I was hoping to find a workable solution....
LVL 34

Expert Comment

ID: 11862874
use an AutoNumber field

Open your table in design view, add a field call ID and set it to autonumber datatype

you can then use

SELECT Max(ID), otherField, someOtherField FROM tblMyTable
Groupby otherField, someOtherFiled;

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

LVL 49

Expert Comment

by:Gustav Brock
ID: 11863181
First, Access (or more precisely: Jet) is a real database but it is not "the" universal and unlimited database that fits every purpose.

Then, you can add a record and retrieve the ID using code:


Public Function InsertRecord(ByVal strTable As String) As Long

  Dim dbs   As DAO.Database
  Dim rst   As DAO.Recordset
  Dim lngID As Long
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strTable)
  With rst
      lngID = !ID
  End With
  Set rst = Nothing
  Set dbs = Nothing
  InsertRecord = lngID

End Function


Of course, change dbs (and even rst) to Static if the creation of records is intense.

If you are not capable of running anything else than a query from your app, wrap the function into a select query:




This will create and retrieve one new record when run.

LVL 35

Author Comment

ID: 11864903
flavo - Very WRONG.  

The field is autonumber,but that is the value I am trying to get - select MAX does NOT work - unless you are guaranteed to have ONLY one person ever using the database with only one connection .

cactus_data, I am not sure I see how that works.  Where does each portion of code go and how does the data get passed to the insert function?

ANd can that be called froma web page.  Access has a lot of things you can do in Access, that you cannot not do when connecting from a web page.
LVL 45

Expert Comment

ID: 11865046
The closest thing to a stored procedure in MSAccess is a stored query.  Other than replacing MSAccess with the MSDE or the SQL Server desktop edition (newly announced), I'm not sure you can do what you are asking in a simple manner.

1. Use a variant of the cactus_data code.
with rst
    !Name = "value"
    !DOB = #value"
    !Gender = "value"
end with
rst.Bookmark = rst.LastModified

Note: if using ADO instead of DAO, the newly inserted record becomes the current row, so you do not need to position to the newly inserted row (using the LastModified property as shown above).

2. Create a function within MSAccess to accept field value parameters, insert the row, and return the ID of the newly inserted row.  Create a select query that invokes the function.
Select fcnInsert([parmNameValue], [parmDOBValue] , [parmGenderValue])
From OneRowTable

Note: OneRowTable is a single-row table that facilitates the above query.

3. Create a data-layer between your program and the database.  Implement the same function as 2 (above) and invoke this data layer in place of direct SQL or direct ADO/DAO inserting.

4. Create the same function as 2 (above), but use MSAccess automation to invoke the function directly.  I mention this last because I am not sure this idea can be implemented.
LVL 49

Expert Comment

by:Gustav Brock
ID: 11865255
> Where does each portion of code go and how does the data get passed to the insert function?

> And can that be called froma web page.  
> Access has a lot of things you can do in Access, that you cannot not do when connecting from a web page.

That depends on how you connect to Access and/or the Jet engine.
As aikimark states, you have no stored procedures in Jet. You will need some kind of running application (your own or a supporting one) to run a function via DAO or ADO. I'm not a web-programmer and the options are to numerous to be more specific. Automation is certainly one option.

LVL 45

Expert Comment

ID: 11865693
There is also one more SQL solution I should suggest...

1. Do your Insert (with a stored query or dynamic SQL)...however you like.
2. Execute the following query:

Select ID
From MyTable
Where ID = (Select Max(N.ID) From MyTable As N Where name = "INameValue" And DOB = #IDOBValue# And Gender = "IGenderValue")

Although your non-autonumber values aren't unique, it is unlikely that duplicate rows would be inserted in the same timeframe by another process/user.  If all the additions were caused by your ASP.Net code, then you might be able to serialize this process (Insert & Select) in a function.  This example assumes your autonumber field is named "ID".

Note: To retrieve the entire row just inserted, use:
Select *
LVL 35

Author Comment

ID: 11865775
" it is unlikely that duplicate rows would be inserted in the same timeframe by another process/user"

Maybe in some cases - but definatley not in all and not in this case.  it IS likely that duplicate rows could be inserted by different users in the same timeframe - that is one reason we are trying to move this project away from access, but we have found limitations with MySQL which was the next options....
LVL 45

Expert Comment

ID: 11865809
The latest version of MySQL has stored procs. :-)

1. How many IIS processes are simultaneously inserting data into this table?

2. Have you looked at your .Net serialization (locking) options?  This would allow you to do your Insert/Select without others' data.

3. Have you looked into database transactions or other database locking mechanisms that might provide your inserting process serialized access to the table during the update?

4. What other simultaneous updaters are there to this table?

5. How about adding a column, while you are still using MSAccess?  You can generate a GUID as one of your non-autonumber columns and then invoke:
Select ID From MyTable Where colGUID = "{...}"

That way you will be <absolutely certain> that you are not getting a row ID inserted by another process/user.

Note: For a faster lookup, you could use a double-word datatype column. Supply the current datetimestamp for both your Insert and your Select.
LVL 35

Author Comment

ID: 11865879
" The latest version of MySQL has stored procs. :-)"

Only sort of....and we are still finding VAST limitations with even the alpha of version 5 which "supposedly supports" stored procs, but as I have said - only to a limted extent and tehre are other limitations.

Answers :

1) Could be a lot - which is why we are looking at upgrading the database.

2) looked, but was kindof confused

3) Yes - access support of them isn't that great that I have seen

4) don't know as of yet - this project will be expanding

5) Thought of that, but what is the function in access to create guid?  Or is tehre one in .Net C#?
LVL 45

Accepted Solution

aikimark earned 500 total points
ID: 11865986

System.Guid.NewGuid().ToString() //= 86692da1-8ab4-48cb-9356-6ae153b85f05
System.Guid.NewGuid().ToString("N") //= c9065b65331f40bd89e3cb4a6c1018ca
System.Guid.NewGuid().ToString("D") //= b4477dcb-c5c8-43c5-972f-ae4e10854ae3
System.Guid.NewGuid().ToString("B") //= {a61dfdb8-59f0-434b-a93e-12638bdf1d71}
System.Guid.NewGuid().ToString("P") //= (5cfa5cff-b0b6-4349-85da-3192456faa37)
LVL 35

Author Comment

ID: 11866208
LVL 49

Expert Comment

by:Gustav Brock
ID: 11868906
That's a nice solution.


Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question