wood burning stoves
The moose likes JDBC and Relational Databases and the fly likes Updating data to a new MS Access database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Updating data to a new MS Access database" Watch "Updating data to a new MS Access database" New topic

Updating data to a new MS Access database

Stacy Eleuterius

Joined: Dec 07, 2000
Posts: 7
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

Joined: Feb 24, 2001
Posts: 107
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

Joined: Mar 06, 2001
Posts: 13459

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

Joined: May 05, 2000
Posts: 13974
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.

Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
Stacy Eleuterius

Joined: Dec 07, 2000
Posts: 7
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:

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

Joined: Dec 07, 2000
Posts: 7
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.
I agree. Here's the link: http://aspose.com/file-tools
subject: Updating data to a new MS Access database
jQuery in Action, 3rd edition