Adding a column to an existing table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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-us/ado270/htm/admscadoapireference.asp
 
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
 
Is it possible to add a new column to an already existing table? If so how
do you do that?
Click to expand...

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]
 
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
Click to expand...

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!
 
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?
 
I would like to use a query and not do it by opening the table in design
view...
 
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!
Click to expand...

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]
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add column with data to an existing table 8
VBA - retrieve data on last create named range from previous inserted named range 0
New Column to Existing Table 1
How to add a column to an existing table and fill it (i.e., filldown) with a particular word / numbe 4
How can I change the column order using VBA 5
How to add an additional field in the existing database table 5
Export an Access table to an EXISTING Oracle Table 1
Making existing columns into look-up columns 3

Back
Top