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

Lambert

I make an append query where I get the max() of the ID field, add 1, alias
it and append that back in to the table as a new record.  This adds a bogus
record to the end of the table which I then delete back out.  The autonumber
is now reset and off I go.  The only issue is if there are other fields that
have constraints then it becomes a bit more difficult discovering and
building such values. 


John W. Colby
www.ColbyConsulting.com 


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Thursday, January 05, 2006 3:18 PM
To: 'Access-D Email (accessd at databaseadvisors.com)'; 'ACCESS-L Email
(ACCESS-L at PEACH.EASE.LSOFT.COM)'
Subject: [AccessD] More Strange Autonumber Behavior - Access 2002 (akaAccess
XP)

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