Did you create a System DSN ODBC connection to pull your tables/data? Trick is to set up your company code and login details in the DSN setup.

I've had similar errors before, using standard  "SOTAMAS90" ODBC connection, especially when other users are working.

Has anyone come up with a viable answer to this error?  Is it a permissions issue on the server?  Any help will be appreciated.

Response to  mjones5 on Sep 9, 2014 2:24 PM

Project's tech adviser/server administrator says:

"There is an obvious bug with Power Pivot running in a 64 bit environment using office 2010 32-bit.  I suspect it is related to the need for ODBC 32 bit drivers and it appears PP is looking at the 64 bit ODBC.  There is no reference to this anywhere but I will continue to look.  It would be interesting to see if this issue exists as well on a WIndows 7 64-Bit workstation at the office.  {note: attempting PowerPivot on Terminal Services, Windows Server 2008 R2}

There is always the possibility to write a VI job to export the tables you require to an access database and then you can run your pivots off that table if the issue is the SOTA ODBC driver.  Just another step."

We have not pursued or tested further.  The project for - pulling data from Sage 100 tables into PowerPivot is on hold.

You'll need to utilize the Power Pivot |Table Import Wizard | " Write a Query that will specify the data to import"  option not the "Select from a list of tables and views to choose the data to import" option to import the Sage 100 data into Excel.  

John Nichols

Sage