2007 Pivot Tbl to Access Conversion Issues

[LindaSD]

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

 

[Geeta Sonawane]

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:

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/tutorial...60-39a86ccab5d6/using-vsto-addin-to-auto.aspx

 

[Humble Bee]

I know this question was asked a couple years ago but I just ran into this issue myself and hadn't been able to find the solution online. So I figure to help anyone else out in the future that does come across this issue, I'll post the solution I did find on my own.

It turns out that Microsoft 2007 & 2010 make PivotTables much more complicated than necessary. If you created a PivotTable using a Microsoft Access Database and want to change the connection to a different database (i.e. you set up the test file using a dummy database to prevent damaging your data and now want to connect to the actual data), follow these steps (written in laymen's terms because I'm not an expert and won't assume anyone else is either):

1. Click anywhere on the PivotTable you created in Excel
2. In the Excel Ribbon, click on the Options tab under the PivotTable Tools section that is now highlighted in red at the top of your window
3. Click on the Change Source Data drop down box in the Data section
4. Click on "Change Data Source..."
5. Click on the Choose Connection button
6. Click on the Browse for More... button at the bottom left corner of the pop up box
7. Click on the New Source... button at the bottom right corner of the new pop up box (it's just above the Open and Cancel buttons, to the right of the File Name box)
8. Select "ODBC DSN" in the options box and click Next
9. Select "MS Access Database" in the options box and click Next
10. Browse to your file location using the Drives drop down box at the bottom of the pop up window and the Directories options box just above that
11. Select your file when it appears in the Database Name options box on the left, and click OK
12. Select the appropriate table or query in the Available Tables and Columns option box on the left of the new pop up box
13. Click the ?>? arrow in the middle of the pop-up box to add the table to the Columns in your query option box on the right, and click NEXT until you reach the Query Wizard - Finish screen. Then click FINISH

I hope this helps.

 

[ggonza1595]

That was a great set of detailed instructions on how to change the data source. Been having that issue since Office 2007 and decided to finally take care of it once I upgraded to Office 2010. Thanks a bunch!

 

[mykie]

HI Guys,

Here is my first post and I am hoping you can help.

I've been frustrated all day by this "You cannot choose a connection file that is incompatible
with the existing connection" much like the first post. I complete reporting using the same method in excel every month however today I continuously get this error and am unable to update my pivot tables for some reason. How do I check the current connection so that I can ensure mine is the same and will be picked up excel and in turn update my spread sheet???

Cheers,
Mykie

 

[nnadkarni]

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

 

[beertrollruss]

This is a great help and time saver. Very well written and easy to follow.