Repeated Autonumber Gremlins (1 Viewer)

theDBguy

I’m here to help
Staff member
Local time
Today, 08:30
Joined
Oct 29, 2018
Messages
21,358
Hi Colin,


In your list of "I didn't do any of the following:," I guess we could argue item (c) could have been worded as "intentionally reseed before uploading." Because the code you used:
Code:
CurrentDb.Execute "ALTER TABLE tblData ALTER COLUMN ID COUNTER(1, 1) NOT NULL PRIMARY KEY;"
Does reseed the Autonumber field by using COUNTER(1, 1) because it is saying start the counter at 1 and increment by 1.

Also, in your "Method 1," you said by using a make-table query,

- This does not re-create the PK or the autonumber datatype
I agree with the first part but not the second part. As a test, I executed the following code in the Immediate Window:
Code:
DoCmd.Close acTable, "tblData"
DoCmd.DeleteObject acTable, "tblData"
CurrentDb.Execute "SELECT tblDataBKP.* INTO tblData FROM tblDataBKP;"
I then opened the new tblData in Design view and see that the ID field is already set as an Autonumber data type. When I tried to add a new record to this new table, the ID field correctly used the next number in sequence (7).


The way I might fix Method 1 is as follows, rather than use the following DDL statement:
Code:
'add PK if not already done
CurrentDb.Execute "ALTER TABLE tblData ALTER COLUMN ID COUNTER(1, 1) NOT NULL PRIMARY KEY;"
You could try using the following DDL statement instead:
Code:
CurrentDb.Execute "ALTER TABLE tblData ADD CONSTRAINT PK PRIMARY KEY (ID);"
When I opened your original table in design view, I noticed the weird name for your primary key index. I knew it had something to do with what happened, but I didn't think you were using code to cause the problem.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 15:30
Joined
Jan 14, 2017
Messages
18,186
This does not re-create the PK or the autonumber datatype

Hi DBG
Oops - Sorry - I'm still making stupid mistakes! :banghead:
Just about to sign of for a while but will correct my earlier explanation later this evening!
Not sure if my brain was working at all during this process

As for the point about 'reseeding', I was deliberately being economical with the truth there ......
and I was partly getting my own back with you for being economical with the truth in your combobox gremlins example!;)

Of course I was aware that I had seeded using code.
However I didn't reseed before uploading - on other words I hadn't intentionally set this up to fail if you see what I mean

Hadn't noticed the weird index name before - not done by me

Anyway, I still say method 3 is the best method to use
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:30
Joined
Oct 29, 2018
Messages
21,358
Hi Colin,


Not saying Method 3 is not the best method. Just saying we could also fix Method 1.


Cheers!
 

isladogs

MVP / VIP
Local time
Today, 15:30
Joined
Jan 14, 2017
Messages
18,186
OK - I managed to confuse myself completely in my earlier explanation
Attached is what I hope is a correct explanation! I have also edited my earlier attempt in #19

In summary....
Method 1 - WRONG - I incorrectly reset the autonumber whilst setting the PK field

Method 2 - FIXED - This time I set the PK without resetting the autonumber

Method 3 - BETTER - Created the table together with autonumber PK and then populated it

Thanks to DBG for correcting my dumb errors
 

Attachments

  • RepeatAutonumber - Explained_v2.zip
    42.6 KB · Views: 88

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:30
Joined
Feb 28, 2001
Messages
26,999
Frothingslosh said:
They do say that experience is knowledge you require immediately after you actually needed it.

I've always heard this as "Experience is the ability to more quickly recognize your mistakes when you make them again" (attributed to author Ambrose Bierce.)
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 11:30
Joined
Oct 17, 2012
Messages
3,276
Bah, should have been 'acquire', not 'require'. Fixing the original quote.
 

isladogs

MVP / VIP
Local time
Today, 15:30
Joined
Jan 14, 2017
Messages
18,186
I've always heard this as "Experience is the ability to more quickly recognize your mistakes when you make them again" (attributed to author Ambrose Bierce.)

Well this has certainly been an 'experience' for me....
 

Micron

AWF VIP
Local time
Today, 11:30
Joined
Oct 20, 2018
Messages
3,476
isladogs, you've been confused by all this? I was giving up on following the narrative, then it seems you did exactly what I was referring to. This is what I used to reseed the table and reproduce the results:
CurrentDb.Execute "ALTER TABLE [tabledata] ALTER COLUMN ID COUNTER(1,1)"


Oh well...
 

isladogs

MVP / VIP
Local time
Today, 15:30
Joined
Jan 14, 2017
Messages
18,186
Sorry about that. I made a complete pig's ear of this .... :eek:

Going back to the article by Allen Browne, I've since been trying to replicate his comments about negative autonumbers. I've no idea how it could happen 'accidentally' but its easy enough to fool Access to create these deliberately.
I've added 4 incorrect autonumber records using an APPEND query (just as DBG did earlier in this thread)



What's 'interesting' now is that the StartDate and EndDate for the first 4 records aren't editable (but the 'NumberField' is)
 

Attachments

  • Autonumber Trickery.zip
    19.2 KB · Views: 94
  • Capture.PNG
    Capture.PNG
    13.2 KB · Views: 169

Users who are viewing this thread

Top Bottom