Paul Hartland
paul.hartland at googlemail.com
Fri Mar 7 06:29:03 CST 2014
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 > -- Paul Hartland paul.hartland at googlemail.com