Thanks all for the suggestions. Will try everything/anything.
The app is a tool to provide information about the mainframe system.
The user enters the name of a program. The app needs to show all the Procs and JCL that call the Program. The result would look like this:
PrgA ProcA JCLA
PrgA ProcA JCLB
PrgA ProcB JCLA
prgA ProcC JCLD
PrgA no proc JCLE
PrgA ProcF No JCL
Looks easy enough, but sometime procs call procs, so I need to be recursive in my queries. Sometimes there is no proc, the JCL executes the program directly. Other times I have orphaned procs with no JCL.
So Q1 -> Q2 -> Q3
and Q4 -> Q5 -> Q6
Q7 Merges Q3 & Q6
Q8 -> Q9
Q10 merges Q7 & Q9
As long as each individual query is not too complex this works. However, once the queries start getting complex, Access regurgitates with "TILT!" (or something like that).
That's why I need to create intermediate tables attached to a specific user.
This application was initially written for a group of 6 user, using in occasionally. Now it has been deployed to the entire development staff (100 users) who are in it all day long. Yeah, is should be rewritten in something else, but due to budget issues that probably won't happen. The corp has looked at other various off-the-shelf products to replace this app, but nothing offers the flexibility that I have in mine, so the users keep rejecting the canned programs.
Most likely choice for a rewrite would be to migrate it to Mainframe Linux and rewrite it in Oracle. (Can only use tools authorized by corp!)