Add password to access report/limit access to users for access reports

[Sf1802]

Hello, i wanted to check if I can add password to an access report. Example, I have 40 officers in the company, each officer will see their own report when they key in their name and password.


My access version (2016) cannot run macro codes, is my company policy to block the macro from running. Hence, not sure if this can be done using queries or other functions? Any advices will be grateful.

[June7]

Is this a split multi-user db?
Surely there is code running in this db.
If you can't run VBA or regular macros, can you run embedded macros or Data Macros?

[ranman256]

Then you have crippled yourself by not running macros.
Thats how Access works.

[CJ_London]

My access version (2016) cannot run macro codes, is my company policy to block the macro from running

can you clarify this. It is standard MS policy to require users to enable vba/macros - be it access/excel/word whatever. Are you sure you aren't confusing company policy with MS policy?

each officer will see their own report when they key in their name and password

this implies your are using vba/macros to manage this process unless you do no verification at all

with regards your requirement, you would need to include a field to identify the user allowed to see the report against each country or person or department, whatever. Then your report rowsource would have a link to the who's logged in table e.g.

SELECT *
FROM tblCountries INNER JOIN tblWho ON tblCountries.UserName=tblWho.UserName

The table would need to be local (on the FE) and only every contain one record.

It won't be particularly secure, they can enter someone else's name for example. You could use the password - but people will forget, tell others etc, and could be quite difficult to maintain

[isladogs]

So you are allowed to write VBA code? This is far more powerful than using a macro so I would say the restriction isn't a real issue.

Do your users login to the database?
If so its easy to achieve this by filtering the report to the logged in user
You can also restrict certain action depending on user permission levels
e.g. have certain items as READ ONLY on not visible to standard users but visible/editable to admin users

[June7]

I did a test by setting db Trust Center Options to "Disable all macros with notification". The result was VBA would not run but an embedded macro would.

So exactly how is company blocking code execution?

[Sf1802]

Dear all, thank you for your replies.
Actually I’m still very new to using access DB, there are alot of things that I’m still not sure, so please pardon me, I might not have the answers to all of your questions.
@June7: I only know that I can’t open any access files if there are macros, it will prompt an error message that file cannot be opened. Hence, for the database that we have created, we are only using forms, queries and reports functions. But whenever I open my DB I will still need to click on the “enable macro” pop out message before I can access my DB.
@Isladogs: what is the difference between macro and VBA, I have thought that they are somewhat linked?
@Ajax: my users don’t login to the database, but the database is saved in our shared point, where everyone need to login to the server.

[orange]

sf1802,

Do you have a DBA in your area/org with whom you can discuss MS Access/database?
Or a technical support person to discuss/understand the technical set up involved?

You may want to watch some related Steve Bishop videos
Creating a Login screen
How to Manage User Access

Let us know how this progresses.

Welcome to the forum and good luck with your research and learning.

[Sf1802]

Hello, unfortunately I don’t any support personnel that I can check with. I will take a look at the resources that you have shared, thank you!

[orange]

sf1802,

If you have a company with 40 officers, then you have some sort of viable business. If the info in the database is important to your business and to each of the officers, then it follows that managing that data in a prudent fashion is a key to the success of the business. But it also seems that this may not be the mode of operation in your business.

How did this database come into existence?
Since you don't have any support staff, nor access to technical personnel, who manages the technical aspects of your IT environment and specifically this database. Unlike the simplicity of the marketing literature, database (planning, design, operation and maintenance) is not trivial. It requires some standards and procedures, and human communications especially when 40 users are involved.

This is not meant to dissuade you in any way. I'm just trying to be constructive in getting you to understand the environment in which you find yourself. Perhaps you could overview your business/company in general terms to help readers put your post and your position into better context.

There are several resources in the links in my signature that you may find helpful/informative.

Good luck.

[CJ_London]

But whenever I open my DB I will still need to click on the “enable macro” pop out message before I can access my DB.

you need to do this for any file first time it is opened (standard MS protocol) which contains macros - including .xlsm files. You can overcome this by doing the following

1. Open Access
2. go to File>Options>Trust Centre
3. click on Trust Centre Settings
4. select trusted locations
5. tick 'allow trusted locations on my network
6. you may need to untick disable all trusted locations
7. select Add new location and browse to the directory the front end is stored in and select it
8. click OK to save

Now any db you put in that location should open without the enable macros prompt

From the tone of your responses, it sounds like the db is not split and each user does not have their own copy of the front end. If this is the case you run a high risk of corruption.

[Sf1802]

Hello, just to give some context on how this DB request came about. The initial dataset actually comes from excel. I have a list of projects that the company has.
The excel spreadsheet keep tracks of the monthly revenue and expenses of each project as well as the no. of utilisation days of each officer. However, different officers are involved in different projects.
I have a list that shows the total utilisation days of each officer with breakdown of the no. of days and the project title. Also each officer will have different minimum utilisation days that they need to meet, so from the list I can do comparison by using a pivot table. But now the management wants to each officer to only see their own utilisation days. And different managers to be able to see their own team’s utilisations. So there are various reports that I have to churn out and to create access to the different reports. So I thought a DB will be more suitable to do this. But if u know that excel can also do this, please let me know, cox I guess the process will be less tedious, in view that my dataset is it already in excel format. Thank you!

[CJ_London]

And different managers to be able to see their own team’s utilisations

that is mission creep. Basically the issue is hiding the entire dataset and only exposing the relevant data to a user. In Access easy to do, Excel not so easy, but either way will require vba code/macros to identify the user. And if they are not allowed then I don't see any way you can do it other than using excel and copy/pasting the individual data to separate workbooks.

[Sf1802]

Hi Ajax, I’m not very good at access, perhaps I will try to see if I can do up a simple login page with the resources that @orange has shared and see if I’m able to run it.

[orange]

sf1802,

Did you watch the Steve Bishop videos suggested in post #8? They should be helpful to the issue of "who is permitted to see what". If you have more basic questions on Access queries, forms and/or vba etc., then post them here before getting too deeply into "coding". For your own benefit, you may wish to make an list of things that you plan to do.
For example, start with a specific objective and work /focus on that. As Ajax advised -that is mission creep. And that has been the downfall of many projects. If the boundaries of the project are not set and agreed upon, then "new features" or "different expectations from project initiators and project team" can extend/expand the project and cause delays, changing priorities, loss of focus and confusion. Beware of mission creep/scope creep. Make a plan, get it agreed to and focus on the project with firm requirement.

Good luck. Keep us informed of your progress.