'Temporary' tables in ms access to deal with complicated mysql tbls

[MrMode]

I am not sure if I am in the right forum.

I have an MS Access front end talking to a MySQL back end. There are 7 tables that need to be linked together to create a single table / query (3 of which need to be be pivoted then concatenated first).

I have tried to use a query that links the various queries and tables together which I then wanted to use in vba and queries but it fires the underlying pivot and concatenate queries making it impossibly slow every time I move forms or make a selection in a list etc.
​

I have tried to run the query as a make table query but it says "Record is too large".​

And I have tried do this through running queries and updating a table with the different queries to build the single table I want. This makes the front end unusable as it hangs for more than 40 minutes as tries to crunch the data.
​

I have read a bit on tables on the fly that can be created then destroyed at the end of the session at the front end.

The table I am trying to create is only to provide information that the user will read, they will not change any of the data.

Does it make sense to create the 'table' on the fly when the user opens the front end, make the data available, process any changes then discard the 'table' when they close the database or would you suggest some alternative?

I am not confident with how to do all of this, but from what I have seen, it seems to make sense to create the table I want from these different tables in MySQL then discard it at the end of the session rather than they route I have tried (and failed) which is to create the table every time a user moves forms or makes a selection in a list.

Any and all suggestions gratefully received.

 

[MrMode]

Or would it make more sense to use a pass through query to 'create' the 'table' at the mysql end that can then be used as the source for the queries and forms in my MS Access front end?

 

[SkipVought]

hi,

There are 7 tables that need to be linked together to create a single table / query (3 of which need to be be pivoted then concatenated first).

So are you saying that first you join the 3 tables to pivot the results and then you join then other 4 tables with the pivot results, and I'd guess you are joining the pivot to the others on ONLY THE ROW FIELDS, not the column/pivot values?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[dhookom]

I would try to perform all of the heavy lifting on the server if possible. Then use pass-through queries to pull the results. If you need to make temporary local tables, I would create a temporary Access file in the same folder as the front-end and push the data into it.

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

I have 4 tables and 3 pivot queries that I need to join together to create one table that I can then use in my forms and queries.

As hookham says, I would prefer to do the heavy lifting on the server then pull the result into a temporary table on the local machine (assuming I would have to re run the process each time I made a selection etc, so would be better to have the data available for the 'session' on the local machine).

The pivot queries have to run before the select query in order to allow for pasting into the table. I have looked at pass through queries, the simple ones work fine, the more complex don't because I would have to write them in mysql syntax not ms access - great a new language to get my head around when I can't even speak ms access yet!

Does that sound like the best way, or is there a better one? I understand that trying to run the queries locally means Access is pulling all the data across first, then trying to execute the queries which is why it hangs like a dog and pass through may reduce the time it takes, it seems to make logical sense to me to pull the result across to a temporary table and dump it once the user has finished.

As I said, the data will never be changed, it is there to fill in fields that the user needs to read off in order to complete their processes.

BTW, the mysql element is an external contractors world that they don't like me playing in, even more reason to leave their stuff alone as much as possible and run the queries less often.

 

[dhookom]

If you can't (or prefer not to) mess on the MySQL side then pull the required data into temporary tables. I worked on a reporting application that initially took several minutes to generate reports. After pulling the data from MySQL (I think) into temporary Access tables, the reports took only a couple seconds. My customers (and other developers) were amazed by the performance increase. They previously had to wait to run reports to off hours.

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

Sounds like a plan because it cannot drag its backside any more than it already does!

Thanks