Your issue with this query is not answerable without knowing a little bit more. When you generate the result-set of this query, how many fields are in it and how big is one record in bytes? I can make educated guesses on that.
I suspect your 35K lines limit is because of a query limit. In this article, MS says the recordset size that can be developed by a query is 1 GB.
Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table.
support.microsoft.com
If you have 35000 rows x 12 fields x 7 tables that is just under 3 million fields. Your maximum record size could easily exceed 4K/record if you had lots of 255-byte fields, but a query that did that would never run so I have to assume it is smaller. But if you have 35000 rows x 84 fields (derived from 7 tables) x 300 bytes per record (not that difficult for 84 fields), you would approach the 1 Gb recordset limit.
Why would setting a new DB be better than incorporating new tables into the current DB?
Because of something called "bloat",you DO NOT want to continually create and delete records. This bloat (database swelling in size) happens because of a holdover problem called "garbage collection." Access, unlike some of the "big boy" databases, does not clean up on-the-fly. You have to do something called a "compact & repair" (C&R) to recover abandoned space. In fact, you didn't mention it, but I would lay odds that you are familiar with that activity. Your file grows in size even after you delete a bunch of stuff.
The "side-end" database is a file that you can create fully configured but empty. Put it aside in a staging area. Then, perhaps in a batch script beforehand, you can delete it and create a new copy of it from the copy in the staging area. Since Access links by file name, not by any kind of internal file identifier, the FE will never know that you replaced the SE database before launching the app.