Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
You cannot choose a connection file that is incompatible
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
2007 Pivot Tbl to Access Conversion Issues
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
You cannot choose a connection file that is incompatible
This is a great help and time saver. Very well written and easy to follow.
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
2007 Pivot Tbl to Access Conversion Issues
THANK YOU! thank you!!!!!!!!!!!!!!!!!!
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
You cannot choose a connection file that is incompatible
THANK YOU SO MUCH!!!!! You make me look like a genius at work.
On Thursday, February 3, 2011 at 6:51:10 AM UTC-6, Humble Bee wrote: 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. On Thursday, August 28, 2008 8:05 AM LindaS wrote: 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 On Wednesday, May 05, 2010 5:56 AM Geeta Sonawane wrote: 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. Submitted via EggHeadCafe Serializing Excel data for input to any Google visualization http://www.eggheadcafe.com/tutorials...alization.aspx On Thursday, February 3, 2011 at 6:51:10 AM UTC-6, Humble Bee wrote: 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. On Thursday, August 28, 2008 8:05 AM LindaS wrote: 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 On Wednesday, May 05, 2010 5:56 AM Geeta Sonawane wrote: 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. Submitted via EggHeadCafe Serializing Excel data for input to any Google visualization http://www.eggheadcafe.com/tutorials...alization.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Pivot to Access | Excel Discussion (Misc queries) | |||
Date Format Issues Access to Excel | Excel Discussion (Misc queries) | |||
Potential CSV/Excel Conversion Issues? | Excel Discussion (Misc queries) | |||
datetime format issues when linking access to excel | Excel Discussion (Misc queries) | |||
Access Excel Linked Text and Number Issues | Excel Discussion (Misc queries) |