This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC 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


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Updating data to a new MS Access database" Watch "Updating data to a new MS Access database" New topic
Author

Updating data to a new MS Access database

Stacy Eleuterius
Greenhorn

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: 96
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

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
Greenhorn

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:

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

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
 
Similar Threads
JSP/Servlet With MS Excel?
UTF-8 and MS Access
reading from oracle!
data migration query
MS Access/Oracle