Stuart McLachlan
stuart at lexacorp.com.pg
Fri Mar 7 15:12:04 CST 2014
Of course, using the sole available AN in a table to determine order of entry makes it meaningful data in its own right and no longer useful as a surrogate PK. So we now need both an non-primary AN key and a GUID PK. :) -- Stuart On 7 Mar 2014 at 10:23, Bill Benson wrote: > I can tell you are not sure. > > I am making the point that to my knowledge, it is not possible to > compare one GUID and determine that it was created either before, or > after a GUID. So I don't know why you reply to that "... and someone's > already pointed out where you'd want to use a GUID rather then an AN." > -- as this is exactly why I WOULD NOT want to use a GUID as opposed to > an AN. > > > >> . If you reset the seed value of an AN to the max value of a long, > >> the > next number > assigned will be negative, thus invalidating your check>> > > ... and who in their right mind would do this? As you wrote, ". I've > never seen anyone do it, yet"" > > > >> I certainly would not be relying on an AN field for that. You > >> should have > an entry date/time and a transaction date/time. > Well that's your prerogative, sir! I take a different view entirely, > and it is because adding records in a batch operation, I will often > stamp them all with the same date/time. I am pretty sure the system > date is not precise enough to measure a distinction between records > added "simultaneously", so using your technique, you would not, in my > estimation of database precision, be able to sort out which record > were added first in an operation. With AN, you don't have that > problem. I just sequence records by my LastModifiedID and the AN field > itself, then it doesn't really matter even if someone else inserts > rows in a record-locking (rather than page-locking) operation. > > Different strokes for different folks, I have seen no reason to give > up my reliance on Autonumber and I am planning on continuing to make > them PKs and have other tables fill up their FK fields with pointers > to those, and probably would never use GUID unless I am on a project > much larger than myself, for performance reasons and for the reason I > just mentioned, that I have no rational way to compare records for > sequence (and datetime is useless, despite the protest I hear you > about to make :0 ) > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman > Sent: Friday, March 07, 2014 9:37 AM To: 'Access Developers discussion > and problem solving' Subject: Re: [AccessD] Membership File Changes > > > Not sure where were going with this, but a couple of points: > > <<Jim, so you have agreed that GUID1 cannot be compared to GUID2 to > tease our a record's ordinal position>> > > Yes and someone's already pointed out where you'd want to use a GUID > rather then an AN. > > <<and you have said that auto number can get > confused and turn negative. >> > > No. You can as a matter of normal operation, roll over to a > negative > value on an AN. I've never seen anyone do it, yet. The max value of > a long is quite high and I doubt an app would be able to create that > many records in it's lifetime. But it is part of the > definition/logic of an AN. If you reset the seed value of an AN to > the max value of a long, the next number assigned will be negative, > thus invalidating your check. > > << No long as the database is not replicated or being > maintained client-side, the connection can be dropped and the record > used until update is required or the next New Record.>> > > While that's true, but the point you miss is that with GUID's, a > connection does not need to be made at all for data entry, replicated > or not. So in the case were I have records created at various > locations, then merged later, GUID's are a perfect choice. > > The only other option is to use AN's, then when merging, prefix the > key in > some way to indicate the source. That works pretty well actually and > you get better performance then a GUID, but then your stuck managing > the process and open the door to possibly making a mistake. > > <<I cannot think of a database project yet where I was not > concerned with sorting records in a recordset that included all > records posted on or before a certain record, or on or after a certain > record, and datetime is not a good predictor. I have seen that field > hand-entered/overridden after-the-fact. >> > > I certainly would not be relying on an AN field for that. You should > have > an entry date/time and a transaction date/time. The first never being > allowed to change. > > Jim. > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson > Sent: Friday, March 07, 2014 09:00 AM To: Access Developers discussion > and problem solving Subject: Re: [AccessD] Membership File Changes > > Jim, so you have agreed that GUID1 cannot be compared to GUID2 to > tease our a record's ordinal position and you have said that auto > number can get <<snip>> > > -- > 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 >