Adding a column in a specific location

[AT76]

Hi,

I'm trying to add a column to my table but it adds it to the last column of the table.

mysql = "ALTER TABLE table1 ADD testColumn varchar(15)"

Is there a way to add this column say in the 5th column location?

Thank you!

 

[PHV]

In a RDBMS, the ordinal position of a column in a row is totally irrelevant.
What is your REAL issue ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MichaelRed]

possible? Yes

useful? mot generally.

look up the general usage (Codd's 12 rules). it is a relational database. generally objects are referred to by name, not ordinal position. Table type recordsets are not often displayed. queries permit the subsetof fields and their (relative) positioning.

Get thee to thy book store and peruse the tome on RELATIONAL database.



MichaelRed

 

[AT76]

Thank you PHV and Michael....

I'm trying to change the format of the table programmatically. I need to change it in a certain format for another application to work. This is why I would like to add some empty columns in my table in specific locations. I know I can do this thru vba and excel then exporting my modified sheet to an access table. I just wanted to see if I can do the same using access instead of excel.

 

[PHV]

And what about a MakeTable query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ddiamond]

I don't believe you can do it with an alter table statement, but you can do it using Access VBA.

set db = currentdb
set tds = db.TableDefs
set td = tds("Test")
set fds = td.fields
set fd = fs("Field3")
fd.OrdinalPosition=1

The OrdinalPosition is zero based, so the above code would make field3 the 2nd field. It was originally the 3rd field.

 

[AT76]

Thank you ddiamond. I will play around with your advice!

 

[Zion7]

a slight modification to ddiamond's code,

Dim tds As DAO.TableDefs, OPost As Variant, x As Integer, fd As DAO.Field, td As DAO.TableDef

Set tds = CurrentDb.TableDefs
Set td = tds("tblConfig")

OPost = Array(4, 2, 8, 3, 9, 1, 6, _
10, 0, 5, 7, 11, 23, 19, 17, 12, _
22, 13, 21, 14, 20, 15, 18, 16)

For Each fd In td.Fields
fd.OrdinalPosition = OPost(x)
x = x + 1
Next

Set tds = Nothing: Set td = Nothing