Query is blank when other users update the source data

[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.

 

[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.

 

[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.

 

[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?

 

[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?

 

[Erin M 2021]

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?

There are a total of 6 .MDB files connected as external data to the .accdb file. They are not imported.

 

[ebs17]

Parallel operation of linked tables with the same name?
Re-linking each time?

Again the question of a correct and complete process arises. How does this take place?
The term "updating" is such a meaningless commonplace that you don't need to think about it.

 

[Erin M 2021]

Parallel operation of linked tables with the same name?
Re-linking each time?

Again the question of a correct and complete process arises. How does this take place?
The term "updating" is such a meaningless commonplace that you don't need to think about it.

All data is entered into our CRM. Weekly, I export this data to .MDBs. There are multiple because they contain different data that gets connected within the queries in the .accdb. No need to relink each time. Process is fine. This is a user specific issue.

 

[ebs17]

#38:

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

Other users <> "I" !!

You would have to ask yourself the questions I am asking.

If you do not answer and examine beyond your belief, any further engagement with the subject is a waste of time. My waste of time is finite.

 

[Erin M 2021]

Step by step in excruciating detail, walk through what happens and where it breaks for the second user.

Here goes:

This is a specific example, but if the user updates any of the linked .MDB files, it is the same occurence.
1. .MDB is updated via export from CRM. .MDB file has three tables. All are updated successfully.

2.Within the .accdb (file that uses the .MDB files as data sources and builds reports), the related tables also successfully get updated.

3. Next is the query that ties in SolicitorGoals (this data does not change) :
Solicitor Goals Query:
SELECT TOP 1 [Solicitor]![CnBio_First_Name]+" "+[Solicitor]![CnBio_Last_Name] AS Solicitor, SolicitorGoals.CnGoal_1_Category, SolicitorGoals.CnGoal_1_Goal, SolicitorGoals.CnGoal_1_Notes
FROM (SolicitorGoalLink INNER JOIN Solicitor ON SolicitorGoalLink.CnBio_LINK = Solicitor.CnBio_LINK) INNER JOIN SolicitorGoals ON SolicitorGoalLink.CnGoal_1_LINK = SolicitorGoals.CnGoal_1_LINK
GROUP BY [Solicitor]![CnBio_First_Name]+" "+[Solicitor]![CnBio_Last_Name], SolicitorGoals.CnGoal_1_Category, SolicitorGoals.CnGoal_1_Goal, SolicitorGoals.CnGoal_1_Notes
HAVING (((SolicitorGoals.CnGoal_1_Category)="Monthly Meeting Goal" Or (SolicitorGoals.CnGoal_1_Category)="Annual Fundraising Goal"))
ORDER BY SolicitorGoals.CnGoal_1_Notes DESC;

Solicitor Gifts Raised:
SELECT GiftSolicitor.GfSol_1_Name AS Solicitor, GiftSolicitor.GfSol_1_Amount AS SolicitorAmount, Gifts.Gf_Date AS DateRaised, [Solicitor Goals].CnGoal_1_Goal AS Goal, [Department Goal].DepartmentGoal, Gifts.Gf_Amount AS [Gift Amount], Gifts.Gf_GftsLnkdtPlnndGft
FROM [Department Goal], (Gifts INNER JOIN GiftSolicitor ON Gifts.GfSol_1_LINK = GiftSolicitor.GfSol_1_LINK) INNER JOIN [Solicitor Goals] ON GiftSolicitor.GfSol_1_Name = [Solicitor Goals].Solicitor
WHERE (((GiftSolicitor.GfSol_1_Name) Is Not Null And (GiftSolicitor.GfSol_1_Name)<>"Betsy Clardy") AND ((Gifts.Gf_GftsLnkdtPlnndGft)=No) AND (([Solicitor Goals].CnGoal_1_Category)="Annual Fundraising Goal"))
ORDER BY [Solicitor Goals].CnGoal_1_Goal DESC;

As a reminder, if I remove the Department Goal query from my design, it does not fix the problem.

When the user goes to view the Solicitor Gifts Raised query, only column headers appear. There is not data in the cells.

Let me know if you have further questions on the set up. I'm having my IT team compare user permissions this afternoon.

 

[Erin M 2021]

What does that mean? Is he opening a table and changing existing rows? Is he adding new rows? This woudl be seriously poor practice. You really can't have multiple "master" sources for data and that is what you end up with when the data is updated in the CRM and also in your local app.

Is he running some download and then replacing the old tables?

You can't describe a process using the passive voice.

1. how are the tables updated? Are they replaced in total? Are the records deleted and new rows appended? Are you joining the old and new and trying to update your version? How are you handling adds/deletes?
2. "also get updated" That is a completely meaningless statement. Does someone wave a magic wand? WE CANNOT SEE ANYTHING. All we know is EXACTLY what you tell us.

=======================================
This isn't working. What YOU can do.
============================================
When the user runs a query that produces incorrect results, You examine each of the data sources and search manually until you actually find the records you think the query should have found.

What is the point of using .mdb's for the BE?
When you download replacement data from a CRM, the best solution is to import it into a NEW, Empty database so you don't run into bloat problems. Don't even try to update individual records. Unless you need to join to these tables in update queries, don't even bother with PKs or RI.

User is replacing tables when "updating". We don't care what was there before, we only care about the new data replacing the .MDB.

I've narrowed it down some. The issue is occurring when the user replaces my .MDB. In testing, the user can manually update data in the .MDB and all is well within the .accdb. This is not PC specific. This is specific to the login (me vs. other user) to our CRM. How and why do you ask? I still do not know. I will be reaching out to the vendor tomorrow.

The reason for the .MDBs is to linked various datasets together. IE, I can't export everything in one table from my CRM. What would you suggest here otherwise?

 

[Erin M 2021]

Not sure I know what FE and BE means.

There is not an integration between the CRM and Microsoft. This is why manual export and replace processes are occurring.

 

[Erin M 2021]

I hope your IT people can help.

Can you define FE and BE?

Does this mean you are out of ideas/stumped?

 

[Erin M 2021]

BE = Back End = separate database containing ONLY tables
FE = Front End = the "application" part with all of the other objects such as forms and reports.

Gotcha. No. This is not split. The .MDBs are just the exported files that get fed into the .accdb. You can modify in both file types.

 

[Erin M 2021]

Too loosie-goosie for my aging heart. Only ONE set of tables should be the "master". When you allow updates in multiple places, you never have a clue what is real.

Well, we AREN'T doing that, this was just to explain the connection. Changes are only made to the data through the export and replace.