Add a column to a table using code

[dave]

I need to add a column to a table using code.

The following works, but only for a new table. How should I change this to
alter an existing table?


Dim strConn
Dim Catalog As New ADOX.Catalog
Dim Table As ADOX.Table

Set Catalog = New ADOX.Catalog

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data.mde"
Catalog.ActiveConnection = strConn

Set Table = New ADOX.Table
Table.Name = "tblExistingTable"
Table.Columns.Append "NewColumn", adBoolean
Catalog.Tables.Append Table

 

[Marshall Barton]

I need to add a column to a table using code.

The following works, but only for a new table. How should I change this to
alter an existing table?


Dim strConn
Dim Catalog As New ADOX.Catalog
Dim Table As ADOX.Table

Set Catalog = New ADOX.Catalog

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data.mde"
Catalog.ActiveConnection = strConn

Set Table = New ADOX.Table
Table.Name = "tblExistingTable"
Table.Columns.Append "NewColumn", adBoolean
Catalog.Tables.Append Table

I don't know that ADO stuff, but I'm sure you could Execute
an ALTER TABLE ADD COLUMN query

 

[Douglas J. Steele]

Try:

Dim strConn
Dim oCatalog As New ADOX.Catalog
Dim oColumn As ADOX.Column

Set oCatalog = New ADOX.Catalog

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data.mde"
oCatalog.ActiveConnection = strConn

Set oColumn = New ADOX.Column
With oColumn
.Name = "NewColumn"
.Type = adBoolean
Set .ParentCatalog = oCatalog
End With
oCat.Tables("tblExistingTable").Columns.Append oColumn


Note that, in addition to some syntax changes, I also renamed your Catalog
variable. It's never a good idea to use reserved words as variables.

 

[dave]

Douglas

Thanks - it works perfectly - I have been working to try and get this going
for a couple of days so really appreciate your help

Dave

 

[dave]

Douglas

Sorry one other question - As i have said this works fine, only issue I have
is when is when I set the .Type as asDate, i seems to set the column as
required.

Is there any way to turn this off?

Dave

 

[Douglas J. Steele]

It shouldn't. I thought the default was nullable (since when you're adding a
field to an existing table, it adds it to existing rows, and doesn't have a
value to put in there)

Try changing

With oColumn
.Name = "NewColumn"
.Type = adDate
Set .ParentCatalog = oCatalog
End With

to

With oColumn
.Name = "NewColumn"
.Type = adDate
.Attributes = adColNullable
Set .ParentCatalog = oCatalog
End With

 

[dave]

thnks - I figured that out separately

It shouldn't. I thought the default was nullable (since when you're adding a
field to an existing table, it adds it to existing rows, and doesn't have a
value to put in there)

Try changing

With oColumn
.Name = "NewColumn"
.Type = adDate
Set .ParentCatalog = oCatalog
End With

to

With oColumn
.Name = "NewColumn"
.Type = adDate
.Attributes = adColNullable
Set .ParentCatalog = oCatalog
End With