Change pivot table source data - Access table

[hotbread]

I have two tables in an Access database which are identical in structure (ie same fields, data types etc), but Table1 contains 'preliminary' data, and Table2 contains 'final' data.

I have a number of Excel workbooks containing pivot tables that all use Table1 as their data source. Is there any way using VBA to switch from Table1 as the data source to Table2, and vice versa? For example, one person may want to use the pivot tables to view preliminary data (from Table1) and another may want to use the same pivot tables to view final data (from Table2). I was able to retrieve the SourceData SQL string from the pivottable, but this is read only - is it possible to change the SQL string programmatically, or make the change some other way?

 

[hotbread]

OK... well, it took me a long time, but I eventually found a way to use the SourceData property to change from one table to another. It wasn't just read-only like I initially thought. Combined all elements of the array into one temporary string, replaced all instances of the Table1 reference to Table2, then divided the string into array elements, each no longer than 255 characters. And it worked.