Add New Field to Existing ACCESS table

  • Thread starter Thread starter Liz
  • Start date Start date
L

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!!!!
 
Liz said:
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!!!!
Click to expand...

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.
 
Back
Top