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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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;

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 50

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 50

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 50

Expert Comment

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


Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

690 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