Change of table name in access where Excel obtains pivot table inf

[Guest]

Hi,

I have change the table name (from table A to Table B). In Excel I normally
use the 'Refresh Data' icon to refresh a pivot table with updated data. When
I try to update it now I get the error [Microsoft][ODBC Microsoft Access
Data] The Microsoft Jet database engine cannot find the input table or query.
Make sure it exists and that its name is spelt correctly.

I know the 'easy' option is to rename the table back to 'Table A' but alas I
can't.

How do I go about redirecting the source.

 

[Guest]

Hi again,

I've figured it out but if anyone knows a better way please advise...

Open the Wizard and click on 'Back' to go 'PivotTable and PivotChart Wizard
and click on the 'Get Data...' button.

You are prompted with the error [Microsoft][ODBC Microsoft Access. The
Microsoft Jet database engine cannot find the input table or query. Make
sure it exists and that its name is spelt correct.

I can't recall how but you get directed to the Microsoft Query Application
then click on view SQL and change it to the new path and/or table names.

Done!

 

[Harlan Grove]

I have change the table name (from table A to Table B). . . . ....
I know the 'easy' option is to rename the table back to 'Table A'
but alas I can't.

....

Is the name 'Table A' still in use in this database? If not, you could
create a QUERY named 'Table A' defined simply as

SELECT * FROM [Table B];

Then the query in Excel would use the query named 'Table A', and thus
indirectly 'Table B'.

 

[Guest]

I've overcome that problem but am now experiencing a new problem.

I have one workbook with multiple pivot tables on it in seperate worksheets.
When I redirect the where to find the pivot table source date (from one LAN
drive to another) OR I create new worksheets (with pivot tables) within the
workbook the size of the file increases greatly (eg from 15MB to 60MB). Then
when I try to update the next sheets source data I get the message 'Not
enough memory'. I can 'ok' this a few times and then I get the message
'Continue without undo'. I say 'Ok' here but then nothing happens and the
query is not saved.

Any ideas?



--
Andrew

I have change the table name (from table A to Table B). . . . ....
I know the 'easy' option is to rename the table back to 'Table A'
but alas I can't.

....

Is the name 'Table A' still in use in this database? If not, you could
create a QUERY named 'Table A' defined simply as

SELECT * FROM [Table B];

Then the query in Excel would use the query named 'Table A', and thus
indirectly 'Table B'.