How do you prevent autonumber from triggering when Rollback occurs?

[Toga]

I just started learning about and trying my hand at using BeginTrans, CommitTrans & the Rollback methods.....and they seem to work really nice. What I notice though is when I force an error and the rollback occurs, the autonumber still triggers in one of my tables.

None of the data was entered as a result....which is a good thing, but the next time I run the query, without forcing an error, I notice that I skip a number in the autonumber field.

I think it's related to the .update occurring in my code in the first table, but before the error is forced on the second table that I'm updating. But like I say, the data never went into the table....at least as far as I could tell.

Is there a way to prevent the autonumber from triggering?

It prevented partial updates to some of the tables in the event of an error, which is the most important thing, and it's not really a big deal....I would just prefer to prevent it from happening if there's something else I can or should be doing. (Perfectionist Phobia!)

Thanks...Toga

 

[ghubbell]

Hi Toga! You iz he'ah alright! ;-) Just like a cancelled update query there's no turning back, at least not that I've found... but wait a sec... I understand your prefectionist concerns but it is just an autonumber. If you really need (or want) to keep everything "in line" you'll probably have to resort to a "man made number". Autonumbers to me are only useful to connect things having (again) learned the hard way how uncontrollable they really are. Take a look at Thread181-59887 for ideas or, as well, somewhere there's a FAQ regarding the same. You want the control, you take the control. It'll mean a little more coding but that's nothing compared to the satisfaction of knowing your numbers stay just the way you would like. Gord
ghubbell@total.net

 

[Toga]

Thanks Gord....

Since the code seems to be performing the most important function of what it's supposed to be doing from all other aspects, I think I'll just live with the autonumber route for now. I don't want to trade what's really a non-issue for a potential issue down the road by trying to create my own autonumber.

I was more currious than anything because no data ever went into the file from what I could see.....even when I steped thru the code line by line.....so why does it trigger???

Apparently, Access must take hold of that new record spot until it's told what to do via the Commit or Rollback methods....and either way, triggers the autonum.

I guess we'll just have to wait for Gates and crew to improve upon the rollback method to prevent this. I'm sure there's good reason why they don't stop the autonum from triggering......but again, I was more currious than anything.

Toga

 

[ghubbell]

Good (early) morning Toga! Let the thread hang around awhile as I would hope to see some other opinions~experience~knowledge regarding this too. Maybe someone knows a secret... I agree though. I could never figure out why canceled action queries do the same. I clearly remember running an append query of some 12000 records, oops! no. forgot something. Cancel! fix and run again. Talk about your numbers being out of sequence! Right on! I'll blame Billy too. Gord
ghubbell@total.net

 

[RickSpr]

Ok, here's why autonumbers don't roll back.

First of all, it's not that autonumbers get "triggered" even though none of the records are added. The reality is, when you use a transaction, the records really are added to the table, but the indexes aren't updated to include them, and the records stay locked, until you commit the updates. And of course, as the records are added, the autonumber has to keep getting incremented.

When you do the Rollback, the records are deleted, and the index update info pending in memory is discarded. (In reality, the records aren't deleted one-by-one; instead, Jet just restores control information that it uses to keep track of where records are. It's like writing the records on a piece of paper, and then throwing away the page instead of erasing what you wrote.)

So why isn't the autonumber rolled back, too? Well, there's a good reason. Suppose you and another user were both adding records at the same time. You get numbers 101, 102, 103,...the other user gets 104..., you get 105, then you roll back your updates but the other guy commits his record. If autonumber got set back to 101, soon it would be 104 again--but the other user's 104 record already exists. Autonumbers are supposed to be unique, so this is a bad thing. (Note: Though autonumbers are usually uniquely indexed, they don't have to be, so it'd be possible to actually have two records with the same authonumber value.)

What happens if you use your own code to simulate an autonumber from a Long stored in a table? Well, when you start adding records in a transaction, you'll put a lock on your stored number. If you roll back, it will be safe, because nobody else can update your stored number while your transaction is pending. But the downside is that, while your transaction is pending, nobody else can add any records. That kind of blows the idea of using your own number in a multiuser database, unless you can ensure that your transactions get ended quickly. Rick Sprague

 

[ghubbell]

Thanks Rick! Another excellent explanation! ;-) Gord
ghubbell@total.net

 

[Toga]

Thanks for taking the time to explain some insight on this Rick.... This really helps in trying to understand what's happening behind the scenes and why!
Toga