A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

AutoNumber Reset



 
 
Thread Tools Display Modes
  #1  
Old January 26th, 2005, 05:47 PM
Reggie Laffond
external usenet poster
 
Posts: n/a
Default AutoNumber Reset

Background:
I have a database I am rewriting in Access 2000 (split DB) that was
originally written in VB 3.0 with Access 2.0. The database has about 70
tables and was actually designed very well, completely normalized, very
efficient and had autonumber fields for all table primary keys. So I simply
imported the tables into Access 2000 for the back end and I am replicating
the forms and behavior in an Access 2000 front end.

Problem:
After working on this for 6 months including having the users do some
preliminary testing I have just discovered that one of my tables had reset
the next autonumber value to 1. Obviously I couldn't add records to the
table because key value 1 already exists. To recover from this I copied the
table (structure only) and ran an append query to create all of the records
with proper key values. Problem solved!!!

Questions:
How could this have happened? If this happened to one table can I prevent it
from happening to another in the future? I had repaired and compacted the
front and back ends a day or so before this problem surfaced. I have never
seen a repair and compact cause this problem.

Thanks in advance!



  #2  
Old January 26th, 2005, 06:39 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"Reggie Laffond" wrote in
:


So I simply imported the tables into Access 2000 for the back end and



I have just discovered that one of my tables had
reset the next autonumber value to 1.


Questions:
How could this have happened?


If you import a table _structure_ then the seed will start from scratch.
If you imported the table with its data then the seed will fall back to
(highest existing autonumber + 1). In effect you are just creating a new
table.

I had repaired
and compacted the front and back ends


Compacting a db with one or more empty tables will reset the autonumber
in that table or those tables.

I don't understand how it matters, though. If you want to start with an
empty database, then it's appropriate to let all counters restart. If you
want to start with a seeded database, then you will need some method of
initialising it, which in turn means that you think very carefully about
what data you need to import, in what order, and how. This is a technical
procedure called Data Migration.

All the best


Tim F

  #3  
Old January 26th, 2005, 10:10 PM
Reggie Laffond
external usenet poster
 
Posts: n/a
Default

I might have given too much information and made my question unclear.

Basically I had a table with 939 records in it with the primary key (auto
number field) from 0 to 942 (with three missing numbers caused by deleted
records). I compacted and repaired the database.. The next day when I tried
to add a new record to the table the auto number kept coming up 1 which
already was used in the first record in the table.

I just want to know if "repair and compact" could reset the auto number seed
even though there were 939 record in the table. Or is there some other known
bug that could cause this to happen. I just want to prevent it in the
future.

Thanks!



"Tim Ferguson" wrote in message
...
"Reggie Laffond" wrote in
:


So I simply imported the tables into Access 2000 for the back end and



I have just discovered that one of my tables had
reset the next autonumber value to 1.


Questions:
How could this have happened?


If you import a table _structure_ then the seed will start from scratch.
If you imported the table with its data then the seed will fall back to
(highest existing autonumber + 1). In effect you are just creating a new
table.

I had repaired
and compacted the front and back ends


Compacting a db with one or more empty tables will reset the autonumber
in that table or those tables.

I don't understand how it matters, though. If you want to start with an
empty database, then it's appropriate to let all counters restart. If you
want to start with a seeded database, then you will need some method of
initialising it, which in turn means that you think very carefully about
what data you need to import, in what order, and how. This is a technical
procedure called Data Migration.

All the best


Tim F



  #4  
Old January 26th, 2005, 11:41 PM
Jeff Conrad
external usenet poster
 
Posts: n/a
Default

Updating your Jet file should solve this issue I believe. See:

ACC2000: AutoNumber Field Duplicates Previous Values
After You Compact and Repair a Database
http://support.microsoft.com/default...b;en-us;257408

--
Jeff Conrad
Access Junkie
Bend, Oregon

"Reggie Laffond" wrote in message
...
I might have given too much information and made my question unclear.

Basically I had a table with 939 records in it with the primary key (auto
number field) from 0 to 942 (with three missing numbers caused by deleted
records). I compacted and repaired the database.. The next day when I tried
to add a new record to the table the auto number kept coming up 1 which
already was used in the first record in the table.

I just want to know if "repair and compact" could reset the auto number seed
even though there were 939 record in the table. Or is there some other known
bug that could cause this to happen. I just want to prevent it in the
future.

Thanks!



"Tim Ferguson" wrote in message
...
"Reggie Laffond" wrote in
:


So I simply imported the tables into Access 2000 for the back end and



I have just discovered that one of my tables had
reset the next autonumber value to 1.


Questions:
How could this have happened?


If you import a table _structure_ then the seed will start from scratch.
If you imported the table with its data then the seed will fall back to
(highest existing autonumber + 1). In effect you are just creating a new
table.

I had repaired
and compacted the front and back ends


Compacting a db with one or more empty tables will reset the autonumber
in that table or those tables.

I don't understand how it matters, though. If you want to start with an
empty database, then it's appropriate to let all counters restart. If you
want to start with a seeded database, then you will need some method of
initialising it, which in turn means that you think very carefully about
what data you need to import, in what order, and how. This is a technical
procedure called Data Migration.

All the best


Tim F



  #5  
Old January 27th, 2005, 08:25 PM
Tim Ferguson
external usenet poster
 
Posts: n/a
Default

"Reggie Laffond" wrote in news:e$nuFu#AFHA.1296
@TK2MSFTNGP10.phx.gbl:

. I compacted and repaired the database.. The next day when I tried
to add a new record to the table the auto number kept coming up 1 which
already was used in the first record in the table.


As Jeff says, this is a bug. Update Access with latest service packs.

All the best


Tim F

  #6  
Old January 27th, 2005, 10:07 PM
Reggie Laffond
external usenet poster
 
Posts: n/a
Default

Will do. Thanks for the quick reply! Great newsgroup!

"Jeff Conrad" wrote in message
...
Updating your Jet file should solve this issue I believe. See:

ACC2000: AutoNumber Field Duplicates Previous Values
After You Compact and Repair a Database
http://support.microsoft.com/default...b;en-us;257408

--
Jeff Conrad
Access Junkie
Bend, Oregon

"Reggie Laffond" wrote in message
...
I might have given too much information and made my question unclear.

Basically I had a table with 939 records in it with the primary key

(auto
number field) from 0 to 942 (with three missing numbers caused by

deleted
records). I compacted and repaired the database.. The next day when I

tried
to add a new record to the table the auto number kept coming up 1 which
already was used in the first record in the table.

I just want to know if "repair and compact" could reset the auto number

seed
even though there were 939 record in the table. Or is there some other

known
bug that could cause this to happen. I just want to prevent it in the
future.

Thanks!



"Tim Ferguson" wrote in message
...
"Reggie Laffond" wrote in
:


So I simply imported the tables into Access 2000 for the back end

and


I have just discovered that one of my tables had
reset the next autonumber value to 1.

Questions:
How could this have happened?

If you import a table _structure_ then the seed will start from

scratch.
If you imported the table with its data then the seed will fall back

to
(highest existing autonumber + 1). In effect you are just creating a

new
table.

I had repaired
and compacted the front and back ends

Compacting a db with one or more empty tables will reset the

autonumber
in that table or those tables.

I don't understand how it matters, though. If you want to start with

an
empty database, then it's appropriate to let all counters restart. If

you
want to start with a seeded database, then you will need some method

of
initialising it, which in turn means that you think very carefully

about
what data you need to import, in what order, and how. This is a

technical
procedure called Data Migration.

All the best


Tim F





  #7  
Old January 27th, 2005, 10:07 PM
Reggie Laffond
external usenet poster
 
Posts: n/a
Default

Will do. Thanks for the quick reply! Great newsgroup!


"Tim Ferguson" wrote in message
...
"Reggie Laffond" wrote in news:e$nuFu#AFHA.1296
@TK2MSFTNGP10.phx.gbl:

. I compacted and repaired the database.. The next day when I tried
to add a new record to the table the auto number kept coming up 1 which
already was used in the first record in the table.


As Jeff says, this is a bug. Update Access with latest service packs.

All the best


Tim F



  #8  
Old January 27th, 2005, 11:35 PM
Jeff Conrad
external usenet poster
 
Posts: n/a
Default

You're welcome, glad to help.
Come back any time.

--
Jeff Conrad
Access Junkie
Bend, Oregon

"Reggie Laffond" wrote in message ...
Will do. Thanks for the quick reply! Great newsgroup!

"Jeff Conrad" wrote in message
...
Updating your Jet file should solve this issue I believe. See:

ACC2000: AutoNumber Field Duplicates Previous Values
After You Compact and Repair a Database
http://support.microsoft.com/default...b;en-us;257408

--
Jeff Conrad
Access Junkie
Bend, Oregon

"Reggie Laffond" wrote in message
...
I might have given too much information and made my question unclear.

Basically I had a table with 939 records in it with the primary key

(auto
number field) from 0 to 942 (with three missing numbers caused by

deleted
records). I compacted and repaired the database.. The next day when I

tried
to add a new record to the table the auto number kept coming up 1 which
already was used in the first record in the table.

I just want to know if "repair and compact" could reset the auto number

seed
even though there were 939 record in the table. Or is there some other

known
bug that could cause this to happen. I just want to prevent it in the
future.

Thanks!



"Tim Ferguson" wrote in message
...
"Reggie Laffond" wrote in
:


So I simply imported the tables into Access 2000 for the back end

and


I have just discovered that one of my tables had
reset the next autonumber value to 1.

Questions:
How could this have happened?

If you import a table _structure_ then the seed will start from

scratch.
If you imported the table with its data then the seed will fall back

to
(highest existing autonumber + 1). In effect you are just creating a

new
table.

I had repaired
and compacted the front and back ends

Compacting a db with one or more empty tables will reset the

autonumber
in that table or those tables.

I don't understand how it matters, though. If you want to start with

an
empty database, then it's appropriate to let all counters restart. If

you
want to start with a seeded database, then you will need some method

of
initialising it, which in turn means that you think very carefully

about
what data you need to import, in what order, and how. This is a

technical
procedure called Data Migration.

All the best


Tim F



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
compacting database doesn't reset autonumber (ver 2000) JulieD New Users 3 January 24th, 2005 05:25 PM
Autonumber Ally H. General Discussion 7 August 27th, 2004 04:51 PM
Autonumber -Reset to a lower number Mike General Discussion 2 June 5th, 2004 01:54 AM
Reset autonumber to zero joseph Database Design 1 May 31st, 2004 03:51 AM
reset autonumber value in table Database Design 0 April 29th, 2004 02:11 PM


All times are GMT +1. The time now is 10:14 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.