How to fallback and force BarTender to use the Microsoft Access Database engine driver (OLEDB / JET) in BarTender 2019 and later Follow
Starting with BarTender 2019, BarTender will natively read Excel and Access files as long as they are in the current format that Microsoft Office uses. Please see What Access and Excel file extensions are compatible with BarTender 2019 and later? for more information.
However if something isn't working with the built-in drivers we use or you simply wish to use the JET drivers that were used in BarTender 2016, you have the option to fallback to Microsoft's drivers and force BarTender to use them.
Falling back to the OLEDB driver
If you must fall back to using the OLEDB driver for any reason, you will need to have the Microsoft Access Database Driver installed on their system. The same rules apply for BarTender 2019 that applied for BarTender 2016 when working with the OLEDB driver:
- The bitrate of BarTender and Microsoft Office must still match
- The matching database driver must be installed
There are more nuances with BarTender 2019 and later versions than with 2016 when it comes to swapping bitrates
- We are reliant on the SQL Express install for the System Database
- By it's nature, SQL must match the bitrate of the operating system
With these points in mind, it's easier to change the bitrate of Office than it is BarTender. This method is strongly recommended as the next section has not been fully confirmed as working.
Changing the registry key
Setting the key
The registry key HKEY_CURRENT_USER\Software\Seagull Scientific\BarTender\Preferred Database Providers\Preferred Excel Provider can be set to "OLE DB" to use Ole DB provider instead of our native provider.
If "Preferred Database Providers" is not present, you can add this by right clicking on the white space in the parent "folder" and select New > Key. Note that keys are case sensitive.
If "Preferred Excel Provider" isn't present, right click on the white space in the parent "folder" and select New > String Value. Right click on this key to set the value to "OLE DB". Keys are case and space sensitive.
Important note, setting this registry key will also mean that the OLE DB provider will be used for all Excel and Access connections. If the you decide you want to use this format, you will need to have the Microsoft Access Database Engine driver installed on your system.
To revert back to using BarTender's native drivers, right click on the "Preferred Excel Provider" key and clear the string value on this key.
If you receive errors about JET drivers when attempting to connect to an Excel or Access database, please see Troubleshooting Excel and BarTender