This week's book giveaway is in the Java 8 forum.
We're giving away four copies of Java 8 in Action and have Raoul-Gabriel Urma, Mario Fusco, and Alan Mycroft on-line!
See this thread for details.
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


Win a copy of Java 8 in Action this week in the Java 8 forum!
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 29274
    
140

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
 
Similar Threads
unable to connect to cloudscape
SQL Server 2000 Database Connection using JDBC
Syntax error in SQL
SQLException on connect
Error in executeQuery() on callableStatement