Access (.mdb) Database manipulation with jdbc

Question

Background: Working on an application to be run on an Apache server hosted by Network Solutions. Friend/Customer insisted on using an Access Database instead of SQL database.

Current Problem: Wrote a Java test program to make sure I can connect to the database before I dive head first into writing the whole backend. When I run this code on the JVM of the apache server the final product will be hosted:

import java.sql.*;
import java.io.*;
public class test {
    public static void main(String[] args) {
        try {
            Class driverClass = Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            DriverManager.registerDriver((Driver) driverClass.newInstance());

            // set this to a MS Access DB you have on your machine
            String filename = new File(".").getCanonicalPath() + "/ITEMS.mdb";


            String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
            database+= filename.trim(); // add on to the end 
            // now we can get the connection from the DriverManager

            System.out.println(database);

            Connection conn = DriverManager.getConnection( database );
        } catch (Exception e) {
            System.out.println("Error: " + e.getMessage() + " " + e.getLocalizedMessage());
            e.printStackTrace();
        }
    }
}

I get a null pointer exception on the line Connection conn= DriverManager.getConnection( database)

Here is the stacktrace:

java.lang.NullPointerException

        at sun.jdbc.odbc.JdbcOdbcDriver.initialize(JdbcOdbcDriver.java:436)

        at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:153)

        at java.sql.DriverManager.getConnection(DriverManager.java:582)

        at java.sql.DriverManager.getConnection(DriverManager.java:207)

        at test.main(test.java:20)

To write this test I used this as my primary source: http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=2691&lngWId=2

MS-Access is also a "desktop" database - not a multi-user database. If you expose access to it to the web, you are in for a world of hurt. Do yourself a huge favor, and move all of the data to a real multi-user database, like Postgresql or MySql. — GreyBeardedGeek, Mar 29 '12 at 01:46
@GreyBeardedGeek While I agree with your sentiment, when someone else is paying the bills, if they want Access, they get Access, world of hurt and all. — corsiKa, Sep 19 '13 at 15:52

score 0 · Answer 1 · answered Sep 19 '13 at 15:45

0

String strconnect="jdbc:odbc:DRIVER=Microsoft Access Driver (*.mdb, *.accdb);DBQ="
Sring filename="some path";
StringBuilder a =new StringBuilder();
a.append(strconnect);
a.append(filename);
String db = a.toString();   
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection c= DriverManager.getrConnection(db,"","")
Statement stmt = c.createStament();
String query = "some query";
stmt.executeQuery(query);
c.close();

Never get canonical Path, Either Specify the path of the access file or use JFile Explore and get path

also don't forget to close connection

answered Sep 19 '13 at 15:45

Sudharsun

741
6
23

The explicit StringBuilder is unnecessary. `String db = strconnect + filename;` will automatically create a string builder for you. Makes it a lot cleaner. :) – corsiKa Sep 19 '13 at 15:51
you are right, but some cases include special charecters, so StringBuilder is safes way – Sudharsun Sep 20 '13 at 05:36
1

Actually, they're identical. When you use the `+` or `+=` operator on a String in Java, the compiler puts a StringBuilder in the resulting bytecode. So whether you use the + opreator or not, you're using a StringBuilder. – corsiKa Sep 20 '13 at 14:01
Ok Man that fine, But is there any wrong using String builder. – Sudharsun Sep 20 '13 at 14:04

score -1 · Answer 2 · edited May 23 '17 at 11:56

Since you mention apache, I will presume that the server is running Linux or BSD.
In that case, please have a look at the following:

Connecting to access database from linux
Good Linux ODBC drivers for Access are expensive! (US$850 for a single machine!)

There is no default drivers for Access provided on linux, and the JDBC ODBC won't work unless you install one.

Unless there is a very special and overwhelming good reason for using an Access database as the backend to a website on a linux server, tell your friend that this is not technically or even economically coherent.

If you need a lightweight database on the server, use SQLite: it's free, there is good support for connecting to it from Java in linux, there is good tooling (even browser plugins), and you can always convert that database to Access if your friend want to get an Access backup once in a while.

Otherwise, go for PostgreSQL or MySQL like everyone does (generally for a good reason).

I think it's a poor decision to assume that if you're running Apache, you're also running a Unix operation system. There are plenty of shops that run Windows and still run Apache. — corsiKa, Sep 19 '13 at 15:49

Access (.mdb) Database manipulation with jdbc

2 Answers2