Macro - adding a field

J

J Austin

Is it possible to use the macro capability in Access in 2007 to add a new
field to a data table? I have not been able to find a way to do this.
 
K

Ken Sheridan

The easiest way is with Jet SQL's Data Definition Language (DDL). The
following for instance will add a Required column MyNewColumn of text data
type and 50 characters maximum length:

ALTER TABLE MyTable
ADD COLUMN MyNewColumn TEXT(50) NOT NULL;

If you are executing the statement from with VBA and using ADO you'll need
to create a reference to the Microsoft ADO Extensions for DDL and Security
library first (Tools | References on the VBA menu bar).

Ken Sheridan
Stafford, England
 
J

J Austin

Ken,
Thanks, I'll have to get familiar with your solution as it would currently
be beyond my programming capability. I was hoping I could do it directly
from VBA if I couldn't use the macro capability.

J Austin
 
J

James A. Fortune

J said:
Ken,
Thanks, I'll have to get familiar with your solution as it would currently
be beyond my programming capability. I was hoping I could do it directly
from VBA if I couldn't use the macro capability.

J Austin
Click to expand...

If you really need to skip using DDL, which is the easiest way as Ken
stated, you could use the fact that TableDef's have a CreateField method.

Something like:

Set tdf = MyDB.CreateTableDef("tblOutput")
Set fld = tdf.CreateField("Notes", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("StreamID", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("Diameter", dbDouble)
tdf.Fields.Append fld
tdf.Fields.Refresh
MyDB.TableDefs.Append tdf
MyDB.TableDefs.Refresh
Set tdf = Nothing
Set fld = Nothing

Also, TableDef's have a CreateIndex method and an Indexes collection so
even more than that can be done with VBA but using DDL is much more
elegant. I used CreateField in a post once simply to try something
different since creating an empty table with all the correct fields and
deleting all the records before use with a Delete Query worked
adequately when used in conjunction with MyDB.RecordsAffected.

James A. Fortune
[email protected]
 
K

Ken Sheridan

No reason why you can't do it in entirely in VBA. Here's the equivalent in
VBA to the DDL I posted:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("MyTable")
With tdf
.Fields.Append .CreateField("MyNewColumn", _
dbText, 50)
.Fields("MyNewColumn").Required = True
End With

Note that I've used DAO here, so you'd need to be sure you have a reference
to the Microsoft DAO object library (Tools | References on the VBA menu bar
again). BTW the space between Append and .CreateField is essential here.

Or you can execute the DDL statement from within VBA:

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "ALTER TABLE MyTable " & _
"ADD COLUMN MyNewColumn " & _
"TEXT(50) NOT NULL"

dbs.Execute strSQL, dbFailOnError

which personally I find simpler than calling the CreateField method. Again
I've used DAO here, so be sure you have a reference to the DAO library.

Going back to your original question you can in fact execute the SQL
statement by means of the RunSQL action in a macro. With my example you'd
enter:

ALTER TABLE MyTable ADD COLUMN MyNewColumn TEXT(50) NOT NULL

as the SQL statement when creating the macro. I'd recommend using VBA,
though. For one thing you can trap any errors in VBA, which you can't do in
a macro. Macros are cheap and cheerful, but do have their limitations.

Ken Sheridan
Stafford, England
 
J

James A. Fortune

Ken said:
No reason why you can't do it in entirely in VBA. Here's the equivalent in
VBA to the DDL I posted:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("MyTable")
With tdf
.Fields.Append .CreateField("MyNewColumn", _
dbText, 50)
.Fields("MyNewColumn").Required = True
End With

Note that I've used DAO here, so you'd need to be sure you have a reference
to the Microsoft DAO object library (Tools | References on the VBA menu bar
again). BTW the space between Append and .CreateField is essential here.

Or you can execute the DDL statement from within VBA:

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "ALTER TABLE MyTable " & _
"ADD COLUMN MyNewColumn " & _
"TEXT(50) NOT NULL"

dbs.Execute strSQL, dbFailOnError

which personally I find simpler than calling the CreateField method. Again
I've used DAO here, so be sure you have a reference to the DAO library.

Going back to your original question you can in fact execute the SQL
statement by means of the RunSQL action in a macro. With my example you'd
enter:

ALTER TABLE MyTable ADD COLUMN MyNewColumn TEXT(50) NOT NULL

as the SQL statement when creating the macro. I'd recommend using VBA,
though. For one thing you can trap any errors in VBA, which you can't do in
a macro. Macros are cheap and cheerful, but do have their limitations.

Ken Sheridan
Stafford, England
Click to expand...

There's nothing I can add to such a great answer.

James A. Fortune
[email protected]
 
K

Ken Sheridan

Thanks James, its very much appreciated.

Regards,

Ken Sheridan
Stafford, England
 
Top