Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Adding a column to an existing table

Discussion in 'Information Technology' started by JOM, Nov 17, 2005.

  1. JOM

    JOM
    Expand Collapse
    Guest

    Is it possible to add a new column to an already existing table? If so how
    do you do that?
     
  2. Loading...

    Similar Threads Forum Date
    SGPC voter forms have caste column Hard Talk Jun 13, 2010
    Sikh News The Faith Column (The New Statesman) Breaking News Jun 25, 2007
    Sikh News Literary revenge of the columnist's 'hopeless husband' (Daily Telegraph: UK News) Breaking News Feb 12, 2006
    Christianity The Giles Fraser Column (Ekklesia) Interfaith Dialogues Jun 7, 2005
    columnist:: June 1 , 2005 (City Pulse) Interfaith Dialogues Jun 2, 2005

  3. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    I'm assuming you mean programatically?

    Here are two methods ...

    Public Sub AddAColumn()

    'Using SQL DDL (Data Definition Language)
    Dim strSQL As String
    strSQL = "ALTER TABLE tblTest ADD COLUMN MyNewTextColumn nvarchar(50)"
    CurrentProject.Connection.Execute strSQL, , adCmdText

    'Using DAO (Microsoft Data Access Objects)
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Set db = CurrentDb
    Set tdf = db.TableDefs("tblTest")
    Set fld = tdf.CreateField("AnotherNewField", dbInteger)
    tdf.Fields.Append fld

    End Sub

    A third alternative is to use ADOX (Microsoft ADO Ext. 2.7 for DDL and
    Security). I don't use that much, so I won't attempt to provide an example
    myself, but here's a link to the on-line documentation ...

    http://msdn.microsoft.com/library/en-us/ado270/htm/admscadoapireference.asp

    --
    Brendan Reynolds


    "JOM" <JOM@discussions.microsoft.com> wrote in message
    news:B94F02CC-BED1-4D48-A74E-3AB1FF6D7848@microsoft.com...
    > Is it possible to add a new column to an already existing table? If so
    > how
    > do you do that?
     
  4. JOM

    JOM
    Expand Collapse
    Guest

    I think my question was incomplete, what I meant was to add a column other
    than going to the table design view to add it

    "KARL DEWEY" wrote:

    > Open the table in design view and add.
    >
    > "JOM" wrote:
    >
    > > Is it possible to add a new column to an already existing table? If so how
    > > do you do that?
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 16 Nov 2005 08:31:06 -0800, JOM
    <JOM@discussions.microsoft.com> wrote:

    >Is it possible to add a new column to an already existing table? If so how
    >do you do that?


    Sure; open the table in design view, add the new field.

    If the database is split, you need to do so by opening the backend
    database.

    John W. Vinson[MVP]
     
  6. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 16 Nov 2005 09:21:05 -0800, JOM
    <JOM@discussions.microsoft.com> wrote:

    >I think my question was incomplete, what I meant was to add a column other
    >than going to the table design view to add it


    Brendan's answers (any of the three) will work in that case.

    John W. Vinson[MVP]
     
  7. JOM

    JOM
    Expand Collapse
    Guest

    Thanks I applied that information in a query and it worked!

    That helped this is still part of the question. I would like to add 2
    columns of table1 and insert that information into the new column of a table2
    using a query...

    Is that possible!






    "Brendan Reynolds" wrote:

    > I'm assuming you mean programatically?
    >
    > Here are two methods ...
    >
    > Public Sub AddAColumn()
    >
    > 'Using SQL DDL (Data Definition Language)
    > Dim strSQL As String
    > strSQL = "ALTER TABLE tblTest ADD COLUMN MyNewTextColumn nvarchar(50)"
    > CurrentProject.Connection.Execute strSQL, , adCmdText
    >
    > 'Using DAO (Microsoft Data Access Objects)
    > Dim db As DAO.Database
    > Dim tdf As DAO.TableDef
    > Dim fld As DAO.Field
    > Set db = CurrentDb
    > Set tdf = db.TableDefs("tblTest")
    > Set fld = tdf.CreateField("AnotherNewField", dbInteger)
    > tdf.Fields.Append fld
    >
    > End Sub
    >
    > A third alternative is to use ADOX (Microsoft ADO Ext. 2.7 for DDL and
    > Security). I don't use that much, so I won't attempt to provide an example
    > myself, but here's a link to the on-line documentation ...
    >
    > http://msdn.microsoft.com/library/en-us/ado270/htm/admscadoapireference.asp
    >
    > --
    > Brendan Reynolds
    >
    >
    > "JOM" <JOM@discussions.microsoft.com> wrote in message
    > news:B94F02CC-BED1-4D48-A74E-3AB1FF6D7848@microsoft.com...
    > > Is it possible to add a new column to an already existing table? If so
    > > how
    > > do you do that?

    >
    >
    >
     
  8. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Open the table in design view and add.

    "JOM" wrote:

    > Is it possible to add a new column to an already existing table? If so how
    > do you do that?
     
  9. Brendan Reynolds

    Brendan Reynolds
    Expand Collapse
    Guest

    Looks like a new question to me, but whatever! :)

    You want to insert the sum of the two fields from which record in table 1
    into which record in table 2?

    --
    Brendan Reynolds

    "JOM" <JOM@discussions.microsoft.com> wrote in message
    news:B98BAD34-0B1D-4FA3-9F2D-5940AEB89E97@microsoft.com...
    > Thanks I applied that information in a query and it worked!
    >
    > That helped this is still part of the question. I would like to add 2
    > columns of table1 and insert that information into the new column of a
    > table2
    > using a query...
    >
    > Is that possible!
    >
    >
    >
    >
    >
    >
    > "Brendan Reynolds" wrote:
    >
    >> I'm assuming you mean programatically?
    >>
    >> Here are two methods ...
    >>
    >> Public Sub AddAColumn()
    >>
    >> 'Using SQL DDL (Data Definition Language)
    >> Dim strSQL As String
    >> strSQL = "ALTER TABLE tblTest ADD COLUMN MyNewTextColumn
    >> nvarchar(50)"
    >> CurrentProject.Connection.Execute strSQL, , adCmdText
    >>
    >> 'Using DAO (Microsoft Data Access Objects)
    >> Dim db As DAO.Database
    >> Dim tdf As DAO.TableDef
    >> Dim fld As DAO.Field
    >> Set db = CurrentDb
    >> Set tdf = db.TableDefs("tblTest")
    >> Set fld = tdf.CreateField("AnotherNewField", dbInteger)
    >> tdf.Fields.Append fld
    >>
    >> End Sub
    >>
    >> A third alternative is to use ADOX (Microsoft ADO Ext. 2.7 for DDL and
    >> Security). I don't use that much, so I won't attempt to provide an
    >> example
    >> myself, but here's a link to the on-line documentation ...
    >>
    >> http://msdn.microsoft.com/library/en-us/ado270/htm/admscadoapireference.asp
    >>
    >> --
    >> Brendan Reynolds
    >>
    >>
    >> "JOM" <JOM@discussions.microsoft.com> wrote in message
    >> news:B94F02CC-BED1-4D48-A74E-3AB1FF6D7848@microsoft.com...
    >> > Is it possible to add a new column to an already existing table? If so
    >> > how
    >> > do you do that?

    >>
    >>
    >>
     
  10. JOM

    JOM
    Expand Collapse
    Guest

    I would like to use a query and not do it by opening the table in design
    view...

    "John Vinson" wrote:

    > On Wed, 16 Nov 2005 08:31:06 -0800, JOM
    > <JOM@discussions.microsoft.com> wrote:
    >
    > >Is it possible to add a new column to an already existing table? If so how
    > >do you do that?

    >
    > Sure; open the table in design view, add the new field.
    >
    > If the database is split, you need to do so by opening the backend
    > database.
    >
    > John W. Vinson[MVP]
    >
     
  11. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 16 Nov 2005 12:57:27 -0800, JOM
    <JOM@discussions.microsoft.com> wrote:

    >That helped this is still part of the question. I would like to add 2
    >columns of table1 and insert that information into the new column of a table2
    >using a query...
    >
    >Is that possible!


    In two steps: add the column, then run an Update query updating it to
    the sum.

    BUT!!!

    Storing derived data such as this in your table accomplishes
    three things: it wastes disk space; it wastes time (almost
    any calculation will be MUCH faster than a disk fetch); and
    most importantly, it risks data corruption. If one of the
    underlying fields is subsequently edited, you will have data
    in your table WHICH IS WRONG, and no automatic way to detect
    that fact.

    Just redo the calculation whenever you need it, either as a
    calculated field in a Query or just as you're now doing it -
    in the control source of a Form or a Report textbox.


    John W. Vinson[MVP]
     

Share This Page