locked
Returning @@Idenity after an insert RRS feed

  • Question

  • Fellow Access'ers,

    I want to do a controlled insert that is driven from a select such as:

    mysql = "Insert into StatusTable (parentid, status, creationdate)

                 "Select TOP 1  ID, 3, now() from main table

    where aaaa = 3

          order by Extract Date"

    With the above, I have chosen to use a Db.Execute approach instead of an addnew.

    Then follow it with a Select @@identity to bring back the autonumber key from the statustable insert.

    With that said, what guarantees me that I will get the @@Identity for me. 

    So what I have decided to do is thinking wrapping this in a transaction even though it is only doing one dml statement.

    So here is the concept i have:

    Dim wrkCurrent as workspace

    Dim dbo_dao as dao.database

    dim myrs as dao.recordset

    dim mykeyid as integer

    Set wrkCurrent = DBEngine.Workspaces(0)

    set dbo_dao = Opendatabase(connection,false)

    wrkCurrent.BeginTrans

    dbo_dao .execute = mysql

    set myrs  = dbo_dao.Openrecorset ("Select @@identity as mykey")

    if not myrs.eof then

    mykeyid  = myrs!mykey

    else

    mykeyid   = 0 

    endif

    myrs.close

    wrkCurrent.CommitTrans

    myDb_DAO.Close
    wrkCurrent.Close

    Now with all of that above:

    1 - Will it bring back MY identity record

    2 - If 1 is correct, is using a transaction necessary for assuring bringing back the correct @@idenity? Other examples do not show using a transaction and i am unsure if that is really necessary.

    thanks,

    MG


    Thursday, August 18, 2016 6:53 PM

Answers

  • I have never worked in a "highly disconnected" world. That's not what Access is made for (though it can be stretched to do that). I set these variables in my startup code and set them to nothing in my shutdown code.

    -Tom. Microsoft Access MVP

    • Marked as answer by mg30 Friday, August 19, 2016 3:00 PM
    Friday, August 19, 2016 2:54 PM

All replies

  • Hi MG. Have you tried you idea yet? What were the results? I have not used transactions before when trying to retrieve the assigned ID for the new record and haven't had any problems. The trick is to use the same connection to make sure you're getting the correct ID. For example:

    With CurrentDb
        .Execute strSQL
        lngNewID = .OpenRecordset("SELECT @@IDENTITY")(0)
    End With

    Just my 2 cents...

    Thursday, August 18, 2016 8:30 PM
  • @theDBguy: But that is what he is doing. Notice the use of myDB_DAO for both .Execute and .OpenRecordset. I agree with you this should be working.

    @mg30: when using a recordset, always use the most lightweight one. In your case a snapshot suffices, yet you are currently using the default heavy dynaset.

    If your insert is into a linked SQL Server table, you may need dbSeeChanges option.

    I think returning 0 to signify "not found" is a bad idea for 2 reasons. First it is a valid key value, and second EOF can only happen under error conditions, so it would be better to bubble up that error, or Raise one of your own.


    -Tom. Microsoft Access MVP

    Thursday, August 18, 2016 9:26 PM
  • Hi Tom. Thanks for clarifying. I guess I was so used to using a With/End With block I missed the name of the DB object. Cheers!
    Thursday, August 18, 2016 9:34 PM
  • HI Tom,

    Many thanks and to all. FYI, no linked tables.  All Access front end doing connection calls to different access backend.

    1 - I am not saying the @@Identity did not work. So far it is working. I was asking IF I should be using a transaction to help assure the correct one.

    In this point, I think having this in a transaction does not supply anything to help me guarantee that I get MY @@identity. it should be doing that on its own because I am using the same connection. Agree?

    2 - Yes, I will be flipping the no data return logic - this was a first pass scratch code and I will bubble up the error.

    3 - Yes, I will flip to snapshot.

    4 -  Opinion question. The DB declaration is at the form level (ie. Dim mydao_db as dao.database).Obviously I always close that connection object but if I always use that object, I would think that setting it to nothing after I close it is extra overhead to remove the pointer to the memory. In this scenario, is it preferred to NOT set that   mydao_db object to nothing after the  mydao_db .close or set it to nothing after the close?

    Thanks,

    MG

    Friday, August 19, 2016 1:08 AM
  • Yes I agree. Transactions are for a different use case, not yours.

    If Access FE and BE, I set two global variables at startup time, and keep them around for the duration of the session. Setting them at form level does not make much sense to me.


    -Tom. Microsoft Access MVP

    Friday, August 19, 2016 4:20 AM
  • HI Tom,

    Sorry about that. I said form level but meant to say Global. I do the same as you. But my question is, do you stil set that variable to NOTHING after you close the connection in a subroutine?  I don't keep the connection open - this design for this is highly disconnected concept.

    Thanks,

    MG

    Friday, August 19, 2016 11:28 AM
  • I have never worked in a "highly disconnected" world. That's not what Access is made for (though it can be stretched to do that). I set these variables in my startup code and set them to nothing in my shutdown code.

    -Tom. Microsoft Access MVP

    • Marked as answer by mg30 Friday, August 19, 2016 3:00 PM
    Friday, August 19, 2016 2:54 PM
  • Hi Tom,

    Thanks for the info. Yes, I have used Access on "rare" occasions over the years - like just a front end.

    This project will be moved to something else likely later but for now, this approach is best for our needs.

    You have answered my question. I do the same as you, I set the objects to nothing on close out. I was just wondering if it is best practice setting the db object to nothing EVERYTIME after I close even though it is a global variable object. I am leaving it as is. I will set to nothing on form exit.

    Thanks again.

    Friday, August 19, 2016 3:00 PM