ADOX column additions

[Bnielsen]

I'm fumbling through a procedure to programically add columns to an existing
table. The purpose of this is to send an updating database that when launched
- updates objects in another database. So far, the only way I can add fields
is using the .append method as follows:

Dim dbUpdateCatalog As ADOX.Catalog
Set dbUpdateCatalog = New ADOX.Catalog


'---Assign connection string to remote connection

dbRemoteConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & strBackEndDB

'---Open remote connection

dbRemoteConnection.Open

'---Associate ADOX catalog with remote connection

dbUpdateCatalog.ActiveConnection = dbRemoteConnection


For num = 0 To (numrows - 1)

With dbUpdateCatalog.Tables(vardata(0, num)).Columns
.Append vardata(1, num), vardata(2, num)
.Refresh
End With

I have had no luck trying to construct new columns using ADOX.Columns. I
would like the ability to set other properties besides type and fieldsize.
There doesn't seem to be a way to modify properties for existing columns
either....HELP!! This ADOX stuff doesn't work the way the Access Developer's
Handbook says it should.

Thanks in advance

Bryan

 

[Brendan Reynolds]

My advice is to forget ADOX and use DAO and/or SQL DDL instead. That said,
though, I was able to create a column and set its default value property
using the following code. Note that I had to append the column to the
table's Columns collection before setting the Default property ...

Public Sub TestADOXCol()

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables("Orders")
Set col = New ADOX.Column
With col
.Name = "MyNewColumn"
.Type = adInteger
End With
tbl.Columns.Append col
tbl.Columns("MyNewColumn").Properties("Default") = 1

End Sub

 

[Ron Weiner]

Why not use DDL for Access too.

Currentdb.Execute "ALTER Table MyTable ADD COLUMN MyNewColumn SMALLINT"

Does the same thing as code below.

Ron W

 

[Ron Weiner]

Jamie

Sheesh gotta' read these things a little closer, I completely missed the
requirement for the Default constraint.

I also thank you for pointing our the not so subtle difference between
CurrentDb.Execute and CurrentProject.Connection.Execute. Heretofore I had
never considered this. I did test this in a up-to-date patched version of
Access 2K with Jet 4.0, and it seems to work properly there too.

Many thanks for the tip. It likely will stop me from repeatedly banging my
head against the desk at some point in the future before I do any serious
permanent damage.

Ron W