[AccessD] More Strange Autonumber Behavior - Access 2002 (aka Access 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