User access to queries and reports different from mine (1 Viewer)

[Erin M 2021]

Good morning,

I'm looking for guidance on why user access would be different from mine. Other user is able to update the linked .MDB file and sees the updated data in the tables within .accdb file but then the built query shows up blank. Why would this be occurring?

When I run them, everything is fine.

Thanks in advance.

 

[ebs17]

These instructions could be given to you by the developer of the application. He should have an idea of what he has implemented.

 

[Erin M 2021]

These instructions could be given to you by the developer of the application. He should have an idea of what he has implemented.

I inherited but also have modified since. The original developer is not accessible.

 

[ebs17]

I ... also have modified since

Now you want to be told by a third party what you have changed and what might have been wrong, so with a wave of a magic wand?

You can analyze a specific query. Do that.

You cannot open/update an external file if it is opened exclusively by someone. Check that.

 

[Erin M 2021]

Now you want to be told by a third party what you have changed and what might have been wrong, so with a wave of a magic wand?

You can analyze a specific query. Do that.

You cannot open/update an external file if it is opened exclusively by someone. Check that.

Thanks, like I said, each of the tables used in the query are operable. So what exactly am I looking for in the query set up that would indicate the issue?

 

[Pat Hartman]

Has the other user ever been able to update? If so, something changed.

If this is a new user, the likely issue is with Windows permissions. In order for the user to update the database he needs update permission to the database, In order to share the database, he needs create and delete permissions so that when he opens the BE, Access can create the lock file and when he closes the BE, Access can delete the lock file.

If you are using the old Access security, you need to add the user to that security and assign the correct permissions.

And finally, users should not be interacting directly with queries. Users should only ever access forms and reports. That is how YOU protect the company's data. Once you let a user run amok and update using queries, you have no control over what they are doing.

 

[Erin M 2021]

Has the other user ever been able to update? If so, something changed.

If this is a new user, the likely issue is with Windows permissions. In order for the user to update the database he needs update permission to the database, In order to share the database, he needs create and delete permissions so that when he opens the BE, Access can create the lock file and when he closes the BE, Access can delete the lock file.

If you are using the old Access security, you need to add the user to that security and assign the correct permissions.

And finally, users should not be interacting directly with queries. Users should only ever access forms and reports. That is how YOU protect the company's data. Once you let a user run amok and update using queries, you have no control over what they are doing.

User can update other Access databases, but not this one.

 

[Pat Hartman]

Are they in the same folder?

 

[Erin M 2021]

Are they in the same folder?

Somewhat but the final folder, no. As an example, here are the file paths:
User unable to run queries here: Reporting Folder\Access Reports\Progress Report\.accdb file
User able to run queries here: Reporting Folder\Access Reports\Development Report\.accdb file

 

[Pat Hartman]

Did you check their access to the folder that does not work????? Can they run the database in the second folder if you copy it to the first folder? It is important to eliminate variables and we do that one at a time.

Please try to reply to each point so we don't have to keep repeating questions. Go back to #6. The questions are designed to narrow down where the problem is. If we don't know where the problem is, we have no shot at fixing it.

Initially you said you had .mdb files? Now you are referencing .accdb files. Are you using both or only .accdb files?

 

[The_Doc_Man]

OK, a quick course in Windows and Access permissions. Since AC2003, DB-level user access control has been discontinued. Therefore, there are only two major ways to achieve the effect you are seeing.

1. IF you have a switchboard or dispatcher form that loads initially and examines information about the current user, that form can assert various control settings to limit or outright block access to certain options. IF there is no such form, then the problem has to be related to Windows file permissions, which can also control accessibility.

2. Windows file settings must be "MODIFY" for the database files AND for the folders in which those database files are stored. "MODIFY" is the broad-brush setting, of which there are only a few. The advanced/detailed settings are 22 in number. These will affect the entire file in that all objects will probably exhibit the same behavior. I.e. can change one? Then can change all. Cannot change one? Then probably cannot change any. The permissions of a given user can be determined easily enough.

For a given user that you wish to evaluate: Using Windows File Explorer window, find the folder containing the Front-End file. (Might also need to find the Back-End file, depending on specifics.) Found that file? Then...

1. Right-click the file to expose the drop-down list.
2. Select Properties >> Security
3. Select Advanced
4. In the list that shows up, find the username and look at the permission for that file

You may have to repeat this process for each user and each file that is part of the database. All database files and the folders they are in MUST have at least MODIFY permissions. There is also the matter that the TRUST CENTER might see those two folders differently.

 

[Erin M 2021]

Did you check their access to the folder that does not work????? Can they run the database in the second folder if you copy it to the first folder? It is important to eliminate variables and we do that one at a time.

Please try to reply to each point so we don't have to keep repeating questions. Go back to #6. The questions are designed to narrow down where the problem is. If we don't know where the problem is, we have no shot at fixing it.

Initially you said you had .mdb files? Now you are referencing .accdb files. Are you using both or only .accdb files?

No change when moving it to the folder that they can update. They can modify other .accdb files. So I'm not sure I follow how this would be a Windows permissions issue.

How do I confirm if the file is using old Access security?

Finally, I export .mdb files from a managed database and use them as my data sources in my .accdb file to create reports. When updating the .mdb files, I am also updating the data captured in the tables of my .accdb file.

Please let me know if I've missed anything.

 

[Erin M 2021]

OK, a quick course in Windows and Access permissions. Since AC2003, DB-level user access control has been discontinued. Therefore, there are only two major ways to achieve the effect you are seeing.

1. IF you have a switchboard or dispatcher form that loads initially and examines information about the current user, that form can assert various control settings to limit or outright block access to certain options. IF there is no such form, then the problem has to be related to Windows file permissions, which can also control accessibility.

2. Windows file settings must be "MODIFY" for the database files AND for the folders in which those database files are stored. "MODIFY" is the broad-brush setting, of which there are only a few. The advanced/detailed settings are 22 in number. These will affect the entire file in that all objects will probably exhibit the same behavior. I.e. can change one? Then can change all. Cannot change one? Then probably cannot change any. The permissions of a given user can be determined easily enough.

For a given user that you wish to evaluate: Using Windows File Explorer window, find the folder containing the Front-End file. (Might also need to find the Back-End file, depending on specifics.) Found that file? Then...

1. Right-click the file to expose the drop-down list.
2. Select Properties >> Security
3. Select Advanced
4. In the list that shows up, find the username and look at the permission for that file

You may have to repeat this process for each user and each file that is part of the database. All database files and the folders they are in MUST have at least MODIFY permissions. There is also the matter that the TRUST CENTER might see those two folders differently.

How do I confirm if the form is a switchboard? I do not believe it is user specific.

You mention a front-end file. I do not believe front/backend files/ split databases were created. I am only aware of the one I build in. User does have exact same securities

 

[The_Doc_Man]

How do I confirm if the form is a switchboard? I do not believe it is user specific.

You mention a front-end file. I do not believe front/backend files/ split databases were created. I am only aware of the one I build in. User does have exact same securities

If you and the user share the file in a commonly accessed folder then you are operating at risk anyway due to file locking considerations.

A "switchboard" form will offer you buttons or other controls to select a particular action that (usually) activates another form or exposes a bunch of previously hidden controls on the current form. IF the users can see raw tables, queries, and forms & reports by their actual database object names then no, it isn't a switchboard.

 

[Erin M 2021]

If you and the user share the file in a commonly accessed folder then you are operating at risk anyway due to file locking considerations.

A "switchboard" form will offer you buttons or other controls to select a particular action that (usually) activates another form or exposes a bunch of previously hidden controls on the current form. IF the users can see raw tables, queries, and forms & reports by their actual database object names then no, it isn't a switchboard.

Thanks for the reply. These are not switchboards. The other user and I would not be in the space at the same time. The other user is being trained as backup to generate and modify the reports built.

 

[Erin M 2021]

If you and the user share the file in a commonly accessed folder then you are operating at risk anyway due to file locking considerations.

A "switchboard" form will offer you buttons or other controls to select a particular action that (usually) activates another form or exposes a bunch of previously hidden controls on the current form. IF the users can see raw tables, queries, and forms & reports by their actual database object names then no, it isn't a switchboard.

Good morning, wanted to follow up to see if you had other testing ideas. thanks

 

[Erin M 2021]

No change when moving it to the folder that they can update. They can modify other .accdb files. So I'm not sure I follow how this would be a Windows permissions issue.

How do I confirm if the file is using old Access security?

Finally, I export .mdb files from a managed database and use them as my data sources in my .accdb file to create reports. When updating the .mdb files, I am also updating the data captured in the tables of my .accdb file.

Please let me know if I've missed anything.

Good morning, wanted to follow up to see if you had any other insights. @Pat Hartman