Query is blank when other users update the source data (1 Viewer)

[Erin M 2021]

Doesn't mean it isn't security or a missing reference. Did you look for those? To look for a missing reference, open any code module on the failing PC. Go to Tools/References. Is any reference marked as MISSING?

Other user has the same 4 when logged in:

 

[Pat Hartman]

Do ALL Solicitors exist in BOTH tables?

 

[Erin M 2021]

Do ALL Solicitors exist in BOTH tables?

There were not the same list. I have added to the criteria of the Solicitor Meeting Union to only included the names in Solicitor Goals

 

[Pat Hartman]

Does it work now?

 

[Erin M 2021]

Does it work now?

Not only dos the user still not have access, it doesn't work for me with that change.

 

[Pat Hartman]

That doesn't make sense. Please post the database.

Also, is the database split? it should be.
Are you and the user sharing the same physical copy of the FE? You should not be. Each user needs his own copy of the FE that he runs from his C drive. Everyone shares the linked BE which is stored on the server.

 

[Erin M 2021]

That doesn't make sense. Please post the database.

Also, is the database split? it should be.
Are you and the user sharing the same physical copy of the FE? You should not be. Each user needs his own copy of the FE that he runs from his C drive. Everyone shares the linked BE which is stored on the server.

I'm not able to post the database. It contains secure information. It is not split as this user is to take over the role of maintaining the reports. They need the same access as myself. Can you explain why we should not share the same copy? We are both accessing it from a shared folder. Copies and backups do exist.

 

[Pat Hartman]

When you open an Access database, parts of it are loaded into memory. When the user opens the same database, parts of it are loaded into memory on their PC. You now have parts of the same physical file in memory on multiple PCs. What could possibly go wrong

We've gone through all the things that are wrong with the query you posted. Now you seem to have "fixed" the data and that broke the query for you. Makes no sense to me.

Remove the third table. Use a DLookup() to retrieve it ONCE in the load event of the report. You can then store it as a tempVar and use the TempVar in the calculation in the report.

Make sure that the data in the other two tables actually relates via the Solicitor. What happens when you run the query with just those two tables?

Did you ever remove the duplicate fields?

 

[Erin M 2021]

When you open an Access database, parts of it are loaded into memory. When the user opens the same database, parts of it are loaded into memory on their PC. You now have parts of the same physical file in memory on multiple PCs. What could possibly go wrong

We've gone through all the things that are wrong with the query you posted. Now you seem to have "fixed" the data and that broke the query for you. Makes no sense to me.

Remove the third table. Use a DLookup() to retrieve it ONCE in the load event of the report. You can then store it as a tempVar and use the TempVar in the calculation in the report.

Make sure that the data in the other two tables actually relates via the Solicitor. What happens when you run the query with just those two tables?

Did you ever remove the duplicate fields?

When I remove the third box named DOD Meeting Goal (this is a query), the other user still doesn't have access to update the tables. Works fine for me. I don't get it! Somewhere, I have permissions that others do not.

 

[Pat Hartman]

I thought the problem was the report. Now you are saying it is NOT the report but that the other user cannot update the tables. If you have the database open exclusive, the other user will NOT be able to update the data. Make sure that the database is closed on your PC. Does everything now work for the user?

 

[Erin M 2021]

I thought the problem was the report. Now you are saying it is NOT the report but that the other user cannot update the tables. If you have the database open exclusive, the other user will NOT be able to update the data. Make sure that the database is closed on your PC. Does everything now work for the user?

The report is fine when I run it. The other user cannot update the table. It is closed when attempted.

 

[Pat Hartman]

This is like pulling teeth. You keep waffling between the report and the user update. Do you have network support? Have them make sure the other user has Create/Delete permissions on the folder that holds the Access application. When someone opens an Access application, Access needs to open a lock file - hence the create permission. Then when the last person closed the Access app, Access needs to delete the lock file - hence the delete permission.

OR --- you can tell very simply if the correct permissions exist. Make sure that both of you have the application closed. Open a file folder and navigate to the folder that holds the application. You should see the database:
yourdatabasename.accdb

Now have the user open the database. You should two files now
yourdatabasename.accdb
yourdatabasename.laccdb

If you do, the user has create permissions. Now have them close the database and the lock file should go away. If it does not, then the network person needs to add delete permission but we still haven't solved the problem.

 

[Erin M 2021]

This is like pulling teeth. You keep waffling between the report and the user update. Do you have network support? Have them make sure the other user has Create/Delete permissions on the folder that holds the Access application. When someone opens an Access application, Access needs to open a lock file - hence the create permission. Then when the last person closed the Access app, Access needs to delete the lock file - hence the delete permission.

OR --- you can tell very simply if the correct permissions exist. Make sure that both of you have the application closed. Open a file folder and navigate to the folder that holds the application. You should see the database:
yourdatabasename.accdb

Now have the user open the database. You should two files now
yourdatabasename.accdb
yourdatabasename.laccdb

If you do, the user has create permissions. Now have them close the database and the lock file should go away. If it does not, then the network person needs to add delete permission but we still haven't solved the problem.

I'm answering all of your questions the best I can. I've posted because I cannot figure it out. The lock file does get created but it's different from how it is occurring on my screen. My screenshot has the Access icon with a lock. The other user has an adobe? icon

Mine:

Other User:

Permissions to the file folder where this is stored appears to the be same as mine as well. Read & Execute, Read, write.

 

[Pat Hartman]

The other user has the .laccdb linked to Adobe rather than Access. I'm not sure why that matters but you should fix it.

Depending on what version of Windows you are using, the setting could be anywhere. Try searching for "choose default apps by file type". Then associate the .laccdb to Access. But before you do this, search your list and find all Access file types. Here are some but possibly not all:
.accdc "signed package", .accde "compiled app", .accdr "runtime app", .accdt "template, .accdu "add in", .accdw "web app", . accft "template"

Access is supposed to create all these mappings when it is installed. Perhaps something went wrong with the install or someone changed the association later.

 

[Erin M 2021]

The other user has the .laccdb linked to Adobe rather than Access. I'm not sure why that matters but you should fix it.

Depending on what version of Windows you are using, the setting could be anywhere. Try searching for "choose default apps by file type". Then associate the .laccdb to Access. But before you do this, search your list and find all Access file types. Here are some but possibly not all:
.accdc "signed package", .accde "compiled app", .accdr "runtime app", .accdt "template, .accdu "add in", .accdw "web app", . accft "template"

Access is supposed to create all these mappings when it is installed. Perhaps something went wrong with the install or someone changed the association later.

This has been updated for the user. Now that we've established the create permissions are on, what's next?

 

[Pat Hartman]

Did changing the .laccdb association work?

That's it. I have no clue what the problem is. You need to get your IT people involved. Someone has to be able to access the app and the computer in question.

I'm leaving for the day so there won't be any more quick replies.

 

[Erin M 2021]

Did changing the .laccdb association work?

That's it. I have no clue what the problem is. You need to get your IT people involved. Someone has to be able to access the app and the computer in question.

I'm leaving for the day so there won't be any more quick replies.

Thanks for your help. That did not change anything. I'll ask my IT team to compare our profiles.

 

[ebs17]

I refer to #3 and #5.

If a query is empty, i.e. shows no records, it does not mean that it does not work. It means that with the query definition used and the data currently used, there are no records that can be displayed. It is not an error, but a result.

Possible reasons for an empty recordset:
The "DOD Meeting Goal" table is included by CROSS JOIN. If there are several records in this table, the records from the JOIN of the other two tables are multiplied according to the number of records. If the table is currently empty, it is multiplied to NULL and the total recordset remains empty.

The other two tables are connected by INNER JOIN. A result greater than empty can only occur if at least one identical key occurs in both tables. If there is nothing, also the CROSS JOIN cannot change anything.

Measures:
Check whether the query design really makes sense and is intended.
Then check the currently available data.

"Query is blank when other users update the source data"
What exactly takes place there?

 

[Erin M 2021]

I refer to #3 and #5.

If a query is empty, i.e. shows no records, it does not mean that it does not work. It means that with the query definition used and the data currently used, there are no records that can be displayed. It is not an error, but a result.

Possible reasons for an empty recordset:
The "DOD Meeting Goal" table is included by CROSS JOIN. If there are several records in this table, the records from the JOIN of the other two tables are multiplied according to the number of records. If the table is currently empty, it is multiplied to NULL and the total recordset remains empty.

The other two tables are connected by INNER JOIN. A result greater than empty can only occur if at least one identical key occurs in both tables. If there is nothing, also the CROSS JOIN cannot change anything.

Measures:
Check whether the query design really makes sense and is intended.
Then check the currently available data.

"Query is blank when other users update the source data"
What exactly takes place there?

Thanks for your inquiry. To be clear. All queries and reports work for me as they should. This is occurring ONLY when another user attempts to update the source data.

Source data comes from .MBD that files are exported from an external CRM. The .MDB files are fed into the .accdb file as tables. User can successfully get through these steps and see the updated data in the tables. Where the issue arises is now in the query using the data updated by that user. I'll note that when this other user makes these updates, I can no longer get data in the query either. But if I were to update it after that, the query would work again for me and that user. So the specific problem, I believe, is in some type of permissions but I have not been able to find any differences.

 

[ebs17]

Source data comes from .MBD that files are exported ...

So there are several files. An MDB cannot be imported as a table.
It seems possible that the selection and import process can be executed incompletely and incorrectly, and that one user then does so.

Normally one would have a procedure in use here, where a user has to contribute as little as possible own activity and thus own errors.

I could easily imagine a VBA procedure, where at the start of the own frontend or additionally at a specific button click everything not yet imported is read in at one go (without repetitions).

How did you organize the import process?