[AccessD] More Strange Autonumber Behavior - Access 2002 (akaAccess XP)

John Colby jwcolby at ColbyConsulting.com
Thu Jan 5 14:44:02 CST 2006


ROTFL.  Yea, that will cause an issue.  I just use autonumber which is
always a long. 


John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Randall R Anthony
Sent: Thursday, January 05, 2006 3:38 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] More Strange Autonumber Behavior - Access 2002
(akaAccess XP)

I had a similar problem, however it was dumb developer related.  I had the
autonumber set to integer, which ran out at 32 thousand whatever. 
Changing the field size to Long Integer fixed it.

>>> Lambert.Heenan at aig.com 01/05/06 3:18 PM >>>
I started to get error messages from one application telling me ...

"Error 3022 : The changes you requested to the table were not successful
because they would create duplicate values in the index, primary key, or
relationship.  Change the data in the field or fields that contain duplicate
data, remove the index, or redefine the index to permit duplicate entries
and try again."

This was in a block of code that was simply appending new records to a table
that has a standard,  AutoNumber field called "ID". (This code has been in
use for the past three years) No other field in the table has a unique
index. The code was like this...

     With RS_Premium_Stream
        For n = LBound(mod_tPremiumArray) To UBound(mod_tPremiumArray)
            tPremiumInfo = mod_tPremiumArray(n)
            .AddNew
            !Policy_ID = tPremiumInfo.nPolicy_ID
            !nPolicyYear = tPremiumInfo.nPolicyYear
            !cPremiumProjection = tPremiumInfo.cPremiumProjection
            !cAnnualLoanInterest = tPremiumInfo.cAnnualLoanInterest
             !dDueDate = tPremiumInfo.dDueDate
            !bPaid = tPremiumInfo.bPaidStatus
            !nAge = tPremiumInfo.nAge
            !dDateCreated = dDataFileLoadDate
            .Update: SysCmd acSysCmdUpdateMeter, n
        Next n

... and the error happened when the .Update line executed. Stepping though
the code I discovered that just before the first execution of the .AddNew
line the value of RS_Premium_Stream!ID was the ID value of the "first
record" in the table, as you might expect, but after executing .AddNew the
value of ID was now that of an *existing* record a few hundred rows into the
table!!! This of course cause the duplicate error when the Update method was
called.

I tried Compact (from the command line), Compact and Repair from the Access
menus, but those did not fix the trouble. Then I tried running a Make Table
query to create a copy of the data (retaining the values of the ID
field) in
a new table, but the new table exhibited the same error. JetComp didn't make
any difference either!!!!

The cure? Make a new empty table with the same structure. Then run an append
query to append all existing records to the new table - this preserves the
values of the autonumber field in the existing table - and then delete the
old table and rename the new one. You'd think that simply running a Make
Table query would have the same effect, but no, that just gives you a copy
of the table that exhibits the same problem.

What a pain!

Lambert
--
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




More information about the AccessD mailing list