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