Updating data to a new MS Access database

[Stacy Eleuterius]

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]

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:

Connection conn;
String createTable = "CREATE TABLE EMPLOYEES " +
        "EMP_SSN   INTEGER PRIMARY KEY," +
        "EMP_NAME  VARCHAR(20)";
.
.
.
Statement stmt = conn.createStatement();
stmt.executeUpdate(createTable);

------------------
Brett Spell
Author, Professional Java Programming

[David O'Meara]

It might be a transaction problem, after you get the connection make sure it is set to autocommit

conn.setAutoCommit(true);

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]

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]

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:

      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      String url = "jdbc [img]http://www.javaranch.com/ubb/redface.gif[/img]dbc:dbpractice";
      Connection con = DriverManager.getConnection(url, "", "");
      con.setAutoCommit(true);
      String createTable = "CREATE TABLE abcd (Date INTEGER PRIMARY KEY, Odometer INTEGER)";
      //String createTable = "CREATE TABLE EMPLOYEES " +        "EMP_SSN   INTEGER PRIMARY KEY," +        "EMP_NAME  VARCHAR(20)";
      Statement stmt = con.createStatement();
      stmt.executeUpdate(createTable);
      con.close();

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]

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.