Changing the Datasource of a Pivot Table

[James]

Hello group:

I've got a strange one here........

I current have an Excel spreadsheet with a pivot table on it that is
currently linked by ODBC to Microsoft Access. The Access database is
being moved to SQL server, so now we have to change the pointer on the
spreadsheet. Additional fields will be added in the SQL server
database, but all existing fields in the Access side will still be
available.

When I right-click the Pivot Table and click refresh, I get the ODBC
link not found or whatever error - as I expected. When I browse to the
file DSN I set up for the spreadsheet to use, I am prompted by the SQL
Server logon dialog box, even tho the DSN file has all the correct
information. Anyway, I can supply the correct logon credentials, click
ok then I get the error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near '`'.

However, if I create a new pivottable on the same sheet, or even a
different sheet using the same procedure above, I am not prompted by
the SQL Server logon dialog box when I select my DSN file, and the
connection is made successfully.

How can I possibly fix this? I have no idea what Line 1 it is talking
about.

Thanks for any help,

James Lankford

 

[aaron.kempf]

if you're trying to do a ODBC link to sql server from excel; you're
crazy

use SQL Server Analysis Services.
it is much more flexible and 100 times faster

 

[Ken Wright]

Another fictitious reply to a non-existent post just to get a rant into the
newsgroups - Petty beyond belief.