Change the Type of a Table Column from Autonumber to Number ?

[JGT]

Hi folks,

One can manually change the Type of a Table Column from Autonumber to Number (long int). Is it possible to do this with VBA code?

Thanks for any hints.

 

[georgedwilkinson]

If you're looking for options, in addition to the two Pat mentioned, you might look into ADOX. I'm thinking DDL is a better option, as presented by Pat. But at least this is one more tool.

 

[JGT]

Hi Pat, Hi George,

Thank you for replying.

Tabledefs collection: as far as I know there are no commands to change things, only to delete, add (new created) objects.
DDL: is almost the same, a very limited command set: Create, Alter and Drop. e.g. ‘you cannot create "AutoNumber Replication," "HyperLink," or "Lookup" type fields using a Microsoft Access DDL’ - MS article 180841. With regard to the Alter statement, it says: ‘the Alter statement won't let you alter an existing field in an Access table (for example, to change the field's data type)’.
ADOX: I didn’t work with ADOX before, because I get the message "Compile error - user-defined function not defined". Appearently ADOX is not std, must be loaded. Some active-X module? Which one?

 

[jal]

I don't know of an efficient way, but an inefficient way is to add a new column, copy the data into the new column, drop the old column, and then hopefully rename the new column.

But I don't know how to rename a column, so at that point you could recreate the old column (under the new datatype) and then copy the data back from the new column into the old. Very ineffecient, but it would probably work.

 

[pono1]

 DoCmd.RunSQL _
  "ALTER TABLE TheTableName " & _
  "ALTER COLUMN ID_Number long"

Regards,
Tim

 

[JGT]

Tim, I don’t believe this, it works! fantastic, I was preparing myself to copy, delete, add columns.
Thank you very much.