vba - Getting Error 3048: Cannot open any more databases

Question

I recently split my database. My form is a calendar with a Tab for Month View, Week View, and Day View with 42, 7, and 7 subforms, respectively. All of these subforms are unbound. When a tab is selected, all the subforms in that tab is assigned a ControlSource while all other subforms are cleared of its ControlSource.

The Month View is the only one that gets Error 3048: Cannot open any more databases. after it loads 23 subforms (there are a few labels, lists, and buttons outside the Tab, but I do not think they are significant).

When the Month View tab is selected, the following actions occur:

1. A 42x2 array is filled with Long date information
2. All subforms are cleared of its SourceObject
3. For all subforms in the selected tab: Assign its SourceObject then call a function (located in the SUBform) to Filter itself. In this function, another function is called to count the number of records in the subform based on some criteria. A database and recordset is created here and passed through several times before the recordset is Closed.

The Subform contains several text boxes with one containing a conditional format to color it. Its Record Source is the query:

SELECT tblTask.JobNum, tblJob.JobNum, tblTask.Sequence, tblJob.Closed, tblJob.Certified, tblEstimator.SortID, tblDivision.SortID, tblJob.EstimatorID, tblTask.DivisionID, tblJob.JobSite, tblJob.Customer, tblJob.Closed, tblTask.Item, tblTask.ItemDescription, tblTask.StartDate, tblTask.EndDate, tblTask.WeekendWork, tblEstimator.EstimatorNum & "-" & [FirstName] & " " & [LastName] & "20" & Mid([tblJob].JobNum,3,2) & " JOBS" AS JobMidFilePath
FROM (tblEstimator RIGHT JOIN tblJob ON tblEstimator.ID = tblJob.EstimatorID) RIGHT JOIN (tblDivision RIGHT JOIN tblTask ON tblDivision.ID = tblTask.DivisionID) ON tblJob.JobNum = tblTask.JobNum
WHERE (((tblJob.Closed)=False))
ORDER BY tblTask.JobNum, tblTask.Sequence, tblTask.StartDate, tblDivision.SortID;

Right now 521 records are returned with this query.

Is it expected that my form should not be able to handle all of this? Or is there a way to improve the efficiency of my form? I'm very lost about what I should do about this, since I need all 42 subforms to load.

Let me know if you need more information. Thanks in advance!

Answer 1

So, the good news is your problem month view tab is all read-only. That will make it easier to implement my suggestions:

1. Try setting the RecordsetType to 'Snapshot' in the Form Properties for each of your subforms. This is simplest. If that doesn't work, try:

2. Use ADO disconnected recordsets. Look at this reference:

How To Create ADO Disconnected Recordsets in VBA/C++/Java

Basically, you create this disconnected recordset thingy, and set the .RecordSet propery of your subform to it:

Set mySubForm.Recordset = myDisconnectedRsObject

Since these by definition do not maintain a connection to the backend, you should be able to create as many as you like without increasing the database count.

Once you get the first one working, you will need to convert all of your Access/Jet queries underlying the 42 subforms into disconnected recordsets.