Autonumbers Within Transaction?

[PeteCresswell]

Bond trading system.

Trader does a "Sell".

First thing, I write a single record to "tblTrade" and capture the
auto-numbered PK, which is TradeID.

Then I write many records to "tblTradeDetail", using the captured
TradeID to point to the parent tblTrade record.

But I intend to wrap that entire process in a transaction.

Do I have a problem? Or is JET smart enough to reserve that
AutoNumbered TradeID in the event that another user enters a trade
while my user is still in the process and has not committed their
transaction yet?

 

[mcescher]

Bond trading system.

Trader does a "Sell".

First thing, I write a single record to "tblTrade" and capture the
auto-numbered PK, which is TradeID.

Then I write many records to "tblTradeDetail", using the captured
TradeID to point to the parent tblTrade record.

But I intend to wrap that entire process in a transaction.

Do I have a problem?   Or is JET smart enough to reserve that
AutoNumbered TradeID in the event that another user enters a trade
while my user is still in the process and has not committed their
transaction yet?

Create the record in tblTrade, and then do a
dmax("[TradeID]","tblTrade") and use to capture the newly created
TradeID.

Hope this helps,
Chris M.

 

[mcescher]

Bond trading system.

Trader does a "Sell".

First thing, I write a single record to "tblTrade" and capture the
auto-numbered PK, which is TradeID.

Then I write many records to "tblTradeDetail", using the captured
TradeID to point to the parent tblTrade record.

But I intend to wrap that entire process in a transaction.

Do I have a problem?   Or is JET smart enough to reserve that
AutoNumbered TradeID in the event that another user enters a trade
while my user is still in the process and has not committed their
transaction yet?

Create the record in tblTrade, and then do a
dmax("[TradeID]","tblTrade") and use to capture the newly created
TradeID.

Hope this helps,
Chris M.- Hide quoted text -

- Show quoted text -

But if six people have added trades in that timeframe.....

That tells me that JET does not enque the Autonumber and I need to
make it just a Long and manage it myself - by whatever means.

Have I got it right?

Yes, I believe that is correct. Although, you could post their user
ID and make that a composite key.

something like this...

DMax("[TradeID]","tblTrade","[UserID] ='" & [UserID] & "' ")

Can one person enter more than one trade before entering the trade
details?

Hope this helps,
Chris M.

 

[Ken Sheridan]

I don't think you'd have a problem with the assigned autonumber value being
reused; as far as I can see its generated once the INSERT statement is
executed within the Transaction and is then no longer available to another
row. However, I can't see how you'd capture the assigned value while within
the Transaction. As the row has not yet been committed to the table there is
nothing to look up.

Ken Sheridan
Stafford, England