7

I have a VB6/Access application that occasionally encounters a problem with wrong autonumber field seed.

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.

I found a KB article that describes my symptoms: http://support.microsoft.com/kb/884185 . In short, they suggest to run a query:

ALTER TABLE MYTABLE ALTER COLUMN ID COUNTER(1001,1)

When I try to do this, it fails with "Invalid field data type"

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.

I use DAO DBEngine.CompactDatabase to perform compact/repair inside the application, but it doesn't fix the seed problem, and some additional tricks are needed.

Any ideas for a solution?

TAbdiukov
  • 1,185
  • 3
  • 12
  • 25
Incidently
  • 4,249
  • 3
  • 23
  • 30
  • out of curosity, are you closing the table **before** running `ALTER TABLE MYTABLE ALTER COLUMN ID COUNTER(1001,1)`? – Todd Main Feb 18 '10 at 21:54
  • If the code is inside an Access MDC/ACCDB, the language is VBA, not VB6. Please clarify. – Fionnuala Feb 19 '10 at 10:09
  • @Otaku: Yes, sure This query fails both when executed inside Access and when executed through DAO. The weird thing is this: if in Access I delete the autonumber field, and then create exactly the same field, the query works. It looks like there are different flavors of autonumber type, but I can't get what this difference is – Incidently Feb 19 '10 at 12:59
  • @Remou: VB6/Access application = application written in VB6 using Access database as its backend – Incidently Feb 19 '10 at 13:01
  • You shouldn't be encountering occasional problems with the seed. Are the systems on which your app running fully patched Windows 2000 and newer? What is the version of msjet40.dll running on the systems? I'd suggest logging the version and see if there are any problems there. What I've done is use the various API calls available and am checking the version number and date/time of a crucial dll, msjetxx.dll, to ensure it matches what I have on my system. See the Verify Appropriate Jet Service Pack is installed page at my website www.granite.ab.ca\access\verifyjetsp.htm – Tony Toews Feb 19 '10 at 21:36
  • FWIW, I deployed seed resetting code in one of my apps a few weeks ago, because I'm frequently importing old data as I keep finding it lying around in archived files. It needs to be reloaded back into the database (somebody failed to enforce RI, so that there's a lot of nonsensical data in there, with foreign keys pointing to records that don't exist, and some of them pointing to the wrong records, because old data used the same numeric range -- yes, a mess!), and each time I do that, the Autonumber counter gets reset to the highest value that was inserted from the archived data. – David-W-Fenton Feb 21 '10 at 05:59
  • David, ok, but that makes sense. You are importing the autonumber field so Jet will reset the seed to the highest number imported if that's higher than the current value. But it shouldn't be reset to a lower number. – Tony Toews Feb 22 '10 at 02:35
  • It *is* set to a lower number, that being the highest number in the insert. I didn't expect it myself, but since it's happened twice, I implemented the seed reset code so I don't let the error be seen by the end users. I know this is not the way it's supposed to work from the way I understand it, but I think anytime you insert a value less than the existing seed, it does happen. And in this case, these archived records are all lower values than the existing Autonumber, and the result is unique index collisions. – David-W-Fenton Feb 23 '10 at 20:30

3 Answers3

4

Please reference the following article, it contains a method you may add to your access project to execute to reset seeding. It has been a saver for me several occasions in the past:

http://allenbrowne.com/ser-40.html

In addition to this it gives explanation and insight into causes and potential resolution for such problems.

Jakkwylde
  • 1,304
  • 9
  • 16
  • I would never add a reference just to support one function -- late binding would be a much better way to do it. But I'm not sure you get anything more than what you'd get with a DAO compact and the ALTER TABLE seed reset. I can't imagine running that code and not compacting afterwards, anyway. – David-W-Fenton Feb 18 '10 at 21:38
0

You may be able to solve the problem with a compact/repair:

In Access 2010:  Compact and Repair Database on the Database Tools ribbon.
In Access 2007:  Office Button | Manage.
In earlier versions:  Tools | Database Utilities.
Pritesh
  • 3,208
  • 9
  • 51
  • 70
0

You may also need to make sure that your database is set up to use ANSI 92 so that COUNTER is recognized as a legitimate data type.

In Access 2007 go to Access Options, Object Designers, SQL Server compatability syntax (ANSI 92) to set this.

AMW
  • 86
  • 1