Pivot Table connected to another Worksheet Table that is linked to Access DB

Question

This is the table that is located in a password protected workbook, and it connected with a connection string to an Access DB Table. The last 2 columns on the right, are calculated fields using Vlookup formula to another worksheet in this protected workbook.

I want to have a Pivot Table in another workbook (not protected), that will be able to connect to this Table, and have updated values every time the original table is modified (the Table in the attached screen-shot is growing every month once new records are added to the Access DB Table).

Is it possible ? I don't want to use the option of referencing to another sheet and select a huge number of cells. Is there a way to implement such a connection with Excel-VBA ? that might allow me also to auto-enter the password in the background and not visible to the user.

Answer 1

You should be able to connect that workbook to the Access DB Table (https://support.office.com/en-us/article/Connect-an-Access-database-to-your-workbook-a3d6500c-4bec-40ce-8cdf-fb4edb723525?ui=en-US&rs=en-US&ad=US&fromAR=1).

Once that is done and the workbook has access to the data by inputting the credentials, you should be able to generate a pivot table in the now connected workbook that reads the Access DB table (https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-external-data-db50d01d-2e1c-43bd-bfb5-b76a818a927b).