R
rfuscjr via AccessMonster.com
We automate reports using a combination of Access and Excel with Access sort
of running the show. We start by defining queries to build the table
required for an Excel pivot. Next we run the queries to build the table.
Next we create the Excel file (we call it a template) that points directly to
the Access table. Finally we build a button in Access that invokes the
queries, opens the Excel template, refreshes the pivots therein and writes
the Excel file to a specified location. The user picks up the file from
there once we have refreshed it for the month.
As users get more sophisticated (and I use that term loosely), I fear someday
they will realize they can refresh a pivot. I do not want them doing tis for
two reasons:
1) Many tables are large and I empty them in Access after the Excel sheet is
written. If they hit refresh its bye bye data.
2) Some Access table are reused. Example: We create a report for each dept.
We run dept 1, refresh the template for dept 1, write the report for dept 1.
We then continue with dept 2, 3 4 etc etc. If the user hits refresh at an
inopputune time, well they might get another dept's data.
So I want some security. I am not sure if I should be somehow securing the
data base OR if there is a simple way in Excel to simply prohibit the user
from refreshing the pivot.
Thoughts, ideas....Thanks!
of running the show. We start by defining queries to build the table
required for an Excel pivot. Next we run the queries to build the table.
Next we create the Excel file (we call it a template) that points directly to
the Access table. Finally we build a button in Access that invokes the
queries, opens the Excel template, refreshes the pivots therein and writes
the Excel file to a specified location. The user picks up the file from
there once we have refreshed it for the month.
As users get more sophisticated (and I use that term loosely), I fear someday
they will realize they can refresh a pivot. I do not want them doing tis for
two reasons:
1) Many tables are large and I empty them in Access after the Excel sheet is
written. If they hit refresh its bye bye data.
2) Some Access table are reused. Example: We create a report for each dept.
We run dept 1, refresh the template for dept 1, write the report for dept 1.
We then continue with dept 2, 3 4 etc etc. If the user hits refresh at an
inopputune time, well they might get another dept's data.
So I want some security. I am not sure if I should be somehow securing the
data base OR if there is a simple way in Excel to simply prohibit the user
from refreshing the pivot.
Thoughts, ideas....Thanks!