Adding column to first position.

[xopherira]

I am adding a new column to use as primary key. How do I add column to first position?

DoCmd.RunSQL ("ALTER TABLE source_data " & "ADD testDelete COUNTER;")

[rpeare]

have you tried

docmd.runsql ("ALTER TABLE Source_Data ADD testDelete COUNTER BEFORE [First Field name];")

[xopherira]

Yes and I get a Syntax error message.
Run-time error '3293':
Syntax error in ALTER table

[rpeare]

ok must be SQL only then.

Is there a specific reason you need to alter the field order? you should be able to get around it or display your information in the order you want without altering the table structure, I've got to assume this has to do with importing data from another data source where the field structure is different but then I don't understand why you'd want to do this programmatically when it could be a one time change.

After doing a quick search I don't know if it's possible to change the field order of a table unless you were to create a new table putting your 'new' column in first then adding all the columns from the existing table, then deleting the original table and renaming the 'new' table to the 'old' table's name. You really shouldn't have to do this though, order of columns should not matter no matter what you're doing.

[xopherira]

Yes, Importing excel spreadsheet and adding field as "primary key". Yes they are different, need to add and delete before export. I guess I can just add and use where it is placed.

[June7]

Bing: access vba change order of fields in table

http://www.access-programmers.co.uk/...ad.php?t=75996

[rpeare]

hah shows what I know. I thought if you used the column index (ordinal position) you'd have to alter the position of every field after it. Should have known better.