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

 

[Antoine]

it may be a question of syntax, or a question of using odbc driver with
java, or more precisely, using java and access together ("they don't like
much each other").
I have developped softwares with access since 97. I now work with java.
I will rewrite, may be, some of them with java (new way of doing the same
things... with web design and full object programming).
In my search to prepare this, I wondered if I could keep Access as database
(many tables yet working, SQL queries yet written, ... and that won't
change).
It seems that it is very risky to use some odbc driver with Java, and JDBC
drivers (safe and robust with java) for access are expensive (see web search
on this subject).
For my projects, I will use MySQL, since it is know full RDBMS (with
external key feature)...

if you have to work with Access, be very aware of this kind of pb you may
find, involving compatibility between ODBC and JDBC....
Antoine