Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Updating data to a new MS Access database

 
Stacy Eleuterius
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to add data to a MS Access database with an application that I am creating. I have created the database and set up the ODBC Source Administrator. Nothing has been done to the database (I.E., no columns have been added).
I am able to open and close the connection to the database in my application. What do I have to do to add 4-5 columns, and the subsequent data to these columns? Can I do everything from the Java side, or do I have to set up the columns in Access?
I apologize for asking such simple questions, however I am having difficulties finding information on these topics. It seems that there is a lot of literature pertaining to pulling data from a database, not sending it!!!
 
Brett Spell
Ranch Hand
Posts: 107
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Stacy,
I'm not certain that I know what you mean when you say that "no columns have been added" to the database, but I'm assuming that you mean that you wish to use JDBC to define tables in the database or to add columns to existing tables. In either case, the answer is that JDBC does allow you to issue DDL statements like CREATE TABLE, and you'll need to use either the execute() or executeUpdate() method in Statement. I'd recommend executeUpdate() since it's a little easier to use, and an example of how to do so appears below:

------------------
Brett Spell
Author, Professional Java Programming
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It might be a transaction problem, after you get the connection make sure it is set to autocommit

I think the default depends on the database and I don't think Access defaults to true. (at least it didnt yesterday )
Either do that or try conn.commit() just before ypu release the connection.
 
Thomas Paul
mister krabs
Ranch Hand
Posts: 13974
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
According to the Java API: "By default, new connections are in auto-commit mode." So if Access does not default to auto-commit then that is an error in the driver you are using and I would look to use a different driver.
 
Stacy Eleuterius
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for your help. I am able to run the program with the errors included belowthe code, and the table is not created. I used the "JdbcOdbcDriver", just because I thought it was the one that I needed. Anymore suggestions?
My code looks like this:

Error:
C:\JBuilder4\jdk1.3\bin\javaw -classpath "C:\SSEJava\DBPractice\classes;C:\JBuilder4\libclasses;C:\JBuilder4\jdk1.3\demo\jfc\Java2D\Java2Demo.jar;C:\JBuilder4\jdk1.3\jre\lib\i18n.jar;C:\JBuilder4\jdk1.3\jre\lib\jaws.jar;C :\JBuilder4\jdk1.3\jre\lib\rt.jar;C:\JBuilder4\jdk1.3\jre\lib\sunrsasign.jar;C:\JBuilder4\jdk1.3\lib\dt.jar;C:\JBuilder4\jdk1.3\lib\tools.jar" dbpractice.Application1
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error in field definition.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:2494)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:314)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(JdbcOdbcStatement.java:264)
at dbpractice.Frame1.jbInit(Frame1.java:109)
at dbpractice.Frame1.<init>(Frame1.java:41)
at dbpractice.Application1.<init>(Application1.java:11)
at dbpractice.Application1.main(Application1.java:40)
 
Stacy Eleuterius
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please disregard that last post. My problem arose in the CREATE TABLE statement:
String createTable = "CREATE TABLE Table2 (Id LONG, MyText TEXT)";

Thanks for everyone's help.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic