When I executed the following in Access:

alter table t1 alter column c1 decimal(8,4);

I got an error message: "Syntax error in ALTER TABLE statement". I got
a similar error when I tried to add a decimal field. I know I can
define a DECIMAL data type via Access GUI interface. But isn't there a
way to do it directly with a SQL statement?

Thanks.

I couldn't find a reference to ALTER TABLE in ADO or DAO

I think you need to use ADOX to add columns and change attributes of a
table.


minjie@excite.com wrote:[color=blue]
> When I executed the following in Access:
>
> alter table t1 alter column c1 decimal(8,4);
>
> I got an error message: "Syntax error in ALTER TABLE statement". I got
> a similar error when I tried to add a decimal field. I know I can
> define a DECIMAL data type via Access GUI interface. But isn't there a
> way to do it directly with a SQL statement?
>
> Thanks.[/color]

from help

"A new Microsoft Jet 4.0 data type
Decimal data types are new to Microsoft Jet 4.0. It is an exact numeric
data type that holds values from -10^28 - 1 through 10^28 - 1. You can
define both precision (1 - 28) and scale (0 - defined precision). The
default precision and scale are 18 and 0, respectively. Supported
synonyms are Dec and Numeric. Data accessed via ODBC that is typed as
Sql_Decimal or Sql_Numeric will now be mapped to the Microsoft Jet
Decimal, instead of Currency. This data type is not supported via Data
Access Objects (DAO). It is supported only with ActiveX Data Objects
(ADO)."

Thus - CurrentProject.Connection.Execute "ALTER TABLE TableName ALTER
COLUMN ColumnName Decimal (28,9)"

You chould be aware that there are sorting problems with this data
type; solvable in some people's minds, not solvable in others; an index
on the field seems to clear up most of these.

Lyle Fairfield wrote:[color=blue]
> from help
>
> "A new Microsoft Jet 4.0 data type
> Decimal data types are new to Microsoft Jet 4.0. It is an exact numeric
> data type that holds values from -10^28 - 1 through 10^28 - 1. You can
> define both precision (1 - 28) and scale (0 - defined precision). The
> default precision and scale are 18 and 0, respectively. Supported
> synonyms are Dec and Numeric. Data accessed via ODBC that is typed as
> Sql_Decimal or Sql_Numeric will now be mapped to the Microsoft Jet
> Decimal, instead of Currency. This data type is not supported via Data
> Access Objects (DAO). It is supported only with ActiveX Data Objects
> (ADO)."
>
> Thus - CurrentProject.Connection.Execute "ALTER TABLE TableName ALTER
> COLUMN ColumnName Decimal (28,9)"
>
> You chould be aware that there are sorting problems with this data
> type; solvable in some people's minds, not solvable in others; an index
> on the field seems to clear up most of these.[/color]

Thanks a lot!