Using ADOX to Alter Column Definition

[JohnJohn]

Hello.

I'm trying to change the NumericScale property for a column but am receiving
errors. Here's the code I'm using:

Dim m_adox_Catalog As ADOX.Catalog
Dim adox_Table As ADOX.Table

Set m_adox_Catalog = New ADOX.Catalog
Set adox_Table = m_adox_Catalog.Table(MyTable)
'error occurs on following line:
adox_Table.Columns.Item(FldName_sn_RateCap).NumericScale = 4

The last line above generates the following error:

Run-time error '-2147217837 (80040e53)'
Method is not supported by this provider.

This only happens when I attempt to ALTER the column. The above line would
work perfectly fine when I create the column. But I need the capability to
alter the column. Also, I tried using the following syntax that generates
the exact same error:

adox_Table.Columns(FldName_sn_RateCap).NumericScale = 4

If there's any alternative you know of to doing it this way, please let me
know how to do it (with specific syntax).

Thank you!
John

 

[Dirk Goldgar]

Hello.

I'm trying to change the NumericScale property for a column but am
receiving errors. Here's the code I'm using:

Dim m_adox_Catalog As ADOX.Catalog
Dim adox_Table As ADOX.Table

Set m_adox_Catalog = New ADOX.Catalog
Set adox_Table = m_adox_Catalog.Table(MyTable)
'error occurs on following line:
adox_Table.Columns.Item(FldName_sn_RateCap).NumericScale = 4

The last line above generates the following error:

Run-time error '-2147217837 (80040e53)'
Method is not supported by this provider.

This only happens when I attempt to ALTER the column. The above line
would work perfectly fine when I create the column. But I need the
capability to alter the column. Also, I tried using the following
syntax that generates the exact same error:

adox_Table.Columns(FldName_sn_RateCap).NumericScale = 4

If there's any alternative you know of to doing it this way, please
let me know how to do it (with specific syntax).

Thank you!
John

According to the ADOX help file:

<quote>
NumericScale is read-only for Column objects already appended to a
collection.
</quote>

I imagine you could do this as a four-step process:

1. Create a new field with the properties you want.
2. Execute an update query to copy the contents of the old field to the
new field
3. Drop the old field.
4. Rename the new field to the old field's name.

 

[JohnJohn]

Thanks Dirk. I actually already tried that as well and ADOX wouldn't let me
append ANY fields to the table. The only thing I've found so far that works
for me is the following:

Create a new blank table with the proper column format.
Copy allllll the data from the old table into the new.
Delete the old table.
Rename the new table.

It sure is messy, but at least it works.

Thanks,
John

 

[Dirk Goldgar]

Thanks Dirk. I actually already tried that as well and ADOX wouldn't
let me append ANY fields to the table. The only thing I've found so
far that works for me is the following:

Create a new blank table with the proper column format.
Copy allllll the data from the old table into the new.
Delete the old table.
Rename the new table.

It sure is messy, but at least it works.

This seems to work, for me:

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

Set con = CurrentProject.Connection

With con
.Execute "ALTER TABLE tblData ADD COLUMN DecimalFieldCOPY
DECIMAL(18,4)"
.Execute "UPDATE tblData SET DecimalFieldCOPY = DecimalField"
.Execute "ALTER TABLE tblData DROP COLUMN DecimalField"
End With

Set cat.ActiveConnection = con
Set tbl = cat.Tables("tblData")
Set col = tbl.Columns("DecimalFieldCOPY")
col.Name = "DecimalField"

Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
Set con = Nothing