John W Colby
jwcolby at gmail.com
Fri Mar 7 15:27:24 CST 2014
I tried your code and I ended up adding a new record to table A (manually) with an autonumber of 1. John W. Colby Reality is what refuses to go away when you do not believe in it On 3/7/2014 4:16 PM, Bill Benson wrote: > John and Gustav, the code I wrote ended with the table in the condition > that one cannot MANUALLY add a record. > > I don't know if inline SQL works, I did not try. > > It is pretty easy to drop those lines of code into the immediate window or > a function, execute them, and see what I mean. > > I am not going to second guess Jet, and if anyone tells me the behavior > makes sense to them, I will believe you LOL. > > Bill > On Mar 7, 2014 1:28 PM, "John W Colby" <jwcolby at gmail.com> wrote: > >> Bill, >> >> Correct me if I am wrong but what is going on is that the autoincrement is >> a SIGNED long integer, i.e. +/- 2^31. I never understood why they used a >> signed long instead of an unsigned long but it is what it is. >> >> So... the way the autoincrement is supposed to work is that it increments >> starting at 1 up until it would "roll over". What it rolls over TO is >> -2^31 (a very large negative number) and then starts counting back towards >> 0. Essentially it simply adds 1 to the number until it counts all the way >> up to 2^33. When it reaches 2^31+1 it "rolls over and continues counting. >> Since 2^33 won't fit in a 32 bit number, when it adds 2^33 ones it is back >> to zero, i.e. it "rolls over" again. >> >> Back to your example, I think that you are trying to insert TWO values >> into table A, the long int and the text value. Since the long in table A >> is an autoincrement, try adding just the text value into Table A and see >> what happens. >> >> John W. Colby >> >> Reality is what refuses to go away >> when you do not believe in it >> >> On 3/7/2014 12:02 PM, Bill Benson wrote: >> >>> OK, this discussion has intrigued me like few others. I have found a fun >>> and >>> free way to corrupt my database. Play along boys and girls and I am sure >>> it >>> will be fun for you too. In the below example, B is used as a temp table >>> to >>> hold a couple of values for the fun and exciting purpose of destroying A's >>> usefulness. >>> >>> CurrentDb.Execute "DROP TABLE [A];" >>> CurrentDb.Execute "DROP TABLE [B];" >>> CurrentDb.Execute "CREATE TABLE [A] (ID AUTOINCREMENT NOT NULL PRIMARY >>> KEY, >>> Field2 TEXT);" >>> CurrentDb.Execute "CREATE TABLE [B] (ID LONG, Field2 TEXT);" >>> CurrentDb.Execute "INSERT INTO B (ID, FIELD2) VALUES (-2147483648, >>> 'DELETEME');" >>> CurrentDb.Execute "INSERT INTO B (ID, FIELD2) VALUES (2147483647, >>> 'DELETEME');" >>> CurrentDb.Execute "INSERT INTO A SELECT * FROM B;" >>> CurrentDb.Execute "DELETE FROM [A] WHERE ID =2147483647;" >>> CurrentDb.Execute "DROP TABLE [B];" >>> >>> This leaves a single record in table A with ID = -2147483648 >>> >>> Now try and add a new record to A and you will find you cannot. Why >>> doesn't >>> it autoincrement to -2147483647 ? That is a very good question. Let's now >>> delete the one record remaining and try again: >>> >>> CurrentDb.Execute "DELETE FROM [A] WHERE ID =2147483648;" >>> >>> Or alternatively >>> CurrentDb.Execute "DELETE * FROM [A];" >>> >>> Now add a new record and you will see that the database begins a new >>> record >>> at ID = -2147483647!!!! >>> >>> DEFIES ALL LOGIC! It has been my pleasure to baffle you! >>> >>> >> --- >> This email is free from viruses and malware because avast! Antivirus >> protection is active. >> http://www.avast.com >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com