Results 1 to 2 of 2
2003-09-26, 22:24 #1
- Join Date
- Jan 2001
- L.A., California
- Thanked 0 Times in 0 Posts
Autonumber field in transaction (2000)
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.
2003-09-26, 23:45 #2
- Join Date
- Aug 2001
- Evergreen, CO, USA
- Thanked 64 Times in 63 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.Wendell