[AccessD] Membership File Changes

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


More information about the AccessD mailing list