Change Decimal Precision and Scale Programmatically?

[JohnJohn]

Hello. I have an application written in Visual Basic 6.0 that uses a
Microsoft Access 2000 database. The application creates all the tables and
columns automatically using ADOX.

I have a particular column that is a number, specifically a decimal with
precision of 8 and scale of 4. I need to change the precision to 10 and the
scale to 6.

When I use ADOX to do this similar to the following:

Dim adox_Table as ADOX.Table

Set adox_Table = adox_Catalog.Tables("MyTable")
adox_Table.Columns.Item("MyDecimalColumn").Precision = 10
adox_Table.Columns.Item("MyDecimalColumn").Scale = 6

...it throws an error, "Method is not supported by this provider."

I tried doing it with ADO as well. No luck there. I also tried using an
SQL statement to do it similar to the following:

ALTER TABLE MyTable
ALTER COLUMN MyColumn DECIMAL (10, 6)

...but that throws an error "Syntax error in ALTER TABLE Statement". I also
can't find a single example anywhere on the web or in Microsoft's
documentation on how to do this with a decimal column.

Anyone have any ideas (short of using the Access GUI to change it
manually...it MUST be done programmatically)? Perhaps I'm using the improper
ALTER TABLE syntax?

Thanks much!
John

[Ralph]

Post by JohnJohn
Hello. I have an application written in Visual Basic 6.0 that uses a
Microsoft Access 2000 database. The application creates all the tables and
columns automatically using ADOX.
I have a particular column that is a number, specifically a decimal with
precision of 8 and scale of 4. I need to change the precision to 10 and the
scale to 6.
Dim adox_Table as ADOX.Table
Set adox_Table = adox_Catalog.Tables("MyTable")
adox_Table.Columns.Item("MyDecimalColumn").Precision = 10
adox_Table.Columns.Item("MyDecimalColumn").Scale = 6
...it throws an error, "Method is not supported by this provider."
I tried doing it with ADO as well. No luck there. I also tried using an
ALTER TABLE MyTable
ALTER COLUMN MyColumn DECIMAL (10, 6)
...but that throws an error "Syntax error in ALTER TABLE Statement". I also
can't find a single example anywhere on the web or in Microsoft's
documentation on how to do this with a decimal column.
Anyone have any ideas (short of using the Access GUI to change it
manually...it MUST be done programmatically)? Perhaps I'm using the improper
ALTER TABLE syntax?
Thanks much!
John

Some column properties don't exist until you have appended the column and
set the catalog, then you can set the properties (which is actually done
thru the catalog object).
... take a look at this...

http://support.microsoft.com/?kbid=275252

-ralph