John W Colby
jwcolby at gmail.com
Fri Mar 7 06:48:35 CST 2014
IIRC that was a bug that was eventually fixed? John W. Colby Reality is what refuses to go away when you do not believe in it On 3/7/2014 7:29 AM, Paul Hartland wrote: > Also I have had a situation happen to me twice with an MS Access database > we had a table which used an autonumber field as the PK, in this table > deletions were allowed, so we could have a case where you could get 1, 2, > 3, 4, 5, 7, 8, 9, 11 as PK's but this was ok, the problems came when we > started having regular crashes before we moved to SQL with VB6 front-end, I > would compact and repair the Access database and let everyone go back in > and a couple of times I got a call saying there had been an error, and it > would involve this table with the autonumber as a PK, it seems that Access > would get confused and if you use the sequence of numbers earlier as an > example it would try starting at 6 this would be ok, then for the next > record it would try using 7 but seven exists so errors would occur, this > happened a couple of times where the autonumber would try restarting from > an earlier number that was missing. > > Paul > > > On 7 March 2014 11:41, Jim Dettman <jimdettman at verizon.net> wrote: > >> <<How else could I sit on record with RecordID = ######## and find the very >>> next previous record?>> >> Well one problem with that is that you really can't. AN's can roll over >> to negative numbers, So your check may not be valid at some point. For >> all practical purposes it's true (the rollover point is quite large), but >> I have seen a Jet db corrupt and as a result, the AN seed jump to an >> extremely high number. >> >> Second problem is that the key is no longer meaningless. You've encoded >> the "entry order" in the value of the key. >> >> You can't change the key without destroying the information it represents. >> >> GUID creation does involve the time as part of the calc, but that doesn't >> allow you to treat them as sequenced numbers. >> >> Jim >> >> Sent from my iPhone >> >> On Mar 6, 2014, at 11:54 PM, "Bill Benson" <bensonforums at gmail.com> wrote: >> >>> I would also add that the autonumber PK is very good for searching for >> all >>> records created before and/or after another record, without having to >>> compare pinpoint precision date fields, which even then may not have >>> *enough* precision when things occur in a batch mode. >>> >>> >>> >>> All I know to do is look for the MAX(RecordID) WHERE RecordID < ########. >>> >>> If someone knows a way to do that with GUID, then I will stand down. >>> >>> But being an ignoramus and knowing nothing about how GUIDs are >> constructed, >>> only that they are essentially "random"...?? >>> >>> Otherwise, well bite off my fingers. >>> >>> -----Original Message----- >>> From: accessd-bounces at databaseadvisors.com >>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W Colby >>> Sent: Thursday, March 06, 2014 11:43 PM >>> To: Access Developers discussion and problem solving >>> Subject: Re: [AccessD] Membership File Changes >>> >>> Explain to the whipper snapper that two integers can be compared in a >> single >>> instruction. Two GUIDs >>> will take an entire string compare function and thousands of cycles to >>> perform the same compare. >>> Then whip up a demo to show him. GUIDs are dog slow compared to >> integers. >>> John W. Colby >>> >>> Reality is what refuses to go away >>> when you do not believe in it >>> >>> On 3/6/2014 8:02 PM, David McAfee wrote: >>>> <snip> >>>> This strategy is not without detractors however, mostly "old timers" >> from >>>> databases where multi-field PKs were ALWAYS used. Mostly they try to >> argue >>>> that it is possible to just read the PK down in the child / grandchild / >>>> great grandchild etc. simply by looking at the data in the multi-field >> PK, >>>> which "saves your bacon" when orphans creep in and so forth. >> Additionally >>>> the "values" can often be pulled for reporting and the like, without >>>> requiring joins back up the chain. Both are true, but not (IMO) make up >>>> for the overhead and headaches that multi-field PKs bring with them. >>>> </snip> >>>> >>>> John, I was recently called an old timer because I insisted on using >>>> autonumber integer PK for some of our new designs. >>>> The young whipper snapper said that's the "old way" of doing things, and >>> is >>>> very anal about using GUIDS and multi-field PKs. >>>> >>>> I know GUIDs have their place, but he just doesn't get it. I love how my >>>> stuff runs so much faster, even with joins. :) >>>> >>>> D >>> >>> --- >>> This email is free from viruses and malware because avast! Antivirus >>> protection is active. >>> http://www.avast.com >>> >>> -- >>> AccessD mailing list >>> AccessD at databaseadvisors.com >>> http://databaseadvisors.com/mailman/listinfo/accessd >>> Website: http://www.databaseadvisors.com >>> >>> -- >>> AccessD mailing list >>> AccessD at databaseadvisors.com >>> http://databaseadvisors.com/mailman/listinfo/accessd >>> Website: http://www.databaseadvisors.com >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> > > --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com