I am making a database to help figure out classes and staffing needs for a science camp. The main problem I'm running into is that there is too much info that needs to be displayed at once for MS Access to handle. I've lowered my expectations of how the form will work, but I'm still getting this "Too many tables open" error.
The form will work for a little while, but eventually it kicks the error out and stops. More tables are getting open because every time I select a teacher for a certain slot, it updates a query that all the teacher drop-down boxes use. This is so that I don't schedule a teacher twice for the same block. I think that Access is opening a new table every time it refreshes and updates the query though, because eventually it stops working.
The question is then, is there any way to purge these opened tables from memory that aren't being used any more without restarting the form or the database? I read somewhere that once a table is opened, it stays opened, but I don't remember where. If that's the case, then, well, that's just dumb.
It sounds like a design problem. If you do not have a good table structure to start with it can lead to a very buggy and difficult program to maintain. Can't tell what the specific cause is based on the information supplied. If you post a sample of your DB or give more details it may be possible to identify the problem.