Basic question about database design (splitting/security)

[Guest]

SORRY in advance for such a basic question, however I just need some guidance
if possible. I use Access 2002 and have about average skill level creating
tables, forms, queries, reports, but very little experience using security
and/or splitting databases.

Management needs me to provide them statistics and/or data elements each
month for various regions. I already have processes in place to get the data.

My question is: What is the best way to present this information to
management?

Concerns: 1) I want to keep the data on a shared drive 2) I want the data
to be secure so that no one can change it. 3) I want the design elements of
my database to be secure 4) For now I want to give them the data in a format
that I choose, but I wouldn’t mind eventually giving them access to create
their own queries/reports.

Should I split my database? If I do that, can I secure both parts using
Access security? Can I secure some ‘canned’ reports for them to use? Any
other ideas?

Any assistance you can provide would be greatly appreciated.

 

[Douglas J Steele]

If multiple users are going to be accessing the data, then there's no
question: you need to split the application into a front-end and back-end.
Only the back-end should be on the server: each user should have his/her own
copy of the front-end, preferably on their hard drive.

Applying User-Level Security will allow prevent them from getting to the
data, except through queries that you've predefined for them.

Giving them access to create their own reports on your queries shouldn't be
a problem. Letting them create their own queries is also possible.

 

[Keith W]

You've identified two unrelated issues here - presenting data and securing
your application. Comments in-line.

What is the best way to present this information to
management?

One of my applications is used to deliver weekly progress metrics via an
Excel spreadsheet/graphs to management. ODBC links to the spreadsheet files
are used by update queries to provide the data to Excel.

Concerns: 1) I want to keep the data on a shared drive 2) I want the data
to be secure so that no one can change it. 3) I want the design elements
of
my database to be secure 4) For now I want to give them the data in a
format
that I choose, but I wouldn't mind eventually giving them access to create
their own queries/reports.

Should I split my database? If I do that, can I secure both parts using
Access security? Can I secure some 'canned' reports for them to use? Any
other ideas?

If the application is to be deployed as "multi-user" then you *must* split
the application to avoid corruption. You must also give each user their own
copy of the front end. To secure the design elements you must invoke the
built-in user-level security, including the disabling of the F11 and SHIFT
bypass keys.

Pre-defined reports can be accessible to the users via a form with a pick
list. You won't be able to let your users create their own reports from a
secured application since, by default, they will not be able to see the db
window.

What you have here is an extremely tall order for someone who is "getting
started". For starters I'd recommend looking at how to link to Excel
spreadsheets (in the help) and looking into user-level security. There's a
link to the security FAQ on my website. Be warned, the FAQ has no filler so
don't be tempted to skip any steps. Also, work on a dummy file - don't lock
yourself out of an important application!

Good luck.
Keith.
www.keithwilby.com