John W Colby
jwcolby at gmail.com
Fri Mar 7 12:27:36 CST 2014
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