Unable to actually update mdb

[Hans Mikkelsen]

Goal: Update a KOMM-field in a table (which i shall call here Table1).

I'm unable to update it, and I can't figure out why. When I do the executeUpdate-statement, it seems to go through (as when I later print out the komm-values it comes out correctly with the new values). However, it does not seem to be saved in the access-DB (.mdb) that I work with? Is there any function that I'm missing? I've tried both with and without disabling the AutoCommit. I based it on an example-tutorial.

package accessTest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class accessTest {

    public static void main(String[] args) {

        // variables
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        // Step 1: Loading or 
        // registering Oracle JDBC driver class
        try {

            Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
        }
        catch(ClassNotFoundException cnfex) {

            System.out.println("Problem in loading or "
                    + "registering MS Access JDBC driver");
            cnfex.printStackTrace();
        }

        // Step 2: Opening database connection
        try {

            String msAccDB = "C:/Users/**/Database.mdb";
            String dbURL = "jdbc:ucanaccess://"
                    + msAccDB;
            System.out.println("DB successfully accessed... perhaps");

            // Step 2.A: Create and 
            // get connection using DriverManager class
            connection = DriverManager.getConnection(dbURL); 

            // Step 2.B: Creating JDBC Statement 
            statement = connection.createStatement();
            Statement st1 = connection.createStatement();

            // Step 2.C: Executing SQL
            Statement updateKomm = connection.createStatement();
            connection.setAutoCommit(false);
            updateKomm.executeUpdate("UPDATE Table1 SET Table1.KOMM = 5000;");

            // Step 2.D: Seeing the new Komm-numbers:
            resultSet = statement.executeQuery("SELECT * FROM Table1;");
            int kommCounter = 0;
            while(resultSet.next()) {
                System.out.println(resultSet.getInt(34));
                System.out.println(kommCounter);
                kommCounter++;
            }
            connection.commit();

        }
        catch(SQLException sqlex){
            sqlex.printStackTrace();
        }
        finally {
            // Step 3: Closing database connection
            try {
                if(null != connection) {
                    // cleanup resources, once after processing
                    statement.close();

                    // and then finally close connection
                    connection.close();
                }
            }
            catch (SQLException sqlex) {
                sqlex.printStackTrace();
            }
        }
        System.out.println("The End");
    }
}

I'd be happy to give more details if that is required. Thanks in advance,
Hansi

[Gord Thompson]

Do you have the database open in Access while you run your Java code? If so, then you may need to close and re-open Access in order to see the changes there.

[Hans Mikkelsen]

Thank you very much for answering. I've tried running the code both with and without having the database open, re-opening the DB does not help. I also tried to use a select query and Sysout to print out the komm-values both before and after the update-query, and I can see them being changed, but it does not seem to be "saved" after the code is run. So if I run the code twice, it'll print first the old KOMM-numbers and then the old KOMM-numbers twice.

Is there perhaps a .save()-function or something similar that I need to have?

[Gord Thompson]

With the default setAutoCommit(true) you don't need to do anything explicit to persist the changes to the database. With setAutoCommit(false) you do need to remember to commit(), but that's all.

[Hans Mikkelsen]

I've now tried with both having no setAutoCommit (so it's the default?), setAutoCommit(false) with commit(), and now with setAutoCommit(true). Neither gives me any "permanent" changes, which I just can't understand. I am using the setAutoCommit on the connection-object, could that be wrong?

Can closing connections have any impact on this? Maybe how I access the database? I use 'DriverManager.getConnection(dbURL)' for the connection, and then creating the update-statement from that connection.

[Gord Thompson]

Immediately after Step 2.A (getting the connection), try this:

String dbFileVersion = ((net.ucanaccess.jdbc.UcanaccessConnection) connection)
        .getDbIO().getFileFormat().toString();
System.out.printf("db file version: %s%n", dbFileVersion);

What does it report?

[Hans Mikkelsen]

Thank you very much for follwing up on this.

So I copy/pasted the code, added a try/catch (Eclipse suggested), and it now reports "db file version: V2000 [VERSION_4]". Does that make any sense?

With the database I work with currently I get some Warnings about inconsistentcy/errors in metadata, but it assures me that all wil work fine. There's some mis-match between table's row count in metadata and what have been found and loaded by UCanAccess (the number that UCanAccess finds seems to be the correct one when I manually check).

Could my Referenced Libraries have any impact on this? I have commons-lang-2.6, commons-logging-1.1.3, hsqldb-2.3.1, ucanaccess-4.0.4, jackcess-2.1.11.

[Gord Thompson]

"db file version: V2000 [VERSION_4]". Does that make any sense?

 
Yes. It means that the database file is compatible with Access_2000. I wanted to make sure that it wasn't an Access_97 .mdb file because UCanAccess only supports them as read-only.

I get some Warnings about inconsistentcy/errors in metadata

 
Those warnings are harmless in and of themselves, but they do indicate some minor inconsistencies in the database. You might consider open it in Access and doing a "Compact and Repair Database" operation on it.

Could my Referenced Libraries have any impact on this? I have commons-lang-2.6, commons-logging-1.1.3, hsqldb-2.3.1, ucanaccess-4.0.4, jackcess-2.1.11.

 
Those are the correct versions for the latest release of UCanAccess.

[Hans Mikkelsen]

Then I have no more suggestions about what could be wrong, it's very odd that this does not work for me. I can't help but think that I've done some small but grave error somewhere, but how that would work i do not know.

[Gord Thompson]

You could try making the file read-only at the OS level and then see if your code fails when it attempts to update the database. If it doesn't fail then you'll know that the code is not trying to update that particular file (and perhaps you may be looking at the wrong one).

[Hans Mikkelsen]

Sorry for the late reply. Making it read-only (Right click => File properties => check for Read Only Attribute) changed nothing, it did not fail. That in itself is surely suspicious, but I don't know what I am doing wrong. I am however very certain that I am not looking at the wrong file, because I can change the contents manually and see that the system.out-lines in my program gives me the changed values.

I hope this is not rude, but I ended up doing it (and other tasks) with VBA instead of Java, so my problem is solved from my point of view. As fun as it would be to do it in Java, it is far too big of a project to rewrite it to Java now. However, if you're curious about what went wrong I can still test things out if you want to.

What also might or might not belong to this story is that the database(s) I worked with had some missing references (which VBA complained about), but even with that fix there were no changes. I cannot guarantee for the state of the Access-bases, there may be other errors that perhaps causes it not to update.

[Gord Thompson]

Not rude at all. Just getting your job done.

If you like you could

• make a copy of the .mdb file,
• remove or obfuscate any sensitive information,
• verify that your test still fails to update the table in that copy of the .mdb, and if so,
• post a download link where I can retrieve a copy and try to recreate the issue here.

 
The missing VBA references would not cause the error (as UCanAccess has nothing to do with them), but as you say, there may be some other issues with the database file.

[Hans Mikkelsen]

The database is not mine, so I'm afraid I can't send it.

However, today I did what I probably should've done a long time ago, I created a new database (Access-program => new database), set in the required fields, tables and some test-data and now everything works flawlessly. I tried it with both the 2000-format and the 2002-2003-format, both works. I honestly did not imagine that could be the case, I was so certain the error was on my side.

So I think I can conclude with that "something" within the databases (basically all of them, I suspect) are rotten?

[Gord Thompson]

If that's the case then I'm surprised that a Compact and Repair Database didn't fix it. Anyway, it's good to hear that you've got it sorted out.