 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 2 / April 2007Auto Number Problem
I have a table with an autonumber and periodically I migrate lines in that table to another table which saves the autonumber in a number formatted field. I had to reinstate a row with its original autonumber and the table lost count. It started counting from the reinstated number. Has anyone had this problem? For Example. I migrated lines 4000 - 4200 to another table. These lines no longer existed in original table. When I reinstated line 4100, it allowed the 4100 in the autonumber field. That would be fine, but it continued to number the lines from 4100 instead of 22000, which was the last line in the table. It was like the table forgot what the last line really was. Thanks Linda Hi, Linda. > It was like the table forgot what the last line really was. I can think of one scenario where this may happen. If the table was empty and the database compacted, and then a record with AutoNumber 4100 was inserted, then next number would be 4101, not 22000, the last AutoNumber prior to emptying the table and compacting the database. If this is not the case, then it's possible that your workstation (or the computer where this database's tables are stored, if you're linking to the tables in the back end) doesn't have the latest service packs for Jet (and possibly MDAC, too, so you may as well check both). If you have Access 2000, 2002, or 2003 installed on the computer where this table is located, then Jet 4.0 SP-8 and MDAC 2.8 SP-1 are the latest service packs. Please see the following Web page for the correct file versions for Jet 4.0: http://support.microsoft.com/?kbid=829558 Please see the following Web page for links to the downloads and the MDAC Component Checker: http://msdn2.microsoft.com/en-us/data/aa937730.aspx If your workstation (and the computer where the back end resides) already has the latest service packs, then it's possible the database file is corrupt. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. >I have a table with an autonumber and periodically I migrate lines in that >table to another table which saves the autonumber in a number formatted [quoted text clipped - 16 lines] > > Linda The sole purpose of the Autonumber datatype is to generate uniqueness for surrogate primary keys. It is entirely for the use of JET in this case. Human use of the autonumber for anything else amounts to abuse. Humans should never see the content of an autonumber field nor care what it happens to be. The symptoms you describe are normal behavior. If you want to avoid the effects you describe, populate the field without the autonumber field. Let the system do it for you. Note that there is no guarantee of uninterrupted sequence in autonumber. HTH  Signature-Larry- -- > I have a table with an autonumber and periodically I migrate lines in that > table to another table which saves the autonumber in a number formatted [quoted text clipped - 16 lines] > > Linda Hi, Larry. > Humans should never see the content of an autonumber field nor care > what it happens to be. When the AutoNumber eventually climbs to 4201, which already exists, Linda will receive an error message, and won't be able to add new records to the table. She will definitely care about that number, because it's wrong: it won't be unique in that table. More importantly, if there are any foreign tables related to this table using the surrogate key, all of the inserted records from 4101 to 4200 will probably not match up with the existing foreign records that already relate to the records that have been moved to the archive table. That's a huge data integrity issue. > The symptoms you describe are normal behavior. Repeating AutoNumbers is not normal behavior. (Well, they repeat after about 4.3 billion inserts, but it's doubtful she's there yet.) In Linda's example, the next AutoNumber should be 22001. Inserting a record to match a related record should not reseed the AutoNumber column, unless the table was emptied first, the database compacted, and then the record was inserted into the table with a value in the AutoNumber column higher than 1. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. > The sole purpose of the Autonumber datatype is to generate uniqueness > for surrogate primary keys. It is entirely for the use of JET in this [quoted text clipped - 33 lines] >> >> Linda I strongly disagree with your database folklore of course your end users should be able to 'see' the integer keys anything else is abuse On Apr 22, 1:15 am, "Larry Daugherty" <Larry.NoSpam.Daughe...@verizon.net> wrote: > The sole purpose of the Autonumber datatype is to generate uniqueness > for surrogate primary keys. It is entirely for the use of JET in this [quoted text clipped - 37 lines] > > > Linda That's what I ended up doing. When a person reinstates a record I do not include the previous autonumber, but allow the original table to generate another number instead. That seems to be the best work around. I allow the user to see the autonumber because I am using it to identify records in a bank statement file. Bank data that we receive is notoriously un-normalized. Then they use the autonumber when they find the deposit that posted to our General Ledger. It's being used as a crossreference. Linda EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com >That's what I ended up doing. When a person reinstates a record I do not include the previous autonumber, but allow the original table to generate another number instead. > [quoted text clipped - 3 lines] > >Then they use the autonumber when they find the deposit that posted to our General Ledger. It's being used as a crossreference. Just beware: an autonumber makes each record unique, even if it is in fact a duplicate. You'll need some sort of controls to make sure that you don't enter the same deposit or withdrawal twice. Not trivial, of course, since it's quite possible that somebody withdrew $60 from the ATM in the morning, and then $60 more in the afternoon; if you're just recording a date, they'd look identical but would in fact be different transactions! John W. Vinson [MVP] in Access Data Projects you have the ability to control how you're inserted autonumber fields. you can either allow it or disallow it set identity_insert tablename on this type of behaivor isn't possible in MDB-- yet another reason not to use MDB On Apr 26, 1:01 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com> wrote: > >That's what I ended up doing. When a person reinstates a record I do not include the previous autonumber, but allow the original table to generate another number instead. > [quoted text clipped - 12 lines] > > John W. Vinson [MVP] "T o d o s M e n o s [ M S F T ]" <t o d o s _ m e n o s _ m s f t@hotmail.com> wrote: >in Access Data Projects you have the ability to control how you're >inserted autonumber fields. [quoted text clipped - 5 lines] >this type of behaivor isn't possible in MDB-- yet another reason not >to use MDB Note that this person is really A a r o n K e m p f and that he is not an employee of Microsoft. His answer was also totally irrelevant to your question. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm my answer was 100% helpful and 100% what needed to be said you can use SET IDENTITY_INSERT TABLENAME ON and then you can overwrite autonumbers; or you can turn it off and it won't allow it either way-- MDB is for chipmunks > "T o d o s M e n o s [ M S F T ]" <t o d o s _ m e n o s _ m s f t...@hotmail.com> > wrote: [quoted text clipped - 18 lines] > read the entire thread of messages. > Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm This is a bug in the current/last version of Jet 3.6 I don't know about Access 2007/ACE. Whenever you paste a value into the autonumber field, you must always reset the autonumber again. You can do this either by using code to reset the autonumber seed property of the field, or you can do a dummy append/ delete to reset the autonumber to a number greater than the last value used. You can use code to reset the autonumber to anything. This is the 'new' feature that broke autonumber. Although the 'new' feature is now 6 years old and autonumber is still broken, and every fix just changed the bugs. Now that ACE has been released, there is no reason to expect this will ever be fixed in Jet 3.6 (david) > I have a table with an autonumber and periodically I migrate lines in that > table to another table which saves the autonumber in a number formatted [quoted text clipped - 16 lines] > > Linda > Now that ACE > has been released, there is no reason to expect this will ever > be fixed in Jet 3.6 Does it mean that Microsoft releases buggy products and doesn't fix bugs in older products? That is: 1) New release is buggy and thus not useful. Many bugs survive to next release. 2) Old release is buggy and thus not useful. Bugs in older products will never be fixed. Vlado > This is a bug in the current/last version of Jet 3.6 > I don't know about Access 2007/ACE. [quoted text clipped - 40 lines] >> >> Linda Yes, Access 95 is an example. It was not very useful, and eventually they just released Access 98. Access 2000/Access 2002 was a bit different: Access 2000 was a useful product with bugs, eventually they just stopped patching A2000 and released A2002. Access 2007/2003 is different again. Access 2003 is useful, but has bugs. Access 2007 is useful, but there is no indication that at present it has fewer bugs than 2003, just different bugs. (david) > > Now that ACE > > has been released, there is no reason to expect this will ever [quoted text clipped - 54 lines] > >> > >> Linda In other words: the only thing can do is to dream of perfect Access... since it's never gonna be released... Why do we pay for then? Do we pay for dreams? :O Vlado > Yes, Access 95 is an example. It was not very useful, and > eventually they just released Access 98. [quoted text clipped - 73 lines] >> >> >> >> Linda I won't defend their release practice, but I will ask if you have ever bought anything that was "perfect"? I use Access because it gets a job done, like my vehicle. Jeff Boyce Microsoft Office/Access MVP > In other words: the only thing can do is to dream of perfect Access... since > it's never gonna be released... Why do we pay for then? Do we pay for [quoted text clipped - 79 lines] > >> >> > >> >> Linda Perfect: My Honda! My Mazda! My TV set! My DVD set! My VCR! My satellite receiver! . . etc... Not perfect: Anything by Microsoft! :-/ Vlado >I won't defend their release practice, but I will ask if you have ever > bought anything that was "perfect"? [quoted text clipped - 92 lines] >> >> >> >> >> >> Linda Hi, Vlado. > Not perfect: > Anything by Microsoft! :-/ Then you know what the shortcomings are, so you can build the perfect software applications yourself and take all of Microsoft's market share. You'll be rich! Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. > Perfect: > My Honda! [quoted text clipped - 109 lines] >>> >> >> >>> >> >> Linda Hi, Gunny, I don't think I'm gonna beat Microsoft. Microsoft is a big SW produrer. They should do their job perfect, or near perfect, at least!!! As I stated before: we will never have Access bug-free. Why they don't take care of what they produce? They know there are bugs in Access and JET. Why don't they simply fix those bugs? Vlado > Hi, Vlado. > [quoted text clipped - 129 lines] >>>> >> >> >>>> >> >> Linda Hi, Vlado. > They should do their job perfect, or near perfect, at least!!! Unfortunately, the software business doesn't work that way. Look at Windows 95 as a case in point. It's the Windows release that should have been 1.0. It took Microsoft 10 years to get to the point where they could release an operating system with that level of sophistication. If they'd waited to release the final version and skipped the other releases along the way (i.e., 1.0, 2.0, 3.0, 3.1, and 3.11), then they would have gone out of business before releasing the Windows 95 version because they needed continuous revenues to fuel further development of their software. Even though those earlier versions had limitations and bugs, they were the fuel that propelled the software development engine towards bigger and better things. > They know there are bugs in Access and JET. Why don't they simply fix > those bugs? I don't work at Microsoft on those teams, so I can only speculate on what their motivations are, which isn't really fair. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. > Hi, Gunny, > [quoted text clipped - 141 lines] >>>>> >> >> >>>>> >> >> Linda >It took Microsoft 10 years to get to the point where they could release an >operating system with that level of sophistication. And for the hardware to get cheap enough that could run it. We'll ignore the bloating arguments. <smile> Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm One simple question: If your customer finds a bug, or ,as Microsoft tend to say, "issue", in your old application, won't you fix the bug? Will you just let it "fly"? I don't think so. Vlado > Hi, Vlado. > [quoted text clipped - 129 lines] >>>> >> >> >>>> >> >> Linda Hi, Vlado. > One simple question: > If your customer finds a bug, or ,as Microsoft tend to say, "issue", in > your old application, won't you fix the bug? Will you just let it "fly"? I > don't think so. It may be a simple question, but it doesn't have a simple answer. What if fixing a bug for one customer adversely affects another -- or perhaps ten other -- customers? What if fixing a bug for a few customers means the product goes in the opposite direction of where most of the other customers want it to go, and which they don't want to pay for if it does? What if fixing a bug would require an extensive (and expensive) rewrite? What if fixing a bug in the current version would be a waste of time because the product is getting rid of that feature in the next version, or in a few versions down the line? In all of these cases, you'd be making a few happy at the expense of the many, or you'd be spending money unnecessarily, or you'd be spinning your wheels. So, there are legitimate business reasons for not fixing certain bugs in software when one has many customers' needs to satisfy, not just one customer's needs. HTH. Gunny See http://www.QBuilt.com for all your database needs. See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials. Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info. > One simple question: > If your customer finds a bug, or ,as Microsoft tend to say, "issue", in [quoted text clipped - 138 lines] >>>>> >> >> >>>>> >> >> Linda more importantly what happens when the big fat company that is raping customers with buggy software? what happens when the lethargic company gets too slow to fix bugs? what happens when people SUBMIT bugs to Microsoft; and they don't fix them? Why is Microsoft busy with Robotics Studio and Lego Mindostorms-- when they can't release bug free MS Access screw MS from a quality standpoint; from a performance standpoint-- all you guys would be better served by moving to DreamWeaver mySql / php as it is; MDB is not a rational platform for anything On Apr 23, 12:25 am, "'69 Camaro" <ForwardZERO_SPAM.To. 69Cam...@Spameater.orgZERO_SPAM> wrote: > Hi, Vlado. > [quoted text clipped - 171 lines] > > - Show quoted text - it's not a question of 'making a few happy' it's about f.cking making BUG FREE SOFTWARE The only thing that can save Microsoft-- at this point-- is for Ralph Nader to replace Ballmer On Apr 23, 12:25 am, "'69 Camaro" <ForwardZERO_SPAM.To. 69Cam...@Spameater.orgZERO_SPAM> wrote: > Hi, Vlado. > [quoted text clipped - 171 lines] > > - Show quoted text - >Why do we pay for then? Do we pay for dreams? :O I really like that phrase and thought. (david) > In other words: the only thing can do is to dream of perfect Access... since > it's never gonna be released... Why do we pay for then? Do we pay for [quoted text clipped - 79 lines] > >> >> > >> >> Linda Access Data Projects never had this problem we have seed and increment and identity insert statements seriously you guys are f.cking idiots for still using MDB for anything > This is a bug in the current/last version of Jet 3.6 > I don't know about Access 2007/ACE. [quoted text clipped - 37 lines] > > > Linda "T o d o s Me n o s [ M S F T ]" <t o d o s _ m e n o s _ m s f t @hotmail.com> wrote: >Access Data Projects never had this problem > [quoted text clipped - 3 lines] > >seriously you guys are f***ing idiots for still using MDB for anything Note that this person is really A a r o n K e m p f and that he is not an employee of Microsoft. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Note that Tony is a MDB _WUSS_ and doesn't deserve to be working with databases anyone, anywhere-- using MDB for anything-- should be FIRED and then SPIT UPON MDB isn't reliable enough for real world usage, let alone newbies if you're new to Access and you want a BETTER PRODUCT then grow into Access Data Projects On Apr 23, 12:36 pm, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote: > "T o d o s Me n o s [ M S F T ]" <t o d o s _ m e n o s _ m s f t @hotmail.com> > wrote: [quoted text clipped - 16 lines] > read the entire thread of messages. > Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm This is an additional comment. I created a table with an auto number. I appended several of the record to another table. Then I appended those records back to the original table and Access allowed duplicate autonumbers. What's up with that? It did not prevent the append. I did not create a primary key on the field. That would have prevented it from being appended, but I thought it would create another number. EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com >This is an additional comment. I created a table with an auto number. > [quoted text clipped - 5 lines] > >It did not prevent the append. I did not create a primary key on the field. That would have prevented it from being appended, but I thought it would create another number. An Append query is the *only* way to put data into an Autonumber field (and it's often useful, say for merging tables). "This behavior is by design" is what the Microsofties would say. If you want to have appended records assigned new autonumbers, just don't include the autonumber field in the Append query; all the fields you do include will get appended, but the autonumber (not mentioned in the query at all) will increment as normal. John W. Vinson [MVP] The autonumber property, by itself, is just a way of automatically generating a default value for a field. (david) > This is an additional comment. I created a table with an auto number. > [quoted text clipped - 5 lines] > > It did not prevent the append. I did not create a primary key on the field. That would have prevented it from being appended, but I thought it would create another number. > EggHeadCafe.com - .NET Developer Portal of Choice > http://www.eggheadcafe.com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.