aspose file tools*
The moose likes JDBC and the fly likes Syntax error for Access Database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Syntax error for Access Database" Watch "Syntax error for Access Database" New topic
Author

Syntax error for Access Database

Clara Hall
Greenhorn

Joined: Nov 24, 2001
Posts: 16
Hi,
Following is my create table string
String qry1 = "CREATE TABLE ResearchTopic "
+ "(TopicID AutoNumber NOT NULL, "
+ "Name VARCHAR(50) NOT NULL, "
+ "Layer INT NOT NULL, "
+ "ParentName VARCHAR(50) NOT NULL, "
+ "PRIMARY KEY(Name, Layer, ParentName), "
+ "UNIQUE(TopicID))";
When I execute the update, it said there is syntax error for field definition.
Following is the error message.
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in
field definition.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
at Database.updateDB(Database.java:55)
at TableCreation.create(TableCreation.java:130)
at TableCreation.main(TableCreation.java:148)
I cannot find the errors in my string. What is wrong? Thank you very much.
Clara
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

The syntax in MSAccess to create a constraint is:
CONSTRAINT name
{PRIMARY KEY (primary1[, primary2 [, ...]]) |
UNIQUE (unique1[, unique2 [, ...]]) |
NOT NULL (notnull1[, notnull2 [, ...]]) |
FOREIGN KEY (ref1[, ref2 [, ...]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, ...]])]}
so this is how I see the constraint clause being in your DDL statement:
CREATE TABLE ResearchTopic
(TopicID integer NOT NULL,
Name VARCHAR(50) NOT NULL,
Layer INT NOT NULL,
ParentName VARCHAR(50) NOT NULL ,
CONSTRAINT ResearchTopic_pk
PRIMARY KEY(Name, Layer, ParentName),
CONSTRAINT ResearchTopic_unique UNIQUE (TopicID) )
integer is bold because I could not create a table using autonumber. It will complain until I change it to another data type? The constraints are correct though.
Clara Hall
Greenhorn

Joined: Nov 24, 2001
Posts: 16
It works now. Thank you so much.
But I have one more question. How to define CASCADE DELETE and CASECADE UPDATE?
I tried to use
ON DELETE CASCADE ON UPDATE CASCADE after the foreign key constrain, but it doesn't work, I also tried some other format which I can imaging, but none of them works.
Thanks.
Clara
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I think you are trying to make MSAccess into a real database. Unfortunately it is not. This may be possible, but still I think you are pushing the limits of Access! If you need these advanced DBMS features, have you thought about moving to a full featured database(MS SQLServer, mySQL, postgres, Oracle)?
Jamie
Clara Hall
Greenhorn

Joined: Nov 24, 2001
Posts: 16
It can be set in the database in its relationship setting, I wander to know how set it through SQL using JDBC. I don't know whether there is any way to do that.
Thank you.
Clara
Alexey Jurchenko
Greenhorn

Joined: Nov 15, 2004
Posts: 1
Originally posted by Jamie Robertson:
I think you are trying to make MSAccess into a real database. Unfortunately it is not. This may be possible, but still I think you are pushing the limits of Access! If you need these advanced DBMS features, have you thought about moving to a full featured database(MS SQLServer, mySQL, postgres, Oracle)?

Jamie


It's very strange - it's possible to set cascade update/delete via "MS Access" main window "Tools/Relations" menu, but it's not possible to set via sql statement even in "Query Design View".
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30938
    
158

Alexey,
Welcome to JavaRanch!

Note that the other posts in this thread are almost three years old.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Syntax error for Access Database