Add column to existing Access2000 table

[James]

Hi All.

I've been trying to add a column to an existing Access2000 table. I know
how to do this if I'm building the table from scratch, but doing to an existing
table appears to be another trick. Does anyone know how this can be done?

Thanks

James

[Michael Culley]

Best do it with an SQL statement

ALTER TABLE MyTable APPEND COLUMN MyCol (Text 25)

I'm probably way off base, but the syntax should be pretty close to this,
just look in help under ALTER.

--
Michael Culley
www.vbdotcom.com


"James" <davidsonj@tni.ca> wrote in message
news:3bfd3452$1@147.208.176.211...
>
> Hi All.
>
> I've been trying to add a column to an existing Access2000 table. I know
> how to do this if I'm building the table from scratch, but doing to an
existing
> table appears to be another trick. Does anyone know how this can be done?
>
> Thanks
>
> James

[Paul Clement]

On 22 Nov 2001 17:22:26 GMT, "James" <davidsonj@tni.ca> wrote:

¤
¤ Hi All.
¤
¤ I've been trying to add a column to an existing Access2000 table. I know
¤ how to do this if I'm building the table from scratch, but doing to an existing
¤ table appears to be another trick. Does anyone know how this can be done?

Are you using ADO or DAO?


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[James]

Hi Paul

I'm using ADO 2.5 if that helps

James

[Paul Clement]

On 26 Nov 2001 19:41:29 GMT, "James" <davidsonj@tni.ca> wrote:

¤
¤ Hi Paul
¤
¤ I'm using ADO 2.5 if that helps
¤
¤ James

You can use ADOX for this (Microsoft ADO Ext 2.x for DDL and Security):

Function AddNewFields()

Dim tbl As ADOX.Table
Dim col As New ADOX.Column
Dim cat As New ADOX.Catalog
Dim cnn As New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=d:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;"

Set cat.ActiveConnection = cnn
Set tbl = cat.Tables("NewTable")
col.NAME = "Name"
col.Type = adVarWChar
col.DefinedSize = 20
col.Attributes = adColNullable
tbl.Columns.Append col
Set col = New ADOX.Column
col.NAME = "Age"
col.Type = adInteger
col.Attributes = adColNullable
tbl.Columns.Append col
cat.Tables.Append tbl

Set cat = Nothing
cnn.Close
Set cnn = Nothing

End Function


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[Michael Culley]

Paul,

When using ADOX the results should be checked fairly carefully because you
don't always get what you expect. I do use it but find it does not work
correctly for some data types.

--
Michael Culley
www.vbdotcom.com


"Paul Clement" <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
news:ir870ussdt40skfs7lf9tpak4d23nt7nlu@4ax.com...
> On 26 Nov 2001 19:41:29 GMT, "James" <davidsonj@tni.ca> wrote:
>
> ¤
> ¤ Hi Paul
> ¤
> ¤ I'm using ADO 2.5 if that helps
> ¤
> ¤ James
>
> You can use ADOX for this (Microsoft ADO Ext 2.x for DDL and Security):
>
> Function AddNewFields()
>
> Dim tbl As ADOX.Table
> Dim col As New ADOX.Column
> Dim cat As New ADOX.Catalog
> Dim cnn As New ADODB.Connection
>
> cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=d:\My Documents\db1.mdb;" & _
> "Jet OLEDB:Engine Type=4;"
>
> Set cat.ActiveConnection = cnn
> Set tbl = cat.Tables("NewTable")
> col.NAME = "Name"
> col.Type = adVarWChar
> col.DefinedSize = 20
> col.Attributes = adColNullable
> tbl.Columns.Append col
> Set col = New ADOX.Column
> col.NAME = "Age"
> col.Type = adInteger
> col.Attributes = adColNullable
> tbl.Columns.Append col
> cat.Tables.Append tbl
>
> Set cat = Nothing
> cnn.Close
> Set cnn = Nothing
>
> End Function
>
>
> Paul ~~~ pclement@ameritech.net
> Microsoft MVP (Visual Basic)