Alter Decimal Places property in table with SQL

[Guest]

I am trying to change the Decimal places of a table field from 2 to 4 using
the following code only to receive a syntax error. Can anybody tell what I
have done wrong? Thanks
Eddy


strSQL = "ALTER TABLE tbl2 ALTER COLUMN Factor Decimal Places (4)"

 

[Guest]

Hi

I assume that the column you are looking to change is called 'factor'. if so
try:

strSQL = "ALTER TABLE tbl2 ALTER COLUMN Factor DECIMAL(6,4)"

The '6' defines the number of integers and the 4 the number of decimal
places. So 6,4 allows values from 999,999.9999 to - 999,999.9999. Change to
'6' to suit.

Good luck.

BW

 

[Guest]

Thanks for the response. You are correct in your assumption regarding the
field name. I tried your suggestion and I still get the "Syntax Error in
ALTER TABLE Statement" error when I run the code. The field in question,
"Factor" is a data type of Number with a field size of Double and Format is
Fixed if that sheds light on the situation.

Thanks

 

[Guest]

The line of code you posted is only assigning a string value to a variable
named strSQL. Is strSQL Dimmed as String? What line of code actually uses
the value of strSQL to execute?

 

[Guest]

Hi,
You are of course correct, I left it out the the post. The complete code
follows:

Dim DatabasePath As String
Dim strSQL As String
Dim dbBackend As DAO.Database
DatabasePath = "C:\Program Files\Backend\BEData.mdb"
Set dbBackend = OpenDatabase(DatabasePath)

strSQL = "ALTER TABLE tbl2 ALTER COLUMN Factor DECIMAL(6,4)"
dbBackend.Execute strSQL

dbBackend.Close

 

[Guest]

Sorry, Eddy, I don't see the problem. I don't have much experience with data
definition SQL.

 

[Guest]

Thanks for taking a look at it Dave.

Sorry, Eddy, I don't see the problem. I don't have much experience with data
definition SQL.

 

[Douglas J. Steele]

What version of Access are you using, Eddy? If it's Access 97, there is no
Decimal type.

 

[onedaywhen]

Dim DatabasePath As String
Dim strSQL As String
Dim dbBackend As DAO.Database
DatabasePath = "C:\Program Files\Backend\BEData.mdb"
Set dbBackend = OpenDatabase(DatabasePath)

strSQL = "ALTER TABLE tbl2 ALTER COLUMN FactorDECIMAL(6,4)"
dbBackend.Execute strSQL

The ALTER TABLE syntax is only supported in Jet 4.0/ACE while in
ANSI-92 Query Mode. Try an ADO connection e.g.

CurrentProject.Connection.Execute _
"ALTER TABLE tbl2 ALTER COLUMN Factor DECIMAL(6,4);"

Note BeWyched made a misstatement about the value range. A column
defined as DECIMAL(6,4) will have a value range from 99.9999 to
-99.9999. The first number (precision=6) is the total number of digits
and the second number (scale=4) is the number of decimal digits.

Jamie.

--