[AccessD] Make-table query question

I have a max table (shows the maximum for some calculations for the purpose
of choosing which records to display from another table) that is recreated
via a make-table query on a regular basis. This needs to happen because of
the complexity of queries being run. I'm sure many of you have run into it:
Access hits its limit in queries of queries of queries, so you have to stop
part-way and do a make-table query, and then start up again. Anyway, it
works fine except for the problems below.

A subform is based in part on the max table mentioned above. When a new
record is created in the main form, and related records are created for the
subform, the presence of the subform's records is based in part on
corresponding records in the max table. Unfortunately, since the max table
is involved in supporting the open forms, it can't be destroyed and
rebuilt, as is required by a make-table query. This results in the
subform's being blank, since there are no corresponding records.

One partial solution is to have the user close the form, run a function
I've created to recreate the tables--there are actually several tables
involved--and then reopen the form. If that user is the only one logged
into the app at the time, that works. I've put a button on the toolbar to
run the function, so it's only a minor inconvenience for the user. However,
if anyone else is logged in, it's a definite no-go.

Any suggestions for help with this? As usual, I may not have explained it
clearly enough. Please let me know if that is the case.

Thomas F. Ewald
FCS Database Manager
General Dynamics Land Systems
(586) 276-1256






This is an e-mail from General Dynamics Land Systems. It is for the intended recipient only and may contain confidential and privileged information.  No one else may read, print, store, copy, forward or act in reliance on it or its attachments.  If you are not the intended recipient, please return this message to the sender and delete the message and any attachments from your computer. Your cooperation is appreciated.