Problems with ucanaccess

[Alejandro Barrero]

I started using ucanaccess for MS Access in Java since the jdbc-odbc driver was removed from Java 8, but now I have problems because it doesn't support "ALTER TABLE". I have browsed for an alternative, but I have not been able to find one. Please direct me to an alternative I can use.

[Stefan Evans]

Hi Alejandro,

Sorry to say I don't know of any alternatives given the constraint of using MS Access.

I would get your team to revisit your assumptions and requirements
- do you HAVE to use MS Access. Can it be migrated to another database with better java support?
- do you HAVE to use the Alter Table syntax? Can you provide that functionality in another way, maybe even not from java?
- do you have to use Java8? Go back to the Java 7 and the JdbcOdbcBridge.

Basically what you are after is not supported.
So push back on the requirements and change something.

The most common view from this forum I'm pretty sure would be to get rid of MS Access and use a real database.

[Winston Gutkowski]

Alejandro Barrero wrote:but now I have problems because it doesn't support "ALTER TABLE"

Ooof. And I'm not surprised. I certainly wouldn't trust middleware to alter the structure of my database on the fly.

ALTER is usually an admin function; so maybe you should explain what it is you're trying to do.

Winston

[Alejandro Barrero]

Thank you for your answer. Unfortunately I do have to use ACCESS and I do have to use "ALTER TABLE". I don't have to use Java 8, but I do have to use a DSNless connection and and my code

public Connection getConnection() throws SQLException {
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String fullConnectionString = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ="
            + filePath + ";Uid=;Pwd=;";
    Connection conn = null;
    try {
        Class.forName(driver);
        conn = DriverManager.getConnection(fullConnectionString, "",
                "");
    } catch (ClassNotFoundException cnfe) {
        throw new SQLException("ClassNotFoundException");
    } catch (SQLException e) {
        throw new SQLException("SQLException");
    }
    return conn;
}

Just stopped working and I have tried a lot of alternatives and Java 6 and 7. I am coming to the conclusion that the problem is Widows 10 and i am exploring this possibility too. I am hoping that there are alternatives to UCANACCESS.

[Alejandro Barrero]

Thank you Winston; the reason I want to use "ALTER TABLE' is that I have an ORM program that creates tables for a database and I want to use it witnACCESS.

[Winston Gutkowski]

Alejandro Barrero wrote:Thank you Winston; the reason I want to use "ALTER TABLE' is that I have an ORM program that creates tables for a database and I want to use it witn ACCESS.

OK, but CREATE TABLE and ALTER TABLE are different orders of function. You may well be able to do the first, but not the second - although I'd be leery of allowing either via middleware if I was a database vendor.

Access, being a "desktop" database, has always had limitations; and I fear you may be running into one of them. ORM is not for the faint of heart; nor for "little" databases. However, better you find out now than six months down the road.

Have you tried a "proof-of-concept" exercise with something like MySQL? That seems like a much more likely target candidate for what you want to do, and it's free (and now owned by Oracle); but I still have many concerns with the idea - and if I was your db admin, I'd need a LOT of convincing.

But that's what admins are for.

Winston

[Alejandro Barrero]

I use "CREATE TABLE", "ALTER TABLE" and many others. The ORM program, which is a personal project, works with several database dialects; I have tested it with, MySQL, ORACLE and ACCESS. It was running fine two years ago. At this point I am creating code to reverse engineer the database and I ran into trouble creating a new ACCESS database. At the time I was using jdbc:odbc for ACCESS, but it stopped working; since I am using UCANACCESS successfully with another application I wanted to try it. I am going to keep looking for another access driver; at the end I might just give up on ACCESS.