HELP!!!! The refresh operation failed because the source data base or the table does not exist, or because you do not have access to the source

[jackmccarthy]

The refresh operation failed because the source data base or the table does not exist, or because you do not have access to the source

More Details:
OLE DB or ODBC error.
An error occurred while processing the 'TRANSACTIONS' table.
The current operation was cancelled because another operation in the transaction failed.
Out of line object 'DataSourceView', referring to ID(s) 'Temp_DSV', has been specified but has not been used.
????????

 

[Laurent C]

Does the 'TRANSACTIONS' table exist, and do you have permissions to access it?

 

[jackmccarthy]

Yes. I had this workbook working.I was updating my data when this problem happend. I tested the "transaction table" in a blank workbook and it loads correctly.

 

[paliman]

It happened to me when I started to use Excel 2010. In most of the cases I was able to fix the error like this:

Press Ctrl + F3 and see if the transaction table is pointed twice. If so, remove the one that has external references.

 

[jackmccarthy]

ctrl F3 in excel takes me to name manager. It appears to be all slicers. I dont see anything related to tables here. I think the problem is that the data in powerpivot table "TRANSACTIONS" has not been updated.

 

[paliman]

Yes, you are led to the name manager. When I had this problem most of the times the data source was twice in that box, and one of them was pointing to a server location.

Another suggestion: If you click on any part of the3 pivot table, there will appear two aditional ribbons grouped by the name of "PivotTable Tools". Go to the "Options" ribbon, click on "Change Data Source" and select the TRANSACTIONS table once again.

 

[Laurent C]

@Pali: Your suggestion is valid for regurlar pivot tables but not for PowerPivot tables.

@jack: where do your transaction table come from? is it a SQL Server connection? Check the connection in the PowerPivot model. Go to your Transaction table then click on Update. If this does not work then go to Design and then Properties to check your connection.

 

[jackmccarthy]

did that it's telling me The SQL statement is not valid. There are no columns detected in the statement. I know they are there. I have checked database and it is there??????????

 

[Laurent C]

So that tells us this is indeed a problem with the connection or the SQL statement. Check the connection string. Does it point to the right DB? Is there any misspelling of a table name or any syntax error? If your SQL statement is a query, does it run correctly in SSMS? (If you have the tool).