Question about ALTER TABLE and ALTER COLUMN

[BobV]

Group:

I am using Access 2002. I want to add a column to a table (MasterData) in
the backend database and then specify that it be an integer with a default
value of zero. I am using the following statement to add a column to the
MasterData table, but I would like to further specify that the column be an
integer with a default value of zero. Can someone suggest the proper syntax
for a VBA statement to do what I want to do? I think I need to use ALTER
COLUMN but I don't know the proper syntax. Any sample code would be greatly
appreciated.

BackDb.Execute "ALTER TABLE [MasterData] ADD COLUMN [CompanyOpen] Number;"

Thanks,
Bob V

 

[Kevin Jameson]

you cant do this with microsoft access 2003

you must upgrade to access 20004 by clicking here:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html

HTH

Kevin

 

[Dirk Goldgar]

Group:

I am using Access 2002. I want to add a column to a table
(MasterData) in the backend database and then specify that it be an
integer with a default value of zero. I am using the following
statement to add a column to the MasterData table, but I would like
to further specify that the column be an integer with a default value
of zero. Can someone suggest the proper syntax for a VBA statement to
do what I want to do? I think I need to use ALTER COLUMN but I don't
know the proper syntax. Any sample code would be greatly appreciated.

BackDb.Execute "ALTER TABLE [MasterData] ADD COLUMN [CompanyOpen]
Number;"

You can make it be an integer just by changing the data type you specify
when you create the column. NUMBER will get you a double-precision,
floating-point field. If you use INTEGER instead, you'll get a long
(4-byte) integer field; if you use SMALLINT, you'll get a 2-byte
integer field.

As for setting the default value, I don't think you can do it using DAO
to execute the SQL statement (though you could use DAO objects to
accomplish it), but it appears you *can* use ADO to execute the SQL
statement, like this:

Dim conn As ADODB.Connection

Set conn = New ADODB.Connection
conn.Open "<connection string for your back-end database>"

conn.Execute _
"ALTER TABLE [MasterData] " & _
"ADD COLUMN [CompanyOpen] INTEGER DEFAULT 0"

conn.Close

 

[BeWyched]

You can get most of the way there using DAO...

Set tdf = Backdb.CreateTableDef("MasterTable")
tdf.Fields.Append .CreateField("CompanyOpen", dbInteger)
Backdb.TableDefs.Append tdf

This will not set the default value to 0, but will do the rest.

 

[BobV]

Thank you all for the help. This is great!

BobV

 

[Dirk Goldgar]

You can get most of the way there using DAO...

Set tdf = Backdb.CreateTableDef("MasterTable")
tdf.Fields.Append .CreateField("CompanyOpen", dbInteger)
Backdb.TableDefs.Append tdf

This will not set the default value to 0, but will do the rest.

That would create the table, but in this case the table already exists,
as I understand it. You're right, though: you can do the whole job
using DAO objects, if BobV doesn't want to use the ADO method I posted.

Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set tdf = Backdb.TableDefs("C")
Set fld = tdf.CreateField("CompanyOpen", dbInteger)
' or
dbLong
fld.DefaultValue = "0"
tdf.Fields.Append fld

Set fld = Nothing
Set tdf = Nothing

 

[BeWyched]

Hi Dirk

For some reason I can't pick up the thread for the ALTER TABLE issue.

Anyway, thanks for your comments about my entry. I was aware of the issue
but had, inadvertently, picked up the wrong code snippet!

Also, I was not aware that the default value could be set using DAO so many
thanks for that.

Rgds

 

[Dirk Goldgar]

Hi Dirk

For some reason I can't pick up the thread for the ALTER TABLE issue.

Huh. I wonder if MS has been working on cleaning up threads that have
been contaminated by the spammer who's been so active in the last couple
of days, and got a little overzealous.

Anyway, thanks for your comments about my entry. I was aware of the
issue but had, inadvertently, picked up the wrong code snippet!

Also, I was not aware that the default value could be set using DAO
so many thanks for that.

You're welcome.