Redefining pivot table source
 

Good day all.

I have a pivot table within an Excel file that picks up its data from an
Access database located
in the same directory as the Excel file. I am attempting, unsuccessfully, to
change the source that the Excel file goes to when it picks up the data.
(Essentially, I am moving a folder from the C drive to the D drive.

The Access file is called "Figs2.mdb", the table within the Access file is
called "figstb" and the columns I am trying to retrieve from the table are
"Name", Location", "Date" and "Amount".

Code snip.......

----
Workbooks.Open ThisWorkbook.Path & "\NewPV.xls"
Set wb = ActiveWorkbook
x = ThisWorkbook.Path
y = x & "\Figs2.mdb"
wb.Sheets(1).PivotTableWizard SourceType:=xlExternal,
SourceData:=Array("SELECT figstb.Name, figstb.Location, figstb.Date,
figstb.Amount" & Chr(13) & "" & Chr(10) & "FROM `" & x & "\Figs2`.figstb
figstb"), _
Connection:=Array("ODBC;DSN=MS Access Database;DBQ=" & y & " ;FIL=MS
Access;")
----

Any help gratefully received. Thanks in advance.