Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 2007 Pivot Tbl to Access Conversion Issues

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


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
2007 Pivot to Access LindaSD Excel Discussion (Misc queries) 1 August 14th 08 06:19 PM
Date Format Issues Access to Excel RC Excel Discussion (Misc queries) 3 June 13th 06 11:28 AM
Potential CSV/Excel Conversion Issues? Erin Excel Discussion (Misc queries) 2 May 15th 06 10:30 PM
datetime format issues when linking access to excel Season Excel Discussion (Misc queries) 0 December 6th 05 05:52 PM
Access Excel Linked Text and Number Issues Scott Excel Discussion (Misc queries) 2 October 4th 05 09:24 PM


All times are GMT +1. The time now is 09:30 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"