Using the DECIMAL type in a CREATE TABLE statement

[Nathan Sokalski]

I am writing a Java program which involves creating a table in a Microsoft
Access database. I am having trouble creating the field which is of type
DECIMAL. I recieve the following error from Java:

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax
error in field definition.

I tried entering the field as each of the following:

CREATE TABLE Employee(Salary DECIMAL)
CREATE TABLE Employee(Salary DECIMAL(10,2))
CREATE TABLE Employee(Salary Decimal)
CREATE TABLE Employee(Salary Decimal(10,2))

If anyone has any knowledge as to what the correct syntax is, I would
appreciate your help. I would also like to know of any reference sites that
show the syntax for the types when using CREATE TABLE. Thank You.

 

[Antoine]

Seems to be a problem of compatibility driver. Java is not fiting very well
with odbc.
Look at the specification of the driver you use (level 1, 2 or 3 ?) to see
if it can do this (see search engines).
Also try the same query within access, to see if it's working (may be a pb
in the query itself or a pb of creating this table in access, without odbc
involved).
more over, I don't use decimal type in access. never done. I don't trust it.
may be use another type ?
if you create the table "on the fly" by program : either, you use a
pre-build table that you don't delete (just suppress the records), or you
use the real type (as a temporary table, it may fit).
hope this help.
Antoine

 

[Lars-Inge Tønnessen]

Sorry, I don't have Access. But this works with the MS SQL Server and the MS
JDBC driver.

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection conn =
DriverManager.getConnection("jdbc:microsoft:sqlserver://localhost:1433;User=
user;Password=password");

String query = "create table SUPPLIES "+
"(SUP_ID INTEGER, "+
"SUP_NAME VARCHAR(40), "+
"DOUB DECIMAL(12,4))";

Statement sts = conn.createStatement();
sts.executeUpdate( query );



Lars-Inge Tønnessen
www.larsinge.com

 

[Nathan Sokalski]

I'm not suprised about that, because Access doesn't have EXACTLY the same
syntax as SQL; Microsoft, as always, decided to make their own adjustments.
However, it is supposedly close enough that most of the stuff can still be
done. And since I am forced to use Access, I need to figure out how to do it
with Access. But all information could end up being of use at some time or
another, so thanks anyway.

 

[Lars-Inge Tønnessen]

I found these, maybe they could be of any help:

http://msdn.microsoft.com/library/d...l/acrefComparingAccesstSQLServerDatatypes.asp

http://msdn.microsoft.com/library/d...wFieldDataTypesAvailableInMicrosoftAccess.asp

Maybe it's the "Currency" you want?
http://members.lycos.co.uk/zhanshan2002/asp/dbcreatesql.html

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replhet_4nn2.asp



(The free MSDE or the MS SQL Server Personal edition is the way to go
=) )

Lars-Inge Tønnessen
www.larsinge.com