P
(PeteCresswell)
A guy came to me for advice today.
Smart guy, sophisticated MS Access user, but not an MS Access
developer; has probably forgotten more about MS Excel than I'll
ever know.
He's been tasked with creating a way to bring together
information from 70 people into a single place for reporting
purposes. Reporting by whom? It's not 100% clear - note
security requirement later on.
Each person's information can be reduced to a single row.
The zinger is a requirement that no person can see anybody's
information except theirs. i.e. Row-level security. This
seems tb the deal breaker on various obvious (and much
simpler) solutions.
What I came up with was this:
--------------------------------------------------------
1) Create 70 Excel workbooks: one for each person.
Each workbook is PW-protected with a different PW.
Each workbook is named for the person it serves.
All workbooks live in a common LAN directory.
2) Each workbook has a visible sheet that's beautified
and protected as needed to facilitate data entry by the
user and keep them on the straight-and-narrow path.
i.e. multiple rows may be used.
3) Each user updates their own .XLS as they wish.
4) Each workbook also has an invisible sheet with
a single row - each of whose cells are Referenced
to the appropriate cell in the visible sheet.
5) It all comes together in an MS Access application
which lives either somewhere that only a certain
person can get to it... or lives in the LAN directory
where all the .XLS files live.
In the MS Access application:
- There are 70 links: one to each .XLS'
invisible sheet.
- There is a single monster union query that
brings together each of the 70 sheets.
- Reporting is done via various
queries/reports/screens whose root source
is the union query.
--------------------------------------------------------
Security requirement seems tb met...more or less...
No VBA coding expertise required.
No knowledge of the MS Access security model needed.
No front-end-back-end issue.
No deployment issues bc everybody has and knows how to use Excel.
In short, it all falls within the existing expertise of the guy
who will be tasked with developing it.
Yes, there seems tb a built-in contradiction vis-a-vis the
security: if there's going tb reporting, how come they don't want
users' to see each other's raw data?
I'm guessing the answer is something about some fields being
visible and others not.... but still, the union query can see it
all.... so maybe the security aspect needs more examination.
But it's not my project....
Does anybody see any errors in the overall approach?
Better approaches that don't need VBA coding expertise or a
knowledge of the MS Access security model?
Smart guy, sophisticated MS Access user, but not an MS Access
developer; has probably forgotten more about MS Excel than I'll
ever know.
He's been tasked with creating a way to bring together
information from 70 people into a single place for reporting
purposes. Reporting by whom? It's not 100% clear - note
security requirement later on.
Each person's information can be reduced to a single row.
The zinger is a requirement that no person can see anybody's
information except theirs. i.e. Row-level security. This
seems tb the deal breaker on various obvious (and much
simpler) solutions.
What I came up with was this:
--------------------------------------------------------
1) Create 70 Excel workbooks: one for each person.
Each workbook is PW-protected with a different PW.
Each workbook is named for the person it serves.
All workbooks live in a common LAN directory.
2) Each workbook has a visible sheet that's beautified
and protected as needed to facilitate data entry by the
user and keep them on the straight-and-narrow path.
i.e. multiple rows may be used.
3) Each user updates their own .XLS as they wish.
4) Each workbook also has an invisible sheet with
a single row - each of whose cells are Referenced
to the appropriate cell in the visible sheet.
5) It all comes together in an MS Access application
which lives either somewhere that only a certain
person can get to it... or lives in the LAN directory
where all the .XLS files live.
In the MS Access application:
- There are 70 links: one to each .XLS'
invisible sheet.
- There is a single monster union query that
brings together each of the 70 sheets.
- Reporting is done via various
queries/reports/screens whose root source
is the union query.
--------------------------------------------------------
Security requirement seems tb met...more or less...
No VBA coding expertise required.
No knowledge of the MS Access security model needed.
No front-end-back-end issue.
No deployment issues bc everybody has and knows how to use Excel.
In short, it all falls within the existing expertise of the guy
who will be tasked with developing it.
Yes, there seems tb a built-in contradiction vis-a-vis the
security: if there's going tb reporting, how come they don't want
users' to see each other's raw data?
I'm guessing the answer is something about some fields being
visible and others not.... but still, the union query can see it
all.... so maybe the security aspect needs more examination.
But it's not my project....
Does anybody see any errors in the overall approach?
Better approaches that don't need VBA coding expertise or a
knowledge of the MS Access security model?