Access SQL: Create Table: Decimal Data Type? 1

[jasonp45]

I want to use Access SQL to create a table. It works fine *EXCEPT* I can't seem to create a decimal number type!

I'm trying to do this:
CREATE TABLE [TEST] ([ID] Long NOT NULL, [MyTextField] Text (2), [MyDecimalField] Decimal(10,6))

Everything works except the Decimal declaration. I've tried changing the word 'Decimal' to 'Double', 'Numeric', etc. but no dice. I need that field to have a Precision of 10 and a Scale of 6. Can someone please tell me the proper syntax? I've searched and searched but can't find exactly what I'm looking for.

Thanks!

 

[GHolden]

Hi, Check out this link...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

You will see the examples...

CREATE TABLE tblCodeNumericDataTypes (
   Field1_TINYINT TINYINT,
   Field2_DECIMAL DECIMAL,
   Field3_DEC DECIMAL,
   Field4_DPRECISION DOUBLE PRECISION)

CREATE TABLE tblDecimalDataTypes (
   DefaultType DECIMAL,
   SpecificType DECIMAL(10,5))

However, there is the note...

The data types listed in the previous SQL statement can be executed only through the Jet OLE DB provider and ADO. They will result in an error message if used through the Access SQL View user interface. Also note that if you create a field with the NUMERIC data type through the Access SQL View, it will result in a DOUBLE data type when you view the table design through the Access user interface. But if you create the NUMERIC data type through the Jet OLE DB provider and ADO, it will result in a data type of DECIMAL when you view the table design through the Access user interface.



There are two ways to write error-free programs; only the third one works.

 

[GHolden]

Sorry that was only possibly half an answer.

Example using ADO

Private Sub CreateTable()
Dim cnn As New ADODB.Connection

Set cnn = CurrentProject.Connection
cnn.Execute ("CREATE TABLE [TEST] ([ID] Long NOT NULL, [MyTextField] Text (2), [MyDecimalField] Decimal(10,6))")

Set cnn = Nothing

End Sub

There are two ways to write error-free programs; only the third one works.

 

[jasonp45]

Thanks! Yeah I figured out that it works through ADO, but I was hoping there was a way to do it through the Access SQL interface.