[AccessD] Membership File Changes

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
>