Connecting to access database from linux

Question

I've created my application and tested it under windows, which writes/reads to/from a access DB file.

But in the real world it will be ran in the linux environment, and I have a big issue now, it appears that there are no drivers for linux to access ms acess db, here is how I make the connection now :

private static Connection getConnection() {
        if (connection == null) {
            try {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String conStr = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=" + configuration.getAccessDbFile();
                connection = DriverManager.getConnection(conStr);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return connection;
    }

Has anyone encountered something similar to this, does anybody have a suggestion what could I do ?

This is the exception I get on the linux :

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)

score 7 · Accepted Answer · answered Apr 21 '11 at 16:18

7

It's uncommon for an application running on Linux to access an MS Access database or use ODBC. ODBC is a windows technology. There are some options for linux, but it's not a common scenario.

As you've discovered, there are no Access ODBC drivers on your linux machine, so the JDBC-ODBC bridge fails. You might be able to install a suitable ODBC driver, but I don't know of any that are free or open-source. The defacto ODBC option for linux is:

http://www.unixodbc.org/

A commercial driver for Access based on UnixODBC:

http://www.easysoft.com/products/data_access/index.html

A type 4 JDBC driver that can supposedly connect to Access databases:

http://www.hxtt.com/access.html

I don't have personal experience with any of these. The type 4 JDBC driver would be ideal, but I'd be skeptical that it works as perfectly as advertised.

(I am sure you have reasons, but I have to wonder why you are using an Access database if you plan on deploying to Linux machines. I believe the only good reason to have a Java application use an Access database is if it's an existing Access application with custom programming used for purposes beyond the Java application. Otherwise, there are a number of better options. Also consider that if your main reason for using Access is just so you can use Access as a user-friendly GUI tool for forms & reporting, you could still store the data in less restrictive database (Derby, SQLLite, MySQL, PostgreSQL, MS SQL Server, etc) and then connect via ODBC from Access to the database.) This would allow you to deploy your Java application on linux, your database wherever makes most sense, and still use Access to connect to the database from windows. I've done this a number of times.)

answered Apr 21 '11 at 16:18

kaliatech

17,579
5
72
84

3

The first step most developers would do in such a situation would do would be to export the Access database and import it into something reasonable, like MySQL. See http://databases.aspfaq.com/database/what-are-the-limitations-of-ms-access.html for a variety of opinions why Access should not be used for anything "real". – wallyk Apr 21 '11 at 16:32
3

@wallyk: what do you mean by "real"? Linux applications? If so, I'd agree with that. Web applications? If so, yes, I'd agree with that. But there is a huge swath of applications for which Access/Jet/ACE is a perfectly appropriate tool/data store -- it's just as "real" as anything else. In short, lose the anti-Access bigotry. – David-W-Fenton Apr 26 '11 at 01:52
2

@David-W-Fenton: It is not bigotry—I have decades of development and maintenance experience saving applications mired in Microsoft technologies. By "real" I mean a significant number of concurrent users, which for Access is more than about 3 or 4 according to my experience, other consultants, and industry reviewers. For standalone applications Access tends to be okay. But if it is ported to any situation with more than a handful of users *or* automated data entry, the concurrency and scalability limitations of Access quickly catch up to the design. – wallyk Apr 26 '11 at 02:06
1

I routinely develop and maintain Access apps with Jet/ACE back ends that have simultaneous user populations in the range of 10-25 users. At the upper end of that, I usually recommend planning to upsize, particularly if there's any possibility of any more increases in user population. At the lower end, not so much. It doesn't require any particularly special programming to make this kind of application work reliably with that many users and a Jet/ACE back end -- you just have to be efficient and not retrieve more data than the user actually can use at once. – David-W-Fenton Apr 29 '11 at 01:29
2

...so, I don't understand why people are routinely complaining about Access with fewer than 5 users. I can't imagine what things they are doing completely wrong. On the other hand, I've been doing this professionally since 1996 and have read a lot on optimizing Access, and have worked hard to understand how the database engine works. What I don't understand is why so many people don't seem willing to do these same things. Upsizing to a different back end is not going to fix a badly-engineered application design -- there is no magic bullet. – David-W-Fenton Apr 29 '11 at 01:31
Hi there, as you mentioned i have installed unixodbc in my ubuntu box and when I try to access the MS-Access Database using jdbc with jdbc-odbc bridge, i get the following error :Unable to connect to database: java.sql.SQLException: [unixODBC][Driver Manager]Data source name not found, and no default driver specified java.sql.SQLException: [unixODBC][Driver Manager]Data source name not found, and no default driver specified. Plese help me in resolving this problem.Regards sentil – user1503117 Oct 15 '14 at 07:50
1

@user1503117 It is extremely unlikely that you will receive any assistance with your issue based on your comment to this (very old) question. Instead, you should [ask a new question](http://stackoverflow.com/questions/ask). – Gord Thompson Oct 20 '14 at 14:23

score 7 · Answer 2 · answered Sep 08 '11 at 14:49

7

Use http://jackcess.sourceforge.net/

You can read / write a Acceess database from Linux or Windows using Java.

answered Sep 08 '11 at 14:49

Abel

71
1
1

score 4 · Answer 3 · answered Apr 21 '11 at 17:02

If you are stuck with Access, but your interactions with the database file are pretty "simple" (e.g. you aren't doing complex SQL queries), you could use something like Jackcess. It is a pure-java library which allows you to manipulate an Access database, but it does not provide a jdbc interface, so you have to write code using the Jackcess api.

(disclaimer, i am a jackcess developer)

score 3 · Answer 4 · edited May 23 '17 at 11:54

3

UCanAccess is a free, open-source pure Java JDBC driver for Access databases. For more information on how to use it, see

Manipulating an Access database from Java without ODBC

edited May 23 '17 at 11:54

Community

1
1

answered Oct 20 '14 at 14:27

Gord Thompson

116,920
32
215
418

score 0 · Answer 5 · answered Apr 21 '11 at 09:52

0

Go through thus link which will help you in this.

http://www.neowin.net/forum/topic/887410-java-ms-access-driver-for-maclinux/

and can you debug and let us know what is the connection string you are passing to get the connection. Because I think you should call getConnection as below

DriverManager.getConnection(conStr, "", "");

answered Apr 21 '11 at 09:52

GuruKulki

25,776
50
140
201

I can't debug this, I'm executing this from executable jar – London Apr 21 '11 at 10:15

Connecting to access database from linux

5 Answers5

Linked