Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    L.A., California
    Thanked 0 Times in 0 Posts

    Autonumber field in transaction (2000)

    Hello all,

    I'm trying to update a set of access tables.
    The Parent table has an Autonumber field which is the key to the table. Let's call the field "ParentID"
    The child tables have ParentID as one of the components of their primary key.

    I was adding a record in the parent table, and since I don't know if there's an Access equivalent to @@Identity, I was doing a query on the parent table to obtain the newly-added ParentID. I specified enough columns in the Where clause to guarantee it was the right record. I then used this number as the ParentID part of the child table writes.

    Now that all that worked, I want to enclose the whole thing in a transaction for security. Problem is, until the transaction is committed, I can't read for the new ParentID, the query returns 0.

    So.....Is there any way in Access to get the equivalent to @@Identity, which will work within the transaction?

    Thanks for any help.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 65 Times in 64 Posts

    Re: Autonumber field in transaction (2000)

    Not as far as I know within a transaction - but first of all let's find out what your back-end database is. If it is Access, then inside a transaction the answer is no. If on the other hand your back-end is SQL Server you have some other options. In any event, I would do your own auto-number and use a table to store the next number to be used. I presume this is happening under DAO, and there aren't any other really foolproof methods of making it work, especially in a multi-user environment. If you want further details on how to do your own autonumber, someone can help you out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts