Retrieving data in pivottable from Access / SQL server

[Olaf NC]

Following problem:
I created a pivottable in compatibility mode in Excel 2007 based on an
access file (also access 2000 format). The Access file is used to collect
data from a SQL server and combine the data.

Updating the pivottable is running smooth in Excel 2007.
If I open the Excel file on an Excel 2003 machine the pivottable can be
adjusted, but refreshing data gives an error "unable to connect to the
server".
If I open the Access file with the Excel/Access 2003 machine, the SQL data
is collected as it should be.
I tried to make a new pivottable in Excel 2003 on the Access database, but
this resulted in the same error.

Might this be a security setting in Access not allowing Excel to get Access
to update the SQL data?
The SQL server requests a username and password, this screen is not
displayed (might be a source of the problem?)

Who can help ????

 

[Shane Devenshire]

Hi,

I don't know the problem but you can have Excel remember the password,
In Excel, with the pivottable selected choose Data, Properties, Definition
tab and check Save password.

I'm not sure this will help but its worth a try.

 

[Olaf NC]

"HI,

Thanks for the possible solution, trying it did unfortunately not solve the
problem.
Problem remains --> "ODBC connection to 'xxxxx' fails"
--> "Problems obtaining data"
Any other options?

Thanks in advance,
Olaf

 

[Rubble]

Hi -
Unfortunately I do not have an answer - but what may be a related quirk that
might help in your search.

When I try to create a pivot table in Excel 2003 connecting to an Access
2007 file I try to create a new connection using the driver with the *.accb
extension. Initially everything seems like it wants to work (like picking
the tables and fields I want to link to); however, when it actually tries to
pull data it gives me the connection error stating that it cannot find the
*.mdb file (even though I had already browsed to the Access 2007 file with
the *.accb extension).

Maybe when Excel 2003 tries to grab data from Access 2007 using the access
driver it jumps to the default extension? I can connect to Access 2007 using
the ACE driver within VBA - but not a direct connection (yet).

Maybe your issue is related to my issue - or maybe not. My issue seems to
be an Office 2007 driver issue as it relates to Excel 2003.