How do I connect a MS Access database to Jetbrains DataGrip

Question

I have been using DataGrip for a couple of weeks now and I like it a lot. The only thing I am unable to find out is how to connect DataGrip to a MS Access database.

Could someone explain to me how to do that?

Gunas

Answer 1

As per the documentation, MS Access is not supported yet.

The following standardized and DBMS vendor-specific SQL dialects are supported: DB2, Derby, H2, HSQLDB, MySQL, Oracle, Oracle SQL*Plus, PostgreSQL, SQL Server, SQL92, SQLite, and Sybase .

Duodenary

Answer 2

For Future readers this is still very doable (I'm on DataGrip version 208.1.4):

Get UcanAccess

1. Download and extract UcanAccess somewhere: pathToUcanAccess
2. In Datagrip goto add new connection: "Driver And Data source"
   • this will create a connection "Database Driver -" and a Driver "Database Driver"
3. Edit the newly created Driver "Database Driver":
   1. Under Additional Files add:
      • <pathToUcanAccess>\ucanaccess-4.0.4.jar
      • <pathToUcanAccess>\lib\commons-lang-2.6.jar
      • <pathToUcanAccess>\lib\commons-logging-1.1.3.jar
      • <pathToUcanAccess>\lib\hsqldb.jar
      • <pathToUcanAccess>\lib\jackcess-2.1.11.jar
   2. For Dialect select "SQL Driver"
   3. Hit Apply (this should load the classes from the added files)
   4. Under the class combo you should now see net.ucanaccess.jdbc.UcanaccessDriver, select it and hit apply again.
   5. Under Url templates add a new template, for name enter "default" for template enter jdbc:ucanaccess://{file::identifier.sqlite};newdatabaseversion=V2010
   6. Hit apply.
4. Now edit the database connection "Database Driver -":
   1. Next to the text box for url, in the drop down select default (or what ever you named the template in step 3.5)
      • A file select field should replace the previously visible "user" and "password" fields
   2. In the file selector, select your mdb
      • the url field should now look like jdbc:ucanaccess://C:/data/MDBs/myMdbFile.mdb;newdatabaseversion=V2010
   3. Hit apply and then ok.

You should see your Connection popup in the list, double-click to connect, expand it down the tree ("Public \ <unnamed> \ tables") and you should see your tables.

Inspired by this question.

Milter

Thanks a lot @GHayes, it still works fine with version 2018.2. You saved me a lot of time and pain. Thanks! – Dexedrine

An update for 2021.1 would be useful, as I can find "additional files"... – Trimester

I get this error when testing the connection 42581][-5581] UCAExc:::5.0.1 unexpected token: INTEGER required: FOR [42581][-5581] unexpected token: INTEGER required: FOR – Hunchbacked

Answer 3

You can try configuring MS Access as an ODBC source and then use a JDBC ODBC driver to connect to it, just like any other JDBC database.

Sasin