title: Reset autonumber seed
body: <p>I have a VB6/Access application that occasionally encounters a problem with wrong autonumber field seed. </p>
  
  <p>Lets say there is a table MYTABLE with an autonumber field ID (that is also the primary key). Lets say at the moment the maximum value of ID is 1000. When the application inserts a new record (ID value is not provided explicitly), for some reason it decides that the next autonumber field value is 950 (and not 1001 as it should be) - so a primary key violation error occurs.</p>
  
  <p>I found a KB article that describes my symptoms: <a href="http://support.microsoft.com/kb/884185" rel="nofollow noreferrer">http://support.microsoft.com/kb/884185</a> . In short, they suggest to run a query:</p>
  
  <pre><code>ALTER TABLE MYTABLE ALTER COLUMN ID COUNTER(1001,1)
  </code></pre>
  
  <p>When I try to do this, it fails with "Invalid field data type"</p>
  
  <p>The problem gets fixed if I open the database in Access and do compact/repair, but I need to be able to fix such problems inside the application: it is installed on a couple of thousands of PCs around the world, and asking people to compact/repair with Access is not an option.</p>
  
  <p>I use DAO <code>DBEngine.CompactDatabase</code> to perform compact/repair inside the application, but it doesn't fix the seed problem, and some additional tricks are needed.</p>
  
  <p>Any ideas for a solution?</p>