File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Insert into MS Access with PreparedStatement 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 "Insert into MS Access with PreparedStatement" Watch "Insert into MS Access with PreparedStatement" New topic

Insert into MS Access with PreparedStatement

Wb Webster

Joined: Aug 27, 2003
Posts: 1
I am having a problem inserting into MS Access with a PreparedStatement. I am not getting any exception thrown or error message but when I check the database, the data that should be inserted isn't there. Here's the code:
public String load(){
String sql = "Select Distinct Phone from [Employee Data] WHERE Phone <> ''";
String psSql = "Insert into tblPhone (id, Phone, Type) VALUES "
+ "(?, ?, 1)";
String out = "";
Connection con = db.getNewConnection();
StringUtil su = new StringUtil();
PreparedStatement ps = con.prepareStatement(psSql);
ResultSet rs = db.openResultSet(sql);
int i = 0;
int j = 0;
System.out.println("about to do loop");
String thisPh = rs.getString("Phone");
ps.setInt(1, ++i);
System.out.println("set int 1");
ps.setString(2, su.stripNonDigits(thisPh));
System.out.println("set string 2");
System.out.println("executing this update ...");
j += ps.executeUpdate();
System.out.println("current insert is:" + su.stripNonDigits(thisPh));
System.out.println("Looped through " + i + " records.");
return "Total of " + j + " records inserted";
catch(Exception e)
return out;
The script runs without any problems returning the following message at the end:
Looped through 273 records.
Total of 273 records inserted
However, when I check the database, there are no records inserted.
I tried the insert without a PreparedStatement and it works. Any suggestions?
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

just for starters, you might want to have a con.commit() to ensure changes are being committed to the database.
if that isn't the problem, you should make sure that you have the most up to date MSAccess ODBC driver on your machine. Some of the older ones are pretty buggy.
It is sorta covered in the JavaRanch Style Guide.
subject: Insert into MS Access with PreparedStatement
It's not a secret anymore!