This happens when the ODBC bit version being used is different from the bit version of Excel. Both the ODBC version and Excel have to be on the same version.
For example, Your Office may be 64bit, and you are attempting to use the ODBC which is set in the 32-bit version of the ODBC Data Source Administrator
There are various stages to resolving this issue and the method depends on the version of Office and the version of Windows on the computer. Here we mainly cover Office 2010, 2013 and 2016, being used with Windows 8 and 10.
Notes:
If using Office 2007 it will be 32 bit version as there is no 64 bit version.
If using Windows 7 or Vista, please see the Appendix 1 at the end of the article.
Stage 1 - Check the bit version of Office
Open any Office app (like Word)
Go to File then Account.
Under "Product Information," you will see the version and bitness. If it's not immediately visible, click on About Word (or another app) to see the full version details
Stage 2 - Find the name of the ODBC being used.
Open AOI Administrator and select Data Connections.
From the list of Data Connections select the one being used.
Note the name in the Data source field. This is the name of the ODBC.
You may also find this, by viewing the setup of the Journal Template. To do this:
On the Add-ins tab, click Journal Import.
From the Menu options, click Setup.
Note the name of the ODBC Data source.
Stage 3 - Check if the ODBC being used is of the correct bit version.
If using Windows 8 or 10, search for 'ODBC' in the Windows Start Menu. A choice of two ODBC Data Source Administrator programs will be listed (32-bit and 64-bit).
Open the ODBC Data Source Administrator of the same bit version as Office.
On the System DSN tab, check if the name being used in AOI Administrator is listed.
If not, a new ODBC will need to be created. It is recommended to include the bit version of the ODBC in it's name in order to easily remember for future, e.g. Dimensions64.
Stage 4 - Use this new ODBC data source name in the Journal Template setup (stage 2).
You can also use this new data source within AOI Administrator.
Finally, you can now complete the process in performing the import, using this new data connection.
Appendix 1 - When running Windows 7 or Vista, the ODBC Data Source Administrator shown will be for the bit version of Windows. If Windows is 64 bit and the32 bit ODBC Data Source Administrator is required, navigate to C:\Windows\SysWOW64\Odbcad32.exe