Pivot table data source, Help!!

nc · May 21st, 2002, 03:29 AM

hi all.
i m very frastrated after spending hours to solve this problem but still....
well, i am creating a pivot table in Excel. The data source is an Access query. but the data source for that Access query is a link table, which links to an SQL database on a server. I used ODBC to link the table in Access. so, when i try to get the data source for the pivot table, the error msg says "ODBC- Connection to 'Team_Health_Report'(the DSN)Failed".
so, my question is: is it i can't have the data source for Pivot table from database in which the data source is a link table (to a server)? i believe i can solve this problem if i use Import table instaed of Link table for Access but i need the data to be updated to time. Can table created using Import table be updated to time?
i am really scratching my head here. sorry for confusing all for Access and Excel but i really need your help. Thanks.

rgds, nc

Ian Mac · May 21st, 2002, 03:41 AM

Quote:

On 2002-05-21 02:29, nc wrote:
hi all.
i m very frastrated after spending hours to solve this problem but still....
well, i am creating a pivot table in Excel. The data source is an Access query. but the data source for that Access query is a link table, which links to an SQL database on a server. I used ODBC to link the table in Access. so, when i try to get the data source for the pivot table, the error msg says "ODBC- Connection to 'Team_Health_Report'(the DSN)Failed".
so, my question is: is it i can't have the data source for Pivot table from database in which the data source is a link table (to a server)? i believe i can solve this problem if i use Import table instaed of Link table for Access but i need the data to be updated to time. Can table created using Import table be updated to time?
i am really scratching my head here. sorry for confusing all for Access and Excel but i really need your help. Thanks.

rgds, nc

I'm not the hottest person at Queries, but I Query linked tables all the time, these are also linked to SQL Server. I don't have any problems.

Are you Querying directly to the Pivot Table.

The way I do it is to Query to a Data sheet and link the Pivot to that. I don't know if this is helpful?

Lee Attlesey · May 21st, 2002, 04:04 AM

Hi nc

I use exactly the same way of updating my pivot tables.

I have a large dataset in Access and created linked pivot table through an ODBC link to a query in the db.

I have found though that if you don't have full access to the specific directory of where the db is located this may not work.

I'm using MS Office '97 & NT

nc · May 21st, 2002, 09:43 PM

Hi, Ian and Lee, and all
well, i think both of u worked the way exactly i want to but i realy can't get the data. It keeps saying odbc connection to (DSN) failed. I tried to get the data source for pivot table from import table instead of link table and it works....
Lee, i am using MS Access 97 but Excel 2000. so, guys, could it be the problem? also, lee, u mentioned that if i don't have full access to the specific directory of where the db is located it may not work. what do u mean yb full access? i am not the owner of the db and i need password to view the data. in fact, the db i am connected to is a view table created from another mass db. so, how??

Lee Attlesey · May 22nd, 2002, 04:27 AM

Hi nc

Firstly, have you tried saving the password on the linked table, if you haven't this may be a reason as to why the link fails.

I'm not sure about the Excel 2000 thing as I don't use it, perhaps someone else can offer some advice?

In regard to full access, I have to connect to a db on our network, at one point I only had read only access to the directory so everytime I refreshed the pivot table, I received a message stating I had problems obtaining data. This was only resolved when my access to the directory was changed.

Have you tried creating a query from the linked table(s) and linking your pivot table to that. This is how I keep a live link with a refresh on opening the pivot table.

nc · May 22nd, 2002, 07:58 PM

hi all,
lee, my data source for the pivot table is a query created from the linked table. that's why i got the error message. also, how to save password on the linked table?
Ian, what do u mean by Query to a Data sheet and link the Pivot to that? ya, i did the query on the linked table and it has no problem. the problem is when i tried to link my pivot table in excel 2000 to the query on the linked table in Access 97, i can't get the data source. but it works with the import table created from the same db.
HOW?? WHAT's the problem?? 1 more question: data in linked table will be updated automatically on the local drive, rite? what about import table?
thanks to all in advance.

rgds,
nc

[ This Message was edited by: nc on 2002-05-22 18:59 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 21st, 2002, 03:29 AM	#1
nc New Member Join Date: May 2002 Posts: 23	hi all. i m very frastrated after spending hours to solve this problem but still.... well, i am creating a pivot table in Excel. The data source is an Access query. but the data source for that Access query is a link table, which links to an SQL database on a server. I used ODBC to link the table in Access. so, when i try to get the data source for the pivot table, the error msg says "ODBC- Connection to 'Team_Health_Report'(the DSN)Failed". so, my question is: is it i can't have the data source for Pivot table from database in which the data source is a link table (to a server)? i believe i can solve this problem if i use Import table instaed of Link table for Access but i need the data to be updated to time. Can table created using Import table be updated to time? i am really scratching my head here. sorry for confusing all for Access and Excel but i really need your help. Thanks. rgds, nc

May 21st, 2002, 04:04 AM	#3
Lee Attlesey New Member Join Date: May 2002 Location: Cambridge, UK Posts: 22	Hi nc I use exactly the same way of updating my pivot tables. I have a large dataset in Access and created linked pivot table through an ODBC link to a query in the db. I have found though that if you don't have full access to the specific directory of where the db is located this may not work. I'm using MS Office '97 & NT

May 21st, 2002, 09:43 PM	#4
nc New Member Join Date: May 2002 Posts: 23	Hi, Ian and Lee, and all well, i think both of u worked the way exactly i want to but i realy can't get the data. It keeps saying odbc connection to (DSN) failed. I tried to get the data source for pivot table from import table instead of link table and it works.... Lee, i am using MS Access 97 but Excel 2000. so, guys, could it be the problem? also, lee, u mentioned that if i don't have full access to the specific directory of where the db is located it may not work. what do u mean yb full access? i am not the owner of the db and i need password to view the data. in fact, the db i am connected to is a view table created from another mass db. so, how??

May 22nd, 2002, 04:27 AM	#5
Lee Attlesey New Member Join Date: May 2002 Location: Cambridge, UK Posts: 22	Hi nc Firstly, have you tried saving the password on the linked table, if you haven't this may be a reason as to why the link fails. I'm not sure about the Excel 2000 thing as I don't use it, perhaps someone else can offer some advice? In regard to full access, I have to connect to a db on our network, at one point I only had read only access to the directory so everytime I refreshed the pivot table, I received a message stating I had problems obtaining data. This was only resolved when my access to the directory was changed. Have you tried creating a query from the linked table(s) and linking your pivot table to that. This is how I keep a live link with a refresh on opening the pivot table.

May 22nd, 2002, 07:58 PM	#6
nc New Member Join Date: May 2002 Posts: 23	hi all, lee, my data source for the pivot table is a query created from the linked table. that's why i got the error message. also, how to save password on the linked table? Ian, what do u mean by Query to a Data sheet and link the Pivot to that? ya, i did the query on the linked table and it has no problem. the problem is when i tried to link my pivot table in excel 2000 to the query on the linked table in Access 97, i can't get the data source. but it works with the import table created from the same db. HOW?? WHAT's the problem?? 1 more question: data in linked table will be updated automatically on the local drive, rite? what about import table? thanks to all in advance. rgds, nc [ This Message was edited by: nc on 2002-05-22 18:59 ]