Get the last inserted value

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"

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....

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;

Dave

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:

<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
    .AddNew
      lngID = !ID
    .Update
    .Close
  End With
  Set rst = Nothing
  Set dbs = Nothing
 
  InsertRecord = lngID

End Function

</code>

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:

<SQL>

  SELECT
    *
  FROM
    tblTable
  WHERE
    ID=InsertRecord("tblTable");

</SQL>

This will create and retrieve one new record when run.

/gustav

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.

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.

Suggestions:
1. Use a variant of the cactus_data code.
with rst
  .addnew
    !Name = "value"
    !DOB = #value"
    !Gender = "value"
  .update
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.
Exammple:
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.

> 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.

/gustav

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 *

" 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....

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.

" 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#?

Thanks!!!

That's a nice solution.

/gustav