In my code, I create and fill a tab

[teach314]

In my code, I create and fill a table that typically has about 100,000 records. The first field, ID, is an autonumber field.
Later in my code, I copy this table to other MDB programs. Doing this causes the ID numbers to keep incrementing, but I want each copy to be IDENTICAL. So, I try to change the Data Type from Autonumber to Long with DDL code like...

db.Execute "ALTER TABLE " & strTblName & " ALTER COLUMN [ID] LONG"

Usually this works perfectly, but when the table occasionaly has about 600,000 records, I get an error message re Lack of System Resources.
Even if i manually open the table in Design View and try to change the ID field from Autonumber to Number (Long), I get the error message "There isn't enough disk space of memory."

Here's the question - how can I use VBA or DDL to modify a field's data type from Autonumber to Long WHEN THE TABLE IS LARGE?

Thanks
teach314

 

[teach314]

ok - I found an answer to the problem.

a) in the table, I called the Autonumber field [ID_TEMP], and included an EMPTY (LONG)field [ID].

Once the table was filled, I used DDL to...

b) COPY the values from [ID_TEMP] to [ID]....

c) DROP the PRIMARY KEY constraint from [ID_TEMP]...

d) DROP the Autonumber field [ID_TEMP]...

e) ADD PRIMARY KEY to Long field [ID].

This works perfectly with very large tables unlike the earlier method.

teach314