Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Syntax error for Access Database

 
Clara Hall
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 34681
367
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Alexey,
Welcome to JavaRanch!

Note that the other posts in this thread are almost three years old.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic