Using a SmartConnect map with an Excel data source. Recently there have been issues opening the map, the error is telling me that the data source cannot be found, however if I close the map and reopen it, it will sometimes reconnect. On the occasions when it fails, when I hit the validate button, it says connection could not be validated, and gives me a blank error message like this.
This appears to be caused by some Microsoft Office updates that were released early this year. The only solution that seems to fix the issue permanently is installing an older version of the Excel 32 bit ODBC driver. We typically have had the best results with the Excel 2010 drivers, which will work with any Excel file created using Excel 2007 and higher. The map setup does not need to be altered, you can still select any version of Excel when you configure your map, when the map runs, the data source will use any 32 bit driver Excel driver that is available.
You will want to download the Excel 2010 32 bit ODBC Driver from this location and run the installer. You need to download the 32 bit version of the file which is named AccessDatabaseEngine.exe, do not use the 64 bit download which is named AccessDatabaseEngine_X64.exe.
If you already have the Excel 2010 64 bit drivers installed you will get an error that says “You cannot install the 32-bit version of Microsoft Access Database Engine 2010 because you currently have 64-bit Office products installed. ” when you attempt to run the installation that looks like this.
If you get this error, you can still install the Excel 2010 32 bit drivers, however you have to do it via a command line. To do this, use Windows Search to search for “cmd” which should display “Command Prompt” in the results, right click on the result and select “Run as Administrator”, type in administrator credentials if prompted.
Once you are in the command prompt, you need to navigate to the location where you have download the “AccessDatabaseEngine.exe” file. I normally will move it to an easier to find location than my downloads folder, in this case I have created a “temp” folder and saved the file into my c:temp folder. So, in order to get into that folder, I type in “cd c:temp” and hit enter, the command line should look like this after that. 
Then you need to run the installation using the /quiet switch. To do that type in the following into the command window “AccessDatabaseEngine.exe /quiet”, the command prompt will look like this before you hit enter.
Press Enter and the 32 bit installer will run without displaying any errors. The installer will open and show that it’s running, however you will not get any sort of confirmation that the installation has completed. Once the command prompt window is available again, you can close it.
After installing the 32 bit drivers, your Excel data source maps should work consistently.
