Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old July 5th, 2004, 09:30 AM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default AutoNumber Reusing numbers

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
  #2 (permalink)  
Old July 5th, 2004, 10:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

  #3 (permalink)  
Old July 5th, 2004, 12:19 PM
Registered User
 
Join Date: Jul 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
  #4 (permalink)  
Old July 5th, 2004, 03:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Question on reusing code billb VBScript 0 August 18th, 2006 10:39 AM
reusing querys jc188 Beginning PHP 3 April 1st, 2006 02:28 AM
Reusing Images hugh@kmcnetwork.com Classic ASP Basics 0 July 19th, 2004 02:58 PM
Reusing String Arrays cebast Servlets 1 May 28th, 2004 04:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.