Hello,
  I have a table with approx. 101,000 records setup with an autonumber key. For the first time ever the table is reusing the autonumbers that it already created. my max # is: 195728. When I create a new record it uses an autonumber (incrimented eachtime I add) of:191364.
  Why? and how do I fix this?
  Background info: I have access97 and another computer has access2000. our tables are located in a seperate database then our forms/queries/reports. We did not change anything in the table database. Its only one table that is doing this.
 Thanx,
Dorota

Hi Dorota,

Index corruption is the first thing that comes to mind. You could try dropping any indexes you currently have on your autonumber field, then re-creating them. If that doesn't get it, you probably need to import all your db objects into a new .mdb.

Also, make sure you have the latest service pack (SP 8) for the Jet engine installed (SP 8). You can get it at http://support.microsoft.com/default.aspx?kbid=239114. There was a MS bug report a while back about autonumbers duplicating old values in some version of Access. Don't know the KB number off hand, but updating Jet was the fix.

HTH,

Bob

I tried your second suggestion of installing the new jet4.0 SP8. But, win98 machine worked and updated the file but our winXP computers do not update the jet file to the new SP8. Any idea why?

also, when I ran the db on the win98 machine and did the 'compact and repair' the problem still exists.

I will try your first recommendation. But would like to know how to get the jet4.0 file updated.

Not sure about the upgrade issue. I'm running XP Pro (SP1) and didn't run into any problems installing Jet SP8. The current version of the Jet .dll (Msjet40.dll) on my system is 4.0.8015.0, which is Jet SP8.

The Jet upgrade was the long shot anyway. I suspect the import into a new .mdb may be your best bet.

Bob