Decimal Field Type Using SQL

[Leigh46137]

I have a runtime Access database that I have developed using Access 2007. I need to change my price field type from Currency to Decimal.
I have to have 2 decimal places and I cannot use the Currency type because I have to export this table to a text file; the program I have to import it into chokes on the dollar signs.

In my code I am executing this SQL statement:

ALTER TABLE [History] ALTER COLUMN [Price] Decimal (5,2);

When I run the code, Access tells me that there is a syntax error in my SQL. (Everthing I've found on the internet in the last 3 hours of research says to use the syntax I'm using)

Can anyone tell me what the syntax ought to be?

Thanks!

 

[spikepl]

http://www.w3schools.com/sql/sql_datatypes.asp

 

[jdraw]

I did a little googling and found an example that said you must use ADO with Decimal.
Here's a routine that should work.

' Procedure : AlterTable
' Author : Jack
' Created : 7/10/2011
' Purpose : To alter a table with a field of Long Integer datatype to Decimal.
' Must use ADO based on a lot of Google work. n my example I created a table with 3 fields, ID Long Integer, a text field and another field.
' This example shows how to Alter the Long Integer field [ID] to decimal.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Private Sub AlterTable()
Dim cnn As New ADODB.Connection
On Error GoTo AlterTable_Error

Set cnn = CurrentProject.Connection
cnn.Execute ("Alter TABLE [TEST] ALTER COLUMN [ID] Decimal(7,3)")

Set cnn = Nothing

On Error GoTo 0
Exit Sub

AlterTable_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure AlterTable of Module AWF_Related"

End Sub

 

[Leigh46137]

Thanks, but I found that one too. Here's what happened when I tried it:

cnn As New ADODB.Connection

Compile error: User-defined type not defined

 

[spikepl]

You just need to set the reference to ADO library
CodeWindow->Tools->References

I just tried and Alter TABLE [TEST] ALTER COLUMN [ID] Decimal(7,3) worked just fine

 

[spikepl]

"The decimal data type isn't supported in the default Jet 4.0 mdb file. You have to use the SQL Server compatibility syntax (ANSI 92) setting to use the decimal data type in the SQL Window.
Click on the menu, Tools > Options. Click on the Tables/Query tab. Mark the check box for "This database" in the SQL Server compatibility syntax (ANSI 92) section. This mode will affect the entire db, including queries with wildcards, so you may want to try this on a copy of your db.
"

from http://stackoverflow.com/questions/180929/how-do-i-create-a-decimal-field-in-access-with-alter-table

 

[Leigh46137]

I'm working with an Access Runtime database. I had to split it in order to be able to make modifications to the user interface. I cannot modify the backend database directly - I can only do it through code. (If I could do it directly, this wouldn't be an issue ) As far as I know, I cannot add references or change settings like that in the back end database.

Anyway, I finally found a work-around. Not pretty or proper, but it gets the job done! Since the field is in a temporary table whose sole purpose in life is to export records to a text file, I can get away with storing the data in a text field. The numbers and decimal point is there, the dollar sign isn't and the program it geting imported into appears to be happy.

Thanks fo all your suggestions and help!

 

[Terry Lawson]

Thanks guys

Use the sub, it works perfectly

Terry Lawson