Bill Benson
vbacreations at gmail.com
Fri Mar 7 09:23:29 CST 2014
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