HELP ARTICLE

Excel data sources work intermittently or give blank error message.


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 /passive switch.  To do that type in the following into the command window “AccessDatabaseEngine.exe /passive”, 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.  












 









RECENT POSTS


How to set up automatic payments on eOne's website portal
"Info 1625. This installation is forbidden by system policy", when attempting to install the SmartConnect Excel Add-In
Salesforce Objects or fields are not available in SmartConnect
Attempting to select a readonly publisher for solution
Success Management Process for SmartConnect

POPULAR POSTS


Zendesk Dynamics 365 Sales Connector
SSL Security error using OLEDB Connection
Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))
The server process could not be started because the configured identity is incorrect.
Using WINSCP and a task to upload or download files from an SFTP site

CATEGORIES

ARCHIVES

open all | close all

TAGS

Business Central CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News eOne Spotlight Events Excel Excel Report Builder Extender Extender Enterprise Flexicoder GP GPUG integration Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Office Relationships partners Popdock release Reporting SalesForce SalesForce.com scripting SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Office Hours SmartList SmartList Builder SmartList Designer SmartView SQL Support Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.