I have a large number of 2003 Excel Pivot Tables that connect via odbc to
2003 Access queries. I recently converted one database to 2007. I then
converted one Excel file that looks at that db to 2007. I went into the new
2007 file and set up a new Connection to look to the new 2007 Access query.
That done, I went into the first pivot table and selected the new connection
- Pivot Table Tools, Change Data Source, Use An External Datasourse, Choose
Connection button. And now I'm presented with this long list of connections.
I went into the properties and made sure I selected the connection that was
the new one to the 2007 database (.accdb). The said Open which brings back
the Change Pivot Table Data Source window with that connection listed, then
ok. I get an error "You cannot choose a connection file that is incompatible
with the existing connection." So how else besides rebuilding every single
pivot table (if I do a new pivot table it lets me select that connection) can
I change the data source? I thought this new Connection setup in 2007 was
supposed to be easier and allow for this type of change. What am I doing
wrong. The help files don't cover enough when it comes to converting and
changing the data sources. This has stopped my work and I'm not rebuilding
all my reporting files. Help! Linda

As you created new connection and you want to apply this new connection to pivottable which has already based on another connection.
Then you have to check the connection type of new and old one. if that is different then you have to create new connection as per old one like if connection type is OLEDB then you have to create another one also with same type or driver should be same.




LindaS wrote:

2007 Pivot Tbl to Access Conversion Issues
28-Aug-08

I have a large number of 2003 Excel Pivot Tables that connect via odbc to
2003 Access queries. I recently converted one database to 2007. I then
converted one Excel file that looks at that db to 2007. I went into the new
2007 file and set up a new Connection to look to the new 2007 Access query.
That done, I went into the first pivot table and selected the new connection
- Pivot Table Tools, Change Data Source, Use An External Datasourse, Choose
Connection button. And now I'm presented with this long list of connections.
I went into the properties and made sure I selected the connection that was
the new one to the 2007 database (.accdb). The said Open which brings back
the Change Pivot Table Data Source window with that connection listed, then
ok. I get an error "You cannot choose a connection file that is incompatible
with the existing connection." So how else besides rebuilding every single
pivot table (if I do a new pivot table it lets me select that connection) can
I change the data source? I thought this new Connection setup in 2007 was
supposed to be easier and allow for this type of change. What am I doing
wrong. The help files don't cover enough when it comes to converting and
changing the data sources. This has stopped my work and I'm not rebuilding
all my reporting files. Help! Linda

Previous Posts In This Thread:

On Thursday, August 28, 2008 8:05 AM
LindaS wrote:

2007 Pivot Tbl to Access Conversion Issues
I have a large number of 2003 Excel Pivot Tables that connect via odbc to
2003 Access queries. I recently converted one database to 2007. I then
converted one Excel file that looks at that db to 2007. I went into the new
2007 file and set up a new Connection to look to the new 2007 Access query.
That done, I went into the first pivot table and selected the new connection
- Pivot Table Tools, Change Data Source, Use An External Datasourse, Choose
Connection button. And now I'm presented with this long list of connections.
I went into the properties and made sure I selected the connection that was
the new one to the 2007 database (.accdb). The said Open which brings back
the Change Pivot Table Data Source window with that connection listed, then
ok. I get an error "You cannot choose a connection file that is incompatible
with the existing connection." So how else besides rebuilding every single
pivot table (if I do a new pivot table it lets me select that connection) can
I change the data source? I thought this new Connection setup in 2007 was
supposed to be easier and allow for this type of change. What am I doing
wrong. The help files don't cover enough when it comes to converting and
changing the data sources. This has stopped my work and I'm not rebuilding
all my reporting files. Help! Linda


Submitted via EggHeadCafe - Software Developer Portal of Choice
Using VSTO Add-In To Automate Frequent Excel 2007 Tasks
http://www.eggheadcafe.com/tutorials...n-to-auto.aspx

Thanks a ton dude You saved me much trouble. I thought i had lost every damn thing, about a month's worth of effort in building complicated formulas, while transitioning from excel to Access. I had assumed that by creating a dummy i will be able to test access for its limits, and then just port the live data onto it.

Thanks a ton again.

NN

THANK YOU! thank you!!!!!!!!!!!!!!!!!!