J
JOM
Is it possible to add a new column to an already existing table? If so how
do you do that?
do you do that?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Brendan Reynolds
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
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
J
JOM
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
than going to the table design view to add it
J
John Vinson
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]
J
John Vinson
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]
J
JOM
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!
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!
K
KARL DEWEY
Open the table in design view and add.
J
JOM
I would like to use a query and not do it by opening the table in design
view...
view...
J
John Vinson
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]