Repeated Autonumber Gremlins

[isladogs]

Here's a little puzzle for anyone interested

Some time ago I read an article by Allen Browne about autonumbers repeating:
Fixing AutoNumbers when Access assigns negatives or duplicates
However I'd never seen any of the behaviour he described until now

Attached is a single table database with an autonumber field. It has 6 records.
If you try and add a record it will restart the autonumber seed at 1

Of course it won't allow you to save the record as its a duplicate
Ditto for the next 5 records you try & append until you get to ID=7

I've attached the database for anyone interested to try for themselves.
I know exactly how I did it unintentionally & can replicate it easily
None of the explanations on Allen's site match what I did.

Lets see how long it takes anyone to work out what I did to cause this!

EDIT: 12/01/2019
Oops - I uploaded the wrong file yesterday - that one worked properly!
New version uploaded which does have the autonumber gremlins

 

[Gasman]

Did you reset the table to start at 1. I've done that after testing and before going live, but only after deleting all records.?

 

[theDBguy]

Hi Colin,


I gave it a try and here's what I did to duplicate your scenario.


1. I created a table with two fields, one as Autonumber and Primary Key
2. I entered a few records to make sure the PK is automatically assigned a sequential number
3. Then,

Spoiler

I deleted the first record

4. ... I then re-inserted the first record back in using an APPEND query.

 

[theDBguy]

Did you reset the table to start at 1. I've done that after testing and before going live, but only after deleting all records.?

Hi. Since Colin posted the link to Allen Browne's article and it's already mentioned in there how to reseed the Autonumber field, plus he (Colin) said he did this "accidentally," I figure he probably didn't reseed the table.

 

[theDBguy]

Okay, after re-reading the original scenario, I am revising my steps:


1. Create the table with the Autonumber PK field
2. Enter some data
3. Then, "accidentally" ....use an APPEND query to insert a row with a value of 0 for the Autonumber field.

 

[isladogs]

Hi

I didn't do any of the following:
a) intend to create a table like this - that was done unintentionally
b) do anything in Allen's article to cause this effect
c) reseed before uploading
d) compact the database
e) delete any records before uploading the file

I did do something stupid when creating the table!

 

[theDBguy]

Hi Colin,

Please see my last post where I also did not "delete" any records. I realized (after looking at your posted image again) it's not necessary to delete any record. Although, what I ended up doing is not something I ever thought of doing before (I wasn't even sure it will work until I tried it).

 

[isladogs]

Ah .... but you haven't actually said what you did!

What I did was also something I'd never done before ... because it was a stupid thing to do ... but you haven't yet guessed what (or at least you haven't said how to replicate this)

 

[theDBguy]

Ah .... but you haven't actually said what you did!

What I did was also something I'd never done before ... because it was a stupid thing to do ... but you haven't yet guessed what (or at least you haven't said how to replicate this)

LOL. I actually said what I did in both my posts. I guess you didn't "look" close enough. At UA, we have a BB tag called

Spoiler

, but I guess AWF doesn't have it (I tried using it, and it didn't work), so I had to use another technique to "disguise" (hint, hint) my post (to avoid spoiling the potential answer for everybody else).

 

[isladogs]

Doh! I've used the same spoiler method myself many times.
Sorry to disappoint you but that's not it either .... but I can see that it would work

 

[theDBguy]

Doh! I've used the same spoiler method myself many times.
Sorry to disappoint you but that's not it either .... but I can see that it would work

Okay, I have been trying it out without looking at your file first, so I decided to download it today to give it another shot. Funny thing though, when I opened your file after downloading it and tried to enter a new record to the table (I selected Today from the date picker for StartDate), Access assigned ID=7 to the new record (I was expecting to see a 1). Could you try your file and see if you get a 1? Thanks.

 

[isladogs]

Oops - thanks for letting me know.
I should have checked the file before uploading it. That one worked as it's meant to do ... with no gremlins

I've now replaced the original version in post #1 with one that does show the strange behaviour.
Its also attached here!

 

[theDBguy]

Thanks. This one does give me a 1 when I try to add a new record. Now, is what I did anywhere close to what you did? I was able to modify my step to eliminate having to delete a record, but if it's not the same as what you did, then I guess there are a few ways to mess up an Autonumber field. Did your "accident" involve using an APPEND query?

 

[isladogs]

Hi DBG
I first noticed the problem when I tried to use an append query on the table and got a key violation error - 6 records could not be appended.

However no append queries were involved prior to that and so are not the cause of the effect

 

[Cronk]

Append queries are not the only way to add data and change the autonumber seed.


I did have a case some years ago with a corrupted record (memo field). As several days of data entry/amendment had taken place before it was noticed, I deleted the record and used an append query to insert the deleted record from a back up copy of the data.

 

[isladogs]

Hi Cronk
If you read his spoilers, that's one of the solutions the DBGuy suggested.

BUT to repeat that's not what I did ...
No append, update or delete queries were used in achieving this 'feature'
See post #6 for further info

 

[Deleted member 147267]

You could re-seed the table via code, but I would hardly call that an accident unless the accident was to use the wrong parameters.

 

[isladogs]

I originally used the word 'unintentionally' to describe how this occurred.
The explanation is in how I created and populated the table

 

[isladogs]

Congratulations to Frothy for working out how I did this.
He said he knew as he'd also done the same dumbass method himself!!!!

The issue with repeat autonumbers came about when I created a simple routine for use with my synchronise data website article: http://www.mendipdatasystems.co.uk/synchronise-data/4594507704

I used a backup table tblDataBKP to 'restore' a table tblData to its original state ready for the next synchronisation test.
The backup table has an autonumber PK field (ID) and 6 records.

Back in post #6 & #16, I listed several things I didn't do:

I didn't do any of the following:
a) intend to create a table like this - that was done unintentionally
b) do anything in Allen Browne's article to cause this effect
c) reseed before uploading
d) compact the database
e) delete any records before uploading the file
f) use append, update or delete queries to achieve this 'feature'

I was careful with how I worded each of those points!
Method 1 explains what I actually did

Method 1 - UPDATED EXPLANATION
The very stupid method I used to restore the table was the cause of the problem:
- Use a make table query to create populate tblData.
- This does not re-create the PK
- I then used a data definition query to set the ID field as the PK field but mistakenly set as autonumber again
RESULT: The table has 6 records but the next record added will have ID=1.
This causes a key violation error.
Similarly for the next 5 records added until it reaches ID=7

The attached database includes this method so you can try each method for yourself.
Feel free to say to yourself "How can anyone be so dumb as to do this!"

The attached db also contains 2 methods that do work

Method 2
As method 1 but set the PK field without resetting the autonumber
RESULT: The next record added will have ID=7. FIXED!

Method 3
The correct method is of course to create the table first
e.g. using a data definition query to include an autonumber PK field
Then use an append query to populate the table
RESULT: The next record added will have ID=7. FIXED!

NOTE I'm in the process of updating my website article to fix this error and add several additional methods of synchronising data from an external table with no PK field e.g. Excel or CSV file

I have now updated the attached file

 

[Frothingslosh]

They do say that experience is knowledge you acquire immediately after you actually needed it.