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

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

 

[Pat Hartman]

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

 

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

 

[Pat Hartman]

but if the user updates any of the linked .MDB files, it is the same occurence.

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.

 

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

 

[Pat Hartman]

If the FE is .accdb, why is the BE .mdb? was the question.

Isn't there some process by which the .mdb's get replaced every day? Why are individual users replacing them piecemeal?

 

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

 

[Pat Hartman]

I hope your IT people can help.

 

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

 

[Pat Hartman]

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.

 

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

 

[Pat Hartman]

You can modify in both file types.

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.

 

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

 

[Pat Hartman]

You've mentioned multiple times that other users update the data.

When any other user updates the .MDB, they can successfully view the new data in the .MDB file and in the .accdb table. Problem is occurring when they try to open the query/report using the table. It comes up blank.

Sorry, I really don't have any more time to spend on this. We are more than 50 posts in and I still do not have a clear picture of what you are doing. Hopefully your IT people will be able to help. If they can't, you might want to try to hire someone who can come on site or at least log in to access the computers remotely.