Error PivotTable External Data Source With Linked Table

Hi all,

Using Sql Server Express 2012 64 bit
Office 2013 64 bit

I Linked from Access to a table in SSE - works
I created a query on the linked table - works
I saved and closed Access
I opened a new excel workbook
I attempted to use the query as my data source for a new PivotTable and received this error

The query did not run, or the database table could not be opened.
Check the database server or contact your database administrator. Make sure the external database is available and hasn't been moved or reorganized, then try the operation again.

Both SSE and Access are on local machine
Other than PivotTable, everything else seems to work perfectly.
SQL Server and SQL Browser are running.

I can create a PivotTable in excel using other tables from the Access database, just not linked tables or queries that use linked tables.

Have you seen/hear of this?
Any solutions?

thx
w

Hi goss,

Is it possible that your Access DB needs to be opened?

Just a guess & the only thing I can think of.

Hi noboffinme,

I think it may have something to do with Trusted Connection ion ODBC Connection String instead of passing the UserID and pwd may be required since a table linked back to SQL Express
I'll tinker with it tonight and post an update.

Thx
w

Another thought, are you set up as an administrator user status?

OK, to backtrack a bit, you have installed SSE & MS Office 2013 on your PC.

I Linked from Access to a table in SSE - works
I created a query on the linked table - works
I saved and closed Access
I opened a new excel workbook
I attempted to use the query as my data source for a new PivotTable and received this error

You linked Access to SSE so you can see the Access tables in SSE
You created a query in Access that is linked to SSE, can you create the query in SSE using Access?
How is Excel connecting to SSE? ODBC to where...?

I could be wrong in what I think may be happening, but thought I'd ask some more questions and offer my view.

I usually connect Excel to SQL Server to access any tables & queries sitting in SQL Server.

Can you upload your Access query to SQL Server & access it through SQL Server to Excel?

I usually connect Excel to SQL Server to access any tables & queries sitting in SQL Server.

Can you upload your Access query to SQL Server & access it through SQL Server to Excel?

Glad I could help.