Change Data Source for Pivot Table?

Thanks for your time and help.

Applications: Excel 2000 and Access 2000

Problem: I'm working with an Excel Pivot Table that is linked to an Access table for its data source.

How do I change the data source to a table in another access database so that I can update my excel pivot table?

thanks again, ths78

You could use programming to change the source. For example:

'====================
Sub PivotChangeSource()
With ActiveWorkbook.PivotCaches(1)
.Connection _
= "ODBC;DSN=MS Access Database;" _
& "DBQ=C:\SalesDB.mdb;DriverId=25;" _
& "FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
.CommandText _
= "SELECT *" & Chr(13) & "" & Chr(10) & _
"FROM `c:\SalesDB.mdb`.qryInvoices"
End With
End Sub
'==========================

ths78 wrote:
> Thanks for your time and help.
>
> Applications: Excel 2000 and Access 2000
>
> Problem: I'm working with an Excel Pivot Table that is linked to an
> Access table for its data source.
>
> How do I change the data source to a table in another access database
> so that I can update my excel pivot table?
>
> thanks again, ths78
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html