Lo solucione de la siguiente forma:
Cree una macros que actualizara el origen de datos de todas las tablas dinámicas que contiene las hojas del reporte.
Código:
Range("A3").Select
ActiveSheet.PivotTableWizard _
SourceType:=xlExternal, _
SourceData:=Array("SELECT * FROM [" + Ruta + "\" + Archivo + "].Detalle"), _
Connection:=Array( _
Array("ODBC;DSN=MS Access Database;DriverId=25;"), _
Array("DBQ=" + Ruta + "\" + Archivo + ".mdb;"), _
Array("DefaultDir=" + Ruta + ";"), _
Array("FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;") _
)
Las variables Ruta y Archivo, como su nombre lo indica, contiene la ruta del Access (que es la misma donde esta el reporte) y el nombre del Access (el mismo que el del reporte)
Código:
Dim Ruta As String
Dim Archivo As String
Ruta = ActiveWorkbook.Path
Archivo = ActiveWorkbook.Name
Archivo = Mid(Archivo, 1, Len(Archivo) - 4)