Add New Field to Existing ACCESS table

[Liz]

Is there a way to add a new field (column) to an ACCESS table using VB?
Something comparable to Transact-SQL "Alter Table Add Column ..."?

If so, an example would be extremely helpful.

Thanks in advance!!!!

 

[Dirk Goldgar]

Is there a way to add a new field (column) to an ACCESS table using
VB? Something comparable to Transact-SQL "Alter Table Add Column ..."?

If so, an example would be extremely helpful.

Thanks in advance!!!!

You can do it by executing an SQL statement, like this:

CurrentDb.Execute _
"ALTER TABLE ADD COLUMN ...", _
dbFailOnError

You'll want to look at the Jet SQL Reference in the online help to get
the exact syntax for the ALTER TABLE statement. You may need to set a
reference to the Microsoft DAO 3.6 Object Library, in order for the
dbFailOnError constant to be defined.

Or you can do it using the DAO TableDef object:

Dim db As DAO.Database
Dim td As DAO.TableDef

Set db = CurrentDb
Set td = db.TableDefs("MyTable")

With td
.Fields.Append .CreateField("NewField", dbText)
End With

Set td = Nothing
Set db = Nothing

For the above, you definitely need the reference to DAO.