MS Access AutoNumber Corruption

Problem

  • Corruption in the Stone Edge tables. AutoNumber field values are reset to a lower value than the most recent value in the AutoNumber field, causing duplicate AutoNumber values within the same table, resulting in the incorrect relationship of records across various tables.

Symptoms

The most common symptoms of this problem are:

  • missing tracking numbers
  • incorrect products listed on, or disappearing from, Purchase Orders
  • the inability to receive inventory

Cause

  • A bug in MS Access 2010 causes the program to reset an AutoNumber field's seed value during the compaction and repair of a database, if it is not the only field in a table's PrimaryKey index, there are more than 1000 records in the table and the Primary Key in the table is sorted in descending order.

Resolution

  • Stone Edge was changed to use the AutoNumber field as the primary key in all tables, beginning in Versions 5.933 and 7.000.3. A new key (SeekIndex) was added in cases where the AutoNumber was part of a multi-field Primary key, and the key was used in recordset Seek commands.

Required User Actions

  • If you have used MS Access 2010 with any of the affected versions of Stone Edge listed in this topic, you should contact the Technical Support department for assistance in repairing the AutoNumber data corruption in your system.
  • If you see this warning message, contact Technical Support for assistance. Do not click through the message and continue to process orders.

  • If you click through this message, you will experience problems exporting data to QuickBooks, as well as other issues caused by not rebuilding the indexes properly.

Component and Versions Affected

  • All MS Access 2010 users

  • Stone Edge Versions 5.932 and lower, V7.000.2 and lower

  • Affected Stone Edge tables:

FIFO POPayments RoadTripTransfers
Goals POReceiving ShipperCode
History Returns ShoppingCartGlobalIDs
ImportTemplateFields ReviewReasons TempDropShipTracking
InventoryAdjustments RMAs TempInvoiceDetails
Notes SalesTaxErrorLog TempQBSort2
Packing Tracking TempShippingMethods
POHistory Transactions TempTransactions
POInvoiceDetails usysPostStack usysTempPostStack

 

 

Created: 2/12/13