I have a .accdb file created with Microsoft Office 2010 and I want to know if it is possible to open it with some native Ubuntu application like LibreOffice or OpenOffice, I know LibreOffice has LibreOffice Base but I can't figure out how to open or if it's even possible to open a .accdb file.
Is there any way I can open a .accdb file without Wine or VirtualBox?
Yes, we can use the UCanAccess JDBC driver to connect to Access databases (.mdb and .accdb) in LibreOffice Base.
First, I installed LibreOffice Base
sudo apt install libreoffice-base
Then I went to UCanAccess on Maven Central, Browsed the latest version, and downloaded the uber.jar file.
I launched LibreOffice (not Base, just LibreOffice itself)
and chose Tools > Options
On the Advanced tab I clicked the "Class Path..." button
and then added the following JAR file using the "Add Archive..." button:
/home/gord/Downloads/ucanaccess-5.1.2-uber.jar
Important: You must close and re-open all LibreOffice (or OpenOffice.org) components for the new "Class Path...” value to take effect. That includes any "quick start” features or other related processes. (If you want to play it safe, simply restart your machine.)
I launched LibreOffice Base, and in Step 1 of the wizard I chose "Connect to an existing database (JDBC)"
The Access file I wanted to manipulate was named "mdbTest.mdb" in my Documents folder, so in Step 2 the "Datasource URL" was …
jdbc:ucanaccess:///home/gord/Documents/mdbTest.mdb
(note that Base supplies the jdbc: prefix for us, so all we need to enter is the remainder of the URL starting with ucanaccess: …)
… and the "JDBC driver class" was
net.ucanaccess.jdbc.UcanaccessDriver
In Step 3, I left the "User name" field empty and just clicked "Next >>".
In Step 4, I saved the LibreOffice Base database as "mdbTest.odb" in my Documents folder.
When the wizard completed it opened my LibreOffice database and I could see the tables in the .accdb file
If you are using a distribution that installs LibreOffice Base by default (e.g., Linux Mint) then you may receive the error
The connection to the data source "MyDatabase" could not be established.
'org.hsqldb.persist.HsqlProperties org.hsqldb.DatabaseURL.parseURL(java.lang.String, boolean, boolean)'
That is because LibreOffice has installed its own (rather old) copy of HSQLDB that conflicts with UCanAccess. To fix that, remove LibreOffice's copy of HSQLDB. For example, on Linux Mint that would be
sudo apt remove libhsqldb1.8.0-java
Also, if LibreOffice Base won't work for whatever reason you can use DBeaver instead. Installing the (free) Community Edition is as easy as
sudo snap install dbeaver-ce
As mentioned here it says it supports Access files but if we look deeper they have only tested in LibreOffice up to Office 2007.
Since you have an Office 2010 I would suggest trying out this link since other users by the look of this have already tried.
Additionally the compatibility shown here says how good LibreOffice Base is when relating to Microsoft Access 2010. At least up to version 3.6.
There is also a question on the Ask Libreoffice site about this that mentions this link where it is said that the 2007 format is different from the 2010 so it will not work correctly in LibreOffice 3.6 or below.
My only recommendation that does not include Wine or VirtualBox would be to use MS Office 2010 to save the Access file as a 2007 version (If possible) or a MDB format. At least while LibreOffice works on improving compatibility with 2010 in general.
