Desperate! Cannot open any more databases error on splitting database

[pondlife]

I understand that Jet 4 has a 2048 limit on the number of TableIDs which if exceeded leads to this error. My form uses 1 bound table, 2 recordsets in code, 1 clone (opened, closed and nothinged in 10 lines) plus a 5 x25 array of combo boxes whose sources are 14 x25 tables (as part of queries) applied in code. Total 360 tables or thereabouts. All works fine when a single database, but after splitting the database, this form when opened generates the dreaded 'Cannot open any more databases' error. (All the other forms in this middlingly-complex application work fine. The error comes whether the form is opened on its own first or after several other forms.)

First question - how can 360 tables explode to exceed the 2048 limit when the database is split (I have read that linked tables need 3 x the number of TableIDs)? Is the number of tables a valid measure?

Second question - if the problem is all those combo controls, how can I get around it? I have to load all the combos on opening the form (it's no good in this app putting the SQL in the tag then calling it at runtime which is one workaround I've seen). I am loading them like this:
Me("cboR" & intRowNum & "C" & intColNum).RowSource = "SELECT * FROM qrySource{intColNum} WHERE [ID] = " & rs!ID

where R = 1-25 and C = 1-5, and ID defines the row in the recordset.

Can anyone see anything wrong with that? If not, what else might be going on here? I'm using ADO in ACC2000.

Thanks for your help.

John

 

[MarkK]

- You could change the row source type of all the combos to 'Value List' and populate the lists using delimited strings. This would solve your too many databases issue since none of the combos would be leveraging a table for its data.
- Also, the loop would not be way more complicated than what you already have. Where you currently assign an SQL statement to the combo's rowsource, instead you open a recordset using that SQL. Traverse the rows of that recordset, concatenate a delimited string, and assign that string as the rowsource of the combo.

 

[boblarson]

I'm probably opening a can of worms, but 360 tables? Doesn't sound normalized to me. I could be wrong but it would be a rare case to have that many tables in a database. How did you wind up with so many?

 

[pondlife]

Hi Bob

This is a 'paged' form with an array of controls displaying 25 records each time, each linked to 2-3 tables. It is bound to a table that often needs 2-3 screenfuls to display all the records. The user clicks Next/Previous buttons to review the whole set page by page, like a Google search. The user's task is to check and amend for each record various choices found to be available for 5 different fields, done using the combos. The available choices to be offered in the combo vary, and the user needs to be able to see the choices already made for the other records in the set. Finally, when a combo finds only one choice, that choice is written to the table without user intervention and the combo is hidden.

This set of functionalities is totally key to the commercial requirement so well worth the extra programming to get an Access form to perform in this way.

Incidentally, I haven't seen 'paged' forms done in Access more than a couple of times elsewhere - have you?

John

 

[pondlife]

Hi lagbolt

Many thanks for your suggestion, which is a very good one. It is somewhat along the lines of the solution I've been exploring, except I need the combo box source to be live data.

I'm finding I can achieve the functionality I need (described in my reply to Bob above) by (1) on the form's Current event, inspect for the number of choices available for each of those 5 fields, if only one choice then update the recordset and hide the combo (2) with all the combo.recordsets starting out blank, load the recordset of choices for a combo at its 'Enter' event (using the SQL described in my original question) and setting it to nothing on 'Close'. There are some wrinkles beyond this which I'm currently working on.

Thus at any one time only one of the 5 x 25 = 125 combos is loaded and using up any of the 2048 JET handles.

John