Description: Base, in Windows OS, has two specific options to connect to MS Access databases: "Microsoft Access" and "Microsoft Access 2007". In order to use these connections, the corresponding database engines must be installed on the system. The "Microsoft Access" connection uses the "Microsoft.Jet.OLEDB.4.0" engine as data provider. This engine was used to connect to *.mdb databases and was installed with certain versions of MS Access. Also it was available for download and installation on Microsof's web pages, although for some time now, this engine is no longer available for download on these pages. The "Microsoft Access 2007" connection uses the "Microsoft.ACE.OLEDB.12.0" provider. Although there is already a newer version, this provider can be obtained from Microsoft's pages by downloading and installing the "Microsoft Access Database Engine 2010 Redistributable". Although the Jet.OLEDB.4.0 engine is not readily available, a connection to *.mdb files can be established via the "Microsoft.ACE.OLEDB.12.0" provider used by the "Microsoft Access 2007" connection, however, if in the database wizard the "Microsoft Access 2007" connection is selected, it does not allow to select *.mdb files because the pattern is set for *.accdb files only. To achieve this improvement it should be done: - Remove the connection that is made via the Jet.OLEDB.4.0 provider. - Rename the connection made with the ACE.OLEDB.12.0 engine (probably to "Microsoft Access" without "2007"). - Extend the pattern of this last connection so that *.mdb files can be opened. Another alternative would be: - Keep the connection to the Jet.OLEDB.4.0 engine (in case some user still has this engine) - Rename the connection "Microsoft Access 2007", for example to "Microsof Access OLEDB.12" so that it does not specify the version of the file type but the engine used. - Add to this connection the *.mdb pattern to be able to open this type of files. Steps to Reproduce: See description Actual Results: Connection to *.mdb files can not be established without Jet.OLEDB.4.0 engine (not available) Expected Results: Establish connection to *.mdb files with ACE.OLEDB.12.0 engine Reproducible: Always User Profile Reset: No Additional Info: Version: 7.6.2.1 (X86_64) / LibreOffice Community Build ID: 56f7684011345957bbf33a7ee678afaf4d2ba333 CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Vulkan; VCL: win Locale: es-ES (es_ES); UI: es-ES Calc: CL threaded
Created attachment 190642 [details] Sample database Como se deduce de mi exposición anterior, para poder conectar con la base de datos se requiere tener instalado el proveedor de datos "Microsoft.ACE.OLEDB.12.0". Los pasos a seguir para comprobar que se puede establecer la conexión con los archivos *.mdb son los siguientes (puede usar la base de datos de ejemplo anexada): 1. Si no se tiene el proveedor instalado, descargar e instalar el paquete "Microsoft Access Database Engine 2010 Redistributable" 2. Abrir Base para crear una base de datos nueva 3. En el database wizard, seleccionar “Connect to an existing database” y en la lista desplegable, seleccionar “Microsoft Access 2007”. Click on “Next2 4. En el segundo paso, haga clic sobre el botón “Browse” 5. En el cuadro de texto escribir “*.mdb” (sin las comillas) y pulsar “Enter” para que el explorador de archivos muestre los archivos *.mdb 6. Navegar para buscar y seleccionar el archivo *.mdb y hacer clic sobre el botón “Open” 7. Hacer click en “Finish” y dar un nombre al archivo .ODB Una comprobación adicional que se puede hacer para saber qué proveedores de datos están instalados es utilizar la conexión ADO (en realidad, Base utiliza internamente ADO para conectarse a las bases de datos Access): 1. Abrir Base para crear una base de datos nueva 2. En el database wizard, seleccionar “Connect to an existing database” y en la lista desplegable, seleccionar “ADO” 3. En es segundo paso del database wizard hacer clic sobre el botón “Browse” 4. Se mostrará una ventana con un cuadro de lista con los proveedores de datos instalados, entre ellos, si hemos instalado el "Microsoft Access Database Engine 2010 Redistributable", tendremos “Microsoft Office 12.0 Access Database Engine OLE DB Provider” que es el que se utiliza para la conexión “Microsoft Access 2007” 5. En el improbable caso de que tenga instalado "Microsoft.Jet.OLEDB.4.0" también aparecerá en esta lista. Si tiene instalad una versión moderna de Access o ha instalado el "Microsoft Access Database Engine 2016 Redistributable", también tenga el “Microsoft Office 16.0 Access Database Engine OLE DB Provider”
As you can deduce from my previous exposition, to be able to connect to the database it is required to have installed the data provider "Microsoft.ACE.OLEDB.12.0". The steps to follow to verify that you can establish the connection with the *.mdb files are the following (you can use the attached example database): 1. If you do not have the provider installed, download and install the package "Microsoft Access Database Engine 2010 Redistributable" 2. 2. Open Base to create a new database 3. In the database wizard, select "Connect to an existing database" and in the drop-down list, select "Microsoft Access 2007". Click on "Next2 4. In the second step, click on the "Browse" button. 5. In the text box type "*.mdb" (without the quotes) and press "Enter" to make the file explorer display the *.mdb files. 6. Browse to find and select the *.mdb file and click on the "Open" button. 7. Click on "Finish" and give a name to the .ODB file. An additional check that can be made to find out which data providers are installed is to use the ADO connection (actually, Base uses ADO internally to connect to Access databases): Open Base to create a new database 2. 2. In the database wizard, select "Connect to an existing database" and in the drop-down list, select "ADO". 3. In the second step of the database wizard click on the "Browse" button. 4. A window will appear with a list box with the installed data providers, among them, if we have installed the "Microsoft Access Database Engine 2010 Redistributable", we will have "Microsoft Office 12.0 Access Database Engine OLE DB Provider" which is the one used for the "Microsoft Access 2007" connection. 5. In the unlikely event that you have "Microsoft.Jet.OLEDB.4.0" installed, it will also appear in this list. If you have installed a modern version of Access or have installed the "Microsoft Access Database Engine 2016 Redistributable", you also have the "Microsoft Office 16.0 Access Database Engine OLE DB Provider".
Summary of current status * Currently to connect to MS Access .mdb files you need the "Microsoft.Jet.OLEDB.4.0" provider. * The "Microsoft.Jet.OLEDB.4.0" provider needs to be installed on the computer to use it. * The connector "Microsoft.Jet.OLEDB.4.0" is not available on the Microsoft download pages for some time now * You can connect to both .mdb and more modern .accdb files via the "Microsoft.ACE.OLEDB.12.0" provider, but Base is only prepared to use this provider with .accdb files. * The "Microsoft.ACE.OLEDB.12.0" provider also needs to be installed, but it is still possible to download an installer from the Microsoft pages. Proposed solution * Modify the code so that all connections to MS Access files, both .mdb and .accdb are made with the provider "Microsoft.ACE.OLEDB.12.0". * Remove all references to the "Microsoft.Jet.OLEDB.4.0" provider to clean up the code. * Leave only one entry "Microsoft Access" in the Connection Wizard, which would work for .mdb and .accdb files. * Remove the entry "Microsoft Access 2007" from the Connection Wizard, since it is no longer needed.
Created attachment 193471 [details] Test database format .accdb
Comment on attachment 193471 [details] Test database format .accdb Test database in Access 2007 format
jucasaca committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/7edca7dc740f6877fa85c2a996ca869c6b971a48 tdf#158056 Connect to MS Access .mdb files by mean of ACE.OLEDB.12.0 provider It will be available in 24.8.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.