2

I'm currently looking to write an importer in my Java program to import data from a file which contains a JET database, but so far searching for a JDBC driver for this format (or just another Java library which can read from it) has proved fruitless.

Does anyone know if such a driver exists, or if not what (platform independent) alternatives might be available?

Michael Berry
  • 61,291
  • 17
  • 134
  • 188

3 Answers3

2

I actually have been importing CSV files into Access database using JDBC and jetEngine query like this

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// set this to a MS Access DB you have on your machine

String filename = "C:/Automation_Tools/Databases/Data.mdb";

String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";

database+= fileName.trim() + ";DriverID=22;READONLY=false}";

// add on to the end now we can get the connection from the DriverManager

con = DriverManager.getConnection( database ,"","");

and then using query like this

String sql = "INSERT INTO " + accessTableName + " SELECT * FROM [Text;HDR=YES;TextDelimiter=\";Has Quotes=TrueFMT=Delimited(,);DATABASE=" + csvDirPath + ";].[" + csvFileName + "]";

//Import/create table String sql = "SELECT * INTO " + accessTableName + " FROM [Text;HDR=YES;TextDelimiter=\";FMT=Delimited(,);DATABASE=" + csvDirPath + ";].[" + csvFileName + "]"; `

Arslan
  • 52
  • 7
  • you can also add a Schema.ini file in the same location as from where you are reading the file from so your datatypes always map correctly. Otherwise JetEngine decides the data type based on the few rows it reads. Check the sample schema file here [Schema Sample](http://msdn.microsoft.com/en-us/library/windows/desktop/ms709353%28v=vs.85%29.aspx) – Arslan Apr 17 '13 at 18:41
  • Thanks - unfortunately I need it to be platform independent so I'm not sure this approach will really work. – Michael Berry Apr 17 '13 at 21:51
  • 1
    The JDBC-ODBC Bridge is obsolete and has been removed from Java 8. Consider using the UCanAccess JDBC driver (details [here](http://stackoverflow.com/q/21955256/2144390)). – Gord Thompson Oct 23 '15 at 13:21
2

I am just now researching this myself and am going to try out UCanAccess. According to the main page it says that it is

an open source Java JDBC Driver implementation which allows Java developers and jdbc client programs (e.g., DBeaver, NetBeans, SQLeo, Open Office Base, Libre Office Base, Squirrell) to read/write Microsoft Access database.

Because it is a pure java implementation it run in both Windows and non-Windows Operative Systems(e.g., linux/unix). No ODBC needed.

Community
  • 1
  • 1
Joshua
  • 1,019
  • 12
  • 21
  • For details on how to get started with UCanAccess see the related question [here](http://stackoverflow.com/q/21955256/2144390). – Gord Thompson Oct 23 '15 at 13:26
1

You can use the built in JDBC ODBC bridge driver. Use a connection string like: jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=database.mdb. This is not platform independend. The Jet engine (or ACE) has to be installed. Sometimes there are problems with encoding and Memo fields.

There is an alternative: http://www.hxtt.com/access.html They claim platform independence but I didn't try it myself.

vanje
  • 9,404
  • 2
  • 31
  • 41
  • Thanks for this - unfortunately platform independence is a requirement so the first option won't work, but I'll have a look at that second link (though it seems to be a paid for product unfortunately, which again rules it out for me.) – Michael Berry Apr 17 '13 at 21:50
  • The JDBC-ODBC Bridge is obsolete and has been removed from Java 8. Consider using the UCanAccess JDBC driver (details [here](http://stackoverflow.com/q/21955256/2144390)). – Gord Thompson Oct 23 '15 at 13:21