How do I insert multiple columns in a table?

[Guest]

I need to insert mulitple columns into a table that I created with a make
table query. Rather than opening the table in the design view and adding the
columns there, I was wondering is there a macro or a module that will do this
for me?

Thank you.

 

[Jeff L]

See ALTER TABLE in help.

 

[Granny Spitz via AccessMonster.com]

I was wondering is there a macro or a module that will do this
for me?

No. But you can write a query that will add these new columns and then run
the query. Create a new query and open it in SQL view. Write your ALTER
TABLE and ADD COLUMN syntax like this, and then run the query:

ALTER TABLE tblNewTable
ADD COLUMN ID AutoIncrement, TxtFld Text (25) NOT NULL,
LngFld LONG, CurFld CURRENCY,
CONSTRAINT PrimaryKey Primary Key (ID);

 

[Roger Carlson]

ALTER TABLE will work after the fact, of course, but if you're doing it on
an on-going basis, you can create extra fields right in the MakeTable query.
Something like this:

SELECT ID, "" AS TextField, 0 as LongField, CDbl("0") AS DoubleField,
CDate("0") As DateField Name, Phone INTO Table2
FROM Table1;

TextField with be text, LongField with be a long integer, DoubleField will
be a double precision number, and DateField will be a date/time field. Now,
of course, you have filled these fields with default values in order to
create the type of field you want. You can follow this up with an Update
query to null the fields

UPDATE Table2 SET TextField = Null, DoubleField = Null, LongField = Null,
DateField = Null;

or fill them with the appropriated data.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

Just for completeness, it IS possible to do this in VBA code using the DAO
object model:

Sub ModifyTable()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("Table1")

Set fld = tdf.CreateField("Test1", dbText)
' Set field properties
fld.Required = True
' Append field to Fields collection
tdf.Fields.Append fld

Set fld = tdf.CreateField("Test2", dbLong)
tdf.Fields.Append fld
End Sub

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L