Adding a column to an existing table

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

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...ireference.asp

--
Brendan Reynolds


"JOM" wrote in message
...
Is it possible to add a new column to an already existing table? If so
how
do you do that?

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?

On Wed, 16 Nov 2005 08:31:06 -0800, JOM
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]

On Wed, 16 Nov 2005 09:21:05 -0800, JOM
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]

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...ireference.asp

--
Brendan Reynolds


"JOM" wrote in message
...
Is it possible to add a new column to an already existing table? If so
how
do you do that?

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?

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" wrote in message
...
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...ireference.asp

--
Brendan Reynolds


"JOM" wrote in message
...
Is it possible to add a new column to an already existing table? If so
how
do you do that?

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
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]

On Wed, 16 Nov 2005 12:57:27 -0800, JOM
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]