[AccessD] Membership File Changes

Awkward....  ;)

Actually, I suspect you either your program works differently than mine
(2010 here) or you are not telling all, John LOL.

Are you *sure* Access automatically puts the correct autosequence number the
first time you attempt to enter information into the table, after running
that code? 

For me Access wanted to re-use -2147483648. This is whether I click on the
new record button, or just click in the open cell in Field2, in datasheet
view of Table A..

Then, if I press ESC, ONLY THEN Access properly attempts a value of ID  =
-2147483647.

Again, this may make sense to someone, but it does not make sense to me.

Sub Test()
Dim MyDB As DAO.Database

Set MyDB = CurrentDb
With MyDB
    On Error Resume Next
    .Execute "DROP TABLE [A];"
    .Execute "DROP TABLE [B];"
    .Execute "CREATE TABLE [A] (ID AUTOINCREMENT NOT NULL PRIMARY KEY,
Field2 TEXT);"
    On Error GoTo Get_Out
    .Execute "CREATE TABLE [B] (ID LONG, Field2 TEXT);"
    .Execute "INSERT INTO B (ID, FIELD2) VALUES (-2147483648,'DELETEME');"
    .Execute "INSERT INTO B (ID, FIELD2) VALUES (2147483647,'DELETEME');"
    .Execute "INSERT INTO A SELECT * FROM B;"
    .Execute "DELETE FROM [A] WHERE ID =2147483647;"
    .Execute "DROP TABLE [B];"
End With

Get_Out:
Set MyDB = Nothing
End Sub



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Friday, March 07, 2014 12:03 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Membership File Changes

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!

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com