Paul Hartland
paul.hartland at googlemail.com
Fri Mar 7 07:26:26 CST 2014
Ah that's sort of good to know but was a right pain, I do miss a bit of access development, might bite the bullet and buy MS Office or something soon, just to keep my hand in. Paul On 7 March 2014 12:48, John W Colby <jwcolby at gmail.com> wrote: > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Paul Hartland paul.hartland at googlemail.com