Form SQL or Query Limit? Error 3276, Invalid database object reference

[jbrumbau]

Hello,

In my database, I'm having an issue where if a user opens too many forms with massive numbers of SQL statements and table references, they'll get error 3276 - Invalid database object reference whenever they try to open another form or if any VBA code executes CurrentDb.OpenRecordset. This message happens with any table or query that would otherwise open without issues.

Because of the limit, a user can only have 1-2 complex forms open at a time before the error message loop happens and the user has to restart the database to get it to stop.

I tried to find a fix to this bug but to no avail, and for now simply enforce a form limit under the Form_Load() subroutine of each of these big forms. I found this article:
http://www.databasedev.co.uk/access_specifications.html
But the only thing I could find in terms of limits I may be reaching was Number of tables in a query = 32.

My question is if there is anything I can do to allow users to have more than 2 of these complex forms open, or if this is simply an MS Access limitation I have to live with?

Thanks in advance.

[Rabbit]

It's an Access limitation. But if you're hitting these limits with just 2 forms open, you could probably redesign and optimize them a lot more than they are currently.

I honestly can't see a need for 25 tables in one query. Practically, it's gotta be unwieldy to use.

[jbrumbau]

Unfortunately I can't reduce the size of these forms any more. It isn't one query that has 25 tables, but each query has several queries nested inside of it, so the total tables referenced could be 10 or so per query. With 3 forms open that means "Invalid Database Object Reference" which is very limiting and annoying. These queries all use type 2 or 3 integer based links to autonumber fields, no string links, so nothing strikes me as unusual here.

The company I work at is an engineering firm and they send these large datasheets to vendors all the time. So naturally, there are going to be a ton of data links in these datasheets since combo boxes should reference tables, subforms should be used for common sections, separate equipment lists need to be referenced, etc. All the fundamentals are being done, and I even did "Analyze Performance" in the [Database Tools] tab in the ribbon and got it in tip top shape (minus the # of controls which I have little to no control over).

It sounds like this is something I'll just have to live with with MS Access :(