Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-13-2019, 09:57 AM   #16
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Repeated Autonumber Gremlins

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 01-13-2019 at 02:24 PM. Reason: clarification
isladogs is offline   Reply With Quote
Old 01-13-2019, 12:12 PM   #17
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 120
Thanks: 0
Thanked 19 Times in 17 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Repeated Autonumber Gremlins

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.
Micron is offline   Reply With Quote
Old 01-13-2019, 12:43 PM   #18
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Repeated Autonumber Gremlins

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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 01-13-2019 at 06:10 PM. Reason: clarification
isladogs is offline   Reply With Quote
Old 01-14-2019, 10:34 AM   #19
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Repeated Autonumber Gremlins

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/s...ata/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:
Quote:
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
Attached Files
File Type: zip RepeatAutonumber - Explained_v2.zip (42.6 KB, 6 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 01-14-2019 at 01:14 PM.
isladogs is offline   Reply With Quote
The Following 2 Users Say Thank You to isladogs For This Useful Post:
jdraw (01-14-2019), theDBguy (01-14-2019)
Old 01-14-2019, 10:43 AM   #20
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,051
Thanks: 79
Thanked 427 Times in 387 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Repeated Autonumber Gremlins

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Frothingslosh; 01-14-2019 at 01:56 PM.
Frothingslosh is offline   Reply With Quote
Old 01-14-2019, 11:02 AM   #21
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 472
Thanks: 5
Thanked 87 Times in 86 Posts
theDBguy will become famous soon enough
Re: Repeated Autonumber Gremlins

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,

Quote:
- 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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by theDBguy; 01-14-2019 at 11:09 AM.
theDBguy is online now   Reply With Quote
Old 01-14-2019, 11:12 AM   #22
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Repeated Autonumber Gremlins

Quote:
This does not re-create the PK or the autonumber datatype
Hi DBG
Oops - Sorry - I'm still making stupid mistakes!
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

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 01-14-2019 at 11:19 AM. Reason: Extra info
isladogs is offline   Reply With Quote
Old 01-14-2019, 11:14 AM   #23
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 472
Thanks: 5
Thanked 87 Times in 86 Posts
theDBguy will become famous soon enough
Re: Repeated Autonumber Gremlins

Hi Colin,


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


Cheers!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 01-14-2019, 01:11 PM   #24
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Repeated Autonumber Gremlins

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
Attached Files
File Type: zip RepeatAutonumber - Explained_v2.zip (42.6 KB, 8 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-14-2019, 01:54 PM   #25
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,725
Thanks: 64
Thanked 1,252 Times in 1,151 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Repeated Autonumber Gremlins

Quote:
Originally Posted by Frothingslosh
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.)
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 01-14-2019, 01:56 PM   #26
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,051
Thanks: 79
Thanked 427 Times in 387 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Repeated Autonumber Gremlins

Bah, should have been 'acquire', not 'require'. Fixing the original quote.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 01-14-2019, 02:40 PM   #27
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Repeated Autonumber Gremlins

Quote:
Originally Posted by The_Doc_Man View Post
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....
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-14-2019, 08:14 PM   #28
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 120
Thanks: 0
Thanked 19 Times in 17 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Repeated Autonumber Gremlins

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...
Micron is offline   Reply With Quote
Old 01-15-2019, 02:37 AM   #29
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Repeated Autonumber Gremlins

Sorry about that. I made a complete pig's ear of this ....

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)
Attached Images
File Type: png Capture.PNG (13.2 KB, 41 views)
Attached Files
File Type: zip Autonumber Trickery.zip (19.2 KB, 7 views)

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Tags
repeated autonumbers

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Repeated code fat controller Modules & VBA 3 11-26-2014 03:04 AM
Gremlins in my form shift2076 Forms 2 04-12-2007 08:06 AM
Gremlins in the VBE editor Steven Deetz Modules & VBA 4 03-15-2005 11:14 AM
repeated prompting enigmajl Reports 1 08-11-2004 07:14 PM
Repeated value in combo box ohio Forms 2 01-17-2001 04:35 PM




All times are GMT -8. The time now is 08:58 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World