none
How to add columns at a specific position in existing table in Access DataBase RRS feed

  • Question

  • Hi,

    I created a table with 5 columns but i missed one column. The missed column should be 3rd row (column's place). I don't want to drop that table and don't want to create it again, I need to alter that table and i have to add that column in 3rd row

    Below query used in SQL.

    ALTER table table_name Add column column_name3 integer AFTER column_name2

    How to call that column in 3rd row in access DB.

    Thanks

    Thursday, December 22, 2016 7:21 AM

All replies

    1. Open the table in design view.
    2. Select the third colmn (click on the record selector at the left border).
    3. Press the Insert key.

    This adds an empty row where you can fill in the missing column.

    You could also add the column at the end of the table and then drag it with the mouse to the desired place (again by click-and-drag on the record selector at the left border).

    Matthias Kläy, Kläy Computing AG

    Thursday, December 22, 2016 7:55 AM
  • Thanks for your quick response.

    i required access query to alter table. 

    Thursday, December 22, 2016 9:41 AM
  • DDL can be use to create tables, fields, change their size and type, but sadly I do not know of a way to reposition the field. 

    The only way I am aware to do this is through VBA.  Something like:

    CurrentDb.TableDefs("YourTableName").Fields("YourFieldName").OrdinalPosition=0
     

    I should point out that at the end of the day it should be irrelavent as no one should be directly interfacing your tables and you can control the positioning of field in queries when you build them.


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Thursday, December 22, 2016 11:23 AM
  • You can only do that manually as described in the first post.  Access SQL does not have that capability, so you would have to drop and re-add the table if you absolutely needed a specific column order accomplished through code.  See the following article for Alter Table options in Access:

    Alter Table Syntax, Access SQL

    To my knowledge, this isn't even possible in SQL Server, which is much more flexible.

    That said, if you are working with a table through code, the order of the columns is generally not important.  If the data needs to be presented to the user in a specific layout, that can be accomplished through queries and forms with recordsources created dynamically.

    Edit:

    Missed Daniel's post about setting ordinal position through VBA.  I was not aware of that option.  Learn something new every day!


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Thursday, December 22, 2016 11:47 AM
    Thursday, December 22, 2016 11:29 AM
  • As said by Daniel Pineault and Miriam Bizup, you cannot use Access DDL alone. But the following example works.

    The only caveat is that you need to renumber the column positions from the new column to the end, because one can have the same ordinal position for more than one column (!). To demonstrate this, I print out the field names and positions after each step.

    Dim db  As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
    
    Set db = CurrentDb
    
    Set qdf = db.CreateQueryDef("", "Create Table tblColPos " & _
                                    " (Nr Long Constraint PrimaryKey Primary Key, " & _
                                    " Col1 Text(20), " & _
                                    " Col2 Text(20), " & _
                                    " Col3 Text(20));")
    qdf.Execute dbFailOnError
                                  
    Set tbl = db.TableDefs("tblColPos")
    For Each fld In tbl.Fields
        Debug.Print fld.Name, fld.OrdinalPosition
    Next
                                    
    Set qdf = db.CreateQueryDef("", "Alter Table tblColPos Add Column ColNew Text(20);")
    qdf.Execute dbFailOnError
    
    tbl.Fields.Refresh
    For Each fld In tbl.Fields
        Debug.Print fld.Name, fld.OrdinalPosition
    Next
    
    tbl.Fields("ColNew").OrdinalPosition = 2
    
    tbl.Fields.Refresh
    For Each fld In tbl.Fields
        Debug.Print fld.Name, fld.OrdinalPosition
    Next
    
    tbl.Fields("Col2").OrdinalPosition = 3
    tbl.Fields("Col3").OrdinalPosition = 4
    
    tbl.Fields.Refresh
    For Each fld In tbl.Fields
        Debug.Print fld.Name, fld.OrdinalPosition
    Next
    
    qdf.Close
    Set qdf = Nothing
    db.Close
    Set db = Nothing
    

    Matthias Kläy, Kläy Computing AG

    Thursday, December 22, 2016 12:24 PM
  • Hi Danish Qadri,

    As mbizup MVP already provide you a link for the Alter table.

    you can see that syntax of alter table don't have any parameter that can support to set or change the position of new column.

     so as Daniel  Pineault and mklaey suggest you to use OrdinalPosition in VBA is the other way to fulfil your requirement.

    so you can refer the suggestion provided by them and if you think that that can solve your issue then mark their suggestion as an answer.

    only using by the Alter Table Query its not possible.

    Reference:

    Field.OrdinalPosition Property (DAO)

    ALTER TABLE Statement (Microsoft Access SQL)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 23, 2016 12:26 AM
    Moderator
  • The great point is: "you need to renumber the column positions from the new column to the end"

    That is what I have been missing for years.

    Thanks

    Saturday, March 3, 2018 10:44 PM