access vba alter table

[Soma_rich]

Bit of a two sided question this,
1) How can I specify where in a table I want to insert a column, I would like the ID column at the begining, but this code always appends to the end:

DoCmd.RunSQL "ALTER TABLE OS_MAINb ADD COLUMN [ID2] BYTE"

2) Now I am sure this is a simple one, how do you define the column as an Autonumber datatype?

Thanks in advance

 

[DJkarl]

use AUTOINCREMENT for autonumber

 

[Pat Hartman]

Using DDL, you cannot specify the ordinal position of a column. You "may" be able to do this if you use the tabledefs collection to add the field since each field has an .ordinalposition property.

 

[Call_Me_Sam]

Bit of a two sided question this,
1) How can I specify where in a table I want to insert a column, I would like the ID column at the begining, but this code always appends to the end:

DoCmd.RunSQL "ALTER TABLE OS_MAINb ADD COLUMN [ID2] BYTE"

2) Now I am sure this is a simple one, how do you define the column as an Autonumber datatype?

Thanks in advance

From what i've read columns in tables do not actaully have any order, they may appear to but thisis due to the tool you are using. I guess the software (Access) is simply doing what its told and altering the table.

The question i guess this raises is why does anyone need the ID (autonumber) column at the left most position if not only for clarity, maybe set as a PK.

My next is, why use code to alter a table when you could simply open the table and add the column?

 

[Pat Hartman]

Columns do have an ordinal order and that is kept as a property of each field.

There are some cases where updates need to be automated and the only way to do that is with DDL. For example, if you have distributed an application and for whatever reason, cannot modify the back end directly, you can create a procedure that will run automatically and add a new column or whatever. This is more common in the case of retail software since if it is something you did for your employer, you probably will have access to the back end to change it using the GUI (after you backed it up of course)