How to connect to a Microsoft Access database

    From The Document Foundation Wiki
    < Faq‎ | Base
    This page contains changes which are not marked for translation.


    This article describes how to connect to Microsoft Access database files.

    Although most of the options can only be used on Windows, an option of how to connect on Linux and other operating systems is also described.

    To connect to Access database files the following options are available:

    • ADO (Windows only)
    • Microsoft Access (Windows only)
    • Microsoft Access 2007 (Windows only)
    • ODBC
    • JDBC

    Next we will see how to use each one of the following connections

    Preconditions in Windows

    Although in older versions of Windows (up to XP) it was likely to have the necessary data provider installed, in newer versions of Windows there is no data provider installed. The data provider is a Microsoft component and is not developed or modified by LibreOffice, it is only used as an intermediary between LibreOffice and the Microsoft Access database file.

    The MDB file types used by versions from Access 97 (Office 8) to Access 2003 (Office 12) required the “Jet 4.0” data provider (Microsoft.Jet.OLEDB.4.0 provider) to be installed. Starting with Access 2003, MS Access starts using a new database file type, type ACCDB, which uses the Access Database Engine, ACE, provider (Microsoft.ACE.OLEDB.12.0 provider).

    Up to LibreOffice version 24.8, the Microsoft Access option is used to connect to MDB files. As we have seen, it requires the Microsoft.Jet.OLEDB.4.0 provider, which has not been available for download on Microsoft's web pages for several years, so this connection is inoperative.

    The Microsoft Access 2007 option, existing up to LibreOffice 24.8, is used to connect to ACCDB files and, as mentioned above, requires the Microsoft.ACE.OLEDB.12.0 provider.

    As of LibreOffice version 24.8, there is only a Microsoft Access option using the Microsoft.ACE.OLEDB.12.0 provider for both MDB and ACCDB file connections.

    ADO connections also need a Microsoft provider to connect to Microsoft Access files.

    In short, to connect to Microsoft Access files with the Windows options, you need to install the appropriate provider. Currently by downloading and installing the Microsoft Access Database Engine 2016 Redistributable from https://www.microsoft.com/en-us/download/details.aspx?id=54920 , you will get the Microsoft.ACE.OLEDB.12.0 (the one used by LibreOffice), Microsoft.ACE.OLEDB.16.0 and the ODBC driver.

    Setup Connection (Wizard Step 1)

    Start wizard with File ▸ New ▸ Database or use item LibreOffice Base from Windows Start menu, or use item Database in LibreOffice Quickstarter.

    In the first step select Connect to an existing database. Open the drop-down list. You can use Microsoft Access, ADO, ODBC, or JDBC. Each of the options has the following characteristics:

    Microsoft Access (up to LibreOffice version 24.8)

    As we have seen in the Preconditions in Windows, you will not be able to connect to MDB files if you do not have the Microsoft.Jet.OLEDB.4.0 provider. Even if you have the provider you will not be able to use this connection if the database is password protected, in this case you must use the ADO connection.

    Microsoft Access 2007 (up to LibreOffice version 24.8)

    This option is used to access ACCDB database files.

    Microsoft Access (LibreOffice version 24.8 and higher)

    Starting with LibreOffice 24.8, the Microsoft Access connection is used to access both MDB and ACCDB files.

    ADO

    The ADO connection allows you to use any of the installed providers to make connections to Access files. It also allows you to configure other connection options that are not configurable with the previous options, such as setting the user name and password to connect to protected databases.

    ODBC

    The connection to the database is made through an ODBC driver. The driver configuration is done at the operating system level and is independent of LibreOffice, which can only use the previously configured drivers.

    JDBC

    The JDBC connection requires a driver that has to be previously installed, although the final configuration is done from LibreOffice. This type of connection requires Java to be installed on the computer. We will see this connection in more detail later in a separate title.

    The next steps depend on the chosen kind of connection. When connection is finished, you see your chosen connection on the left side in the status bar.

    Setup Connection (Wizard Step 2)

    Microsoft Access and Microsoft Access 2007

    Type the path to the database or click the Browse button to navigate to the appropriate file. Clicking Next will take you to the last step of the wizard (See Configure the connection (Step 4 of the wizard)), although in this case it will be step 3.

    ADO

    Enter the complete connection string to access the database file. The connection string is different from the file path.

    Generate the ADO connection string

    If you do not know the connection string, click the Browse button and it will open a wizard for the data link configuration, where you can select the provider, the data file to connect to, user and password for the connection, and other advanced options. When you close the dialog the generated connection string will be displayed.

    For Access files (MDB and ACCDB) you can select either the Microsoft Office 12.0 Access Database Engine OLE DB Provider or the Microsoft Office 16.0 Access Database Engine OLE DB Provider.

    ODBC

    To establish an ODBC connection, in this second step of the wizard you must provide the name of the data source. You can also click on the Browse button and you will get a list of registered data sources.

    If you have not registered your database you will have to do it with the Windows application ODBC Data Sources. With this application you will be able to add a connection with the ODBC drivers installed. In our case you must select the Microsof Access Driver (*.mdb, *.accdb), once selected, by clicking on the Finish button a dialog will appear to complete the configuration.

    If the database to which we will connect is password protected, it can be configured using the Advanced button of the connection configuration dialog, but it must be noted that the password is readable in the ODBC registry.

    The ODBC driver for the Microsoft Access connection is installed together with the ADO providers that we saw in the prerequisites.

    Connection configuration (Wizard Step 3)

    In this step the password options for the different connection types are configured.

    ADO

    Leave the fields empty.

    ODBC

    If the database is password protected and you did not type it in the ODBC connection setup, check the Password required option.

    MDB files

    If the database is protected, in User name type Admin or the user name if it is a different one. If it is not protected leave the field empty.

    ACCDB files

    This file type does not use users, so leave the Username field empty.

    Check the connection if necessary. Click on Next

    Save and Proceed (Wizard Step 4)

    If you want to use the data source in other LibreOffice modules, e.g. for mail merge, check the option Yes, register the database for me. After clicking Finish, you will be prompted for the name of the .ODB file and the folder in which to save the file. You can now work with the database.

    JDBC Connections (Windows, Linux and other operating systems)

    The JDBC connection is the only one of the connections to Microsoft Access that can be used on non-Windows operating systems. Well, theoretically you could also use ODBC connections, but I'm afraid there is no free driver for non-Windows systems.

    The JDBC connection, as already mentioned, requires that the system has Java installed. In addition a suitable JDBC driver is required for Microsoft Access connections. At least there is a free opensource driver, UCanAccess, which can be downloaded from https://ucanaccess.sourceforge.net/site.html. Being a pure java driver, it can be used on any operating system that can install Java.

    Preparing the computer

    As mentioned above, the computer must have Java installed.

    In addition, the UcanAccess driver must be downloaded and unzipped to a suitable location. When unzipping the file, the folder structure of the compressed file must be respected.

    Prepare LibreOffice

    • Open LibreOffice in the Start Center or start any of the applications (except Base or Math).
    • Select Tools ▸ Options ▸ LibreOffice ▸ Advanced, under Java options, check the Use a Java Runtime Environment option and under Installed Runtime Environments (JRE) select the one you want to use.
    • Without leaving the Advanced options page, click on the Classpath button.
    • In the following dialog select the Add folder button and navigate to the folder where you unzipped the driver, inside this folder select the loader folder and inside it the ucanload.jar file. Click on Open and then close all dialogs with the OK button.
    • Restart LibreOffice for the configuration to take effect.

    Connecting

    • Open Database
    • In step 1 of the wizard, select Connect to an existing database and from the drop-down list select JDBC. Click Next.
    • In step 2, in the text box where it says to provide the connection string, after jdbc: type ucanaccess// and the complete path to the database, but although in Windows the backslash (\) is used here it should be replaced by slash (/) for example jdbc:ucanaccess://C:/Users/jc/Desktop/Access/Database1.accdb or jdbc:ucanaccess:///home/jc/Desktop/Access/Database1.accdb.
    • In that same dialog in the JDBC Driver Class text box you must type net.ucanaccess.jdbc.UcanloadDriver (the driver name is case sensitive, so you must type it as shown). You can use the Test Class button to check that you have spelled the name correctly and that the class path was set up properly. Click Next.
    • In the next dialog box you can use the Test connection button to check if everything is set up properly. Leave the user name blank and do not check the Password required option, because this driver does not allow access to protected databases. Click Next.
    • In the next dialog box configure whether or not to register the database. Click Finish, the dialog will appear to give a name to the created ODB file and select the folder where it will be saved.

    Properties of the Connections

    When connecting to a Microsoft Access database from LibreOffice, neither forms nor reports are accessible and existing queries are converted to views.

    Access to tables depends on the type of Access file and the type of connection used.

    Access to the Tables

    MDB files

    Both in the connections established through Microsoft Access and ADO options, you can alter the content of the tables and add and delete records. You cannot add tables or modify the structure of existing tables.

    In connections established with ODBC you can add, edit and delete records. Tables can be added, but the structure of existing tables cannot be modified.

    In the JDBC connections you can see the data and apparently you can update it, but it gives errors in any of the operations.

    ACCDB files

    LibreOffice versions prior to 24.8

    In both connections established with Microsoft Access 2007 and those established with ADO, data can be viewed but cannot be modified. It is also not possible to add tables or modify the structure of existing tables.

    In the connections established with ODBC you can add, edit and delete records. Tables can be added, but the structure of existing tables cannot be modified. In JDBC connections you can see the data and apparently you can update it, but it gives errors in any of the operations.

    Version 24.8 of LibreOffice and later versions

    In this case, both in the connections established with Microsoft Access and those established with ADO, the data can be seen and can also be modified, but it is not possible to add tables or modify the structure of the existing ones.

    ODBC and JDBC connections work the same as in LibreOffice versions prior to 24.8.


    See also

    https://wiki.openoffice.org/wiki/MSA-Base_Faq

    https://developer.microsoft.com/



    Autor: Juan C. Sanz 9 march 2025. Based on Regina's work.

    Please don't hesitate to improve this guide and adapt it to new versions.