error 3048 - Cannot open any more databases

[Guest]

I am having an issue with a form that I am hoping someone can help me solve,
or at least point me in a direction so that I can arrive at a solution myself.

Well I guess to begin with I am running Access 2003 on Windows XP.

My issue is, as stated in the subject line, that I am getting the “Can not
open any more databases†error after using a certain form I have created for
sometimes a very very short period of time. Sometimes I can only click three
combo boxes.

This form is an unbound form with 48 subforms. The 48 subforms are actually
the same subform, just inserted 48 times. I realize this sounds reasonably
insane, but let me give you more info as to why this is the way it is. It is
a weekly work schedule for 8 employees over 6 days of the week. I have
linked each individual occurrence of this subform to two text boxes on the
parent form, one for a date, one for a name. I have names running down the
left hand side and dates running along the top, which are in the parent form.
Each subform has about 4 combo boxes plus a whole slew of other controls,
well between 15 and 25 I would say. I also have a few other things going on
in the parent form as well that references the child forms. I hope this
gives you something at least. I am happy to give more info on this if need
be.

I understand that this error is caused by a limitation on the amount of
“connections†I can have. My ultimate question is how do I fix it. I have
read that I can replace my Dcounts, and Dlookups with other methods, but I
did bit of this, but it did not seem to alleviate the issue much at all. I
used recordcount instead of dcount, and closed the recordset after the count.
Are there alternatives to combo boxes? Like programmatically creating one.
I notice that I can test the form by just going through and clicking the drop
down arrow next to each combo box in each subform to replicate this error
message.(sometimes I get the error message after just clicking one combo box
if I scroll parent form to view all 48 subfrms first.) My assumption is
that I am opening a “connection†accessing those tables over and over again,
without closing those connections with each combo box. So my thoughts were
that maybe I could create custom combo boxes so that I can open the
connection only when needed and close the connections when not, thereby,
decreasing the frequency of the error, or eliminating the error.

Thanks a bunch to all those who will give me a hand with this one.

 

[Allen Browne]

Greg, the problem probably does lie with the RowSource for the combos.

If there are 48 combos that all have the same RowSource (such as the ones
where you select a staff member), you may be able to write a callback
function to use as the RowSourceType.

Here's an example of such a function:
http://allenbrowne.com/func-02.html
That one uses a loop to read file names into a static array. You could do a
similar thing, using OpenRecordset to read the employees into a static
array, and then supply all the combos from the one array.

It's a bit arcane to wrap your head around if you've never seen this kind of
callback before, but it's not difficult to implement.

 

[Guest]

Allen,

Thank you so much for the information.

I think I may understand what you are doing with your code.

I guess the biggest thing for me to understand, and please correct me if I
am wrong, is that I can populate a combo box with vba without using the row
source property buy creating a function and then typing that function name
into the Row Source Type Property thereby, I opening the "connection" to the
underlying tables only when needed.

However, I am using these combo boxes not only as a way of selecting a
grouping of information, but also to display the information in the correct
way. That doesnt make sense when I read that, but here is what it is doing.
The "work schedule" table is storing numbers in a field titled "Type" for the
type of work to be done that day. However, since I do not want the user to
see the number of the "Type" I have the combo box there to actually display
the "type" field as a Name not a number. Is this bad practice. I believe
this is actually causing some additional work load for access because each
scheduled item has this "Type" combo box. And since there are 48 subforms
capable of displaying 10 or so records, that could be, at a maximum, 480
combo boxes accessing the back end to "translate" the number to the name of
the type. This also is the same for several other combo boxes. (I am
begining to see why I am having such a problem)

THanks again for the reply. I hope I havent lost you yet, i may have lost
myself.

 

[Pieter Wijnen]

A simple function to populate a Combo/ListBox

Private Function cbo_Populate(Fld As Access.Control, id As Long, row As
Long, col As Long, Code As Integer)
Dim thQ As String
Dim Rs As DAO.Recordset
Dim F As DAO.Field
Static QDef As DAO.QueryDef
Dim Db As DAO.Database
Dim Parm As DAO.Parameter
Static Entries As Variant
Static Cnt As Long
Dim ReturnVal As Variant

On Local Error Resume Next

ReturnVal = Null
Select Case Code
Case Access.acLBInitialize 'Initialize.
Cnt = &HFFFF& 'Max Rows Allowed
Entries = Empty
thQ = "SELECT T.TypeID, T.Type FROM Type T" & VBA.vbCrlf & _
"Order By 2"
Set Db = Access.CurrentDb
Set QDef = Db.CreateQueryDef(VBA.vbNullString, thQ)
For Each Parm In QDef.Parameters
With Parm
.Value = Access.Eval(.Name)
End With
Next
Set Rs = QDef.OpenRecordset(DAO.dbOpenSnapshot)
Entries = Rs.GetRows(Cnt)
Cnt = Rs.RecordCount
Rs.Close: Set Rs = Nothing
Set Db = Nothing
ReturnVal = Cnt
Case Access.acLBOpen 'Open.
ReturnVal = Timer 'Unique ID number for control.
Case Access.acLBGetRowCount 'Number of rows.
ReturnVal = Cnt
Case Access.acLBGetColumnCount 'Number of columns.
ReturnVal = Fld.ColumnCount
Case Access.acLBGetColumnWidth 'Column width.
ReturnVal = -1 'Use the default width.
Case Access.acLBGetValue 'Get the data.
If Fld.ColumnHeads And row = 0 Then
ReturnVal = VBA.StrConv(QDef.Fields(col).Name, VBA.vbProperCase)
Else
ReturnVal = Entries(col, row + Fld.ColumnHeads)
End If
Case Access.acLBEnd 'End
Cnt = 0
Entries = Empty
ReturnVal = Nothing
QDef.Close : Set QDef = Nothing
End Select
cbo_Populate = ReturnVal
End Function

 

[Allen Browne]

That's fine, Greg. I take it that the bound column is not the display
column. The combo that uses the callback function as its RowSourceType will
still be able to display the desired data.

What happens is that you load the static array one (when the combo is first
initialized), and then it calls the callback function whenever it needs a
value. You better believe the documentation when it says Access will call
the callback in any old order, but it doesn't matter because it is now
reading the values from the array.

 

[Guest]

Allen,

Yes the bound column of my combo boxes are not the display column.

This is good that the combo box will still be able to display the desired
data. But do you think that if I were to go this route it will definitely
decrease my open connections, since ultimately that is what I am after.

Also, would it just be easier for me to store, in my Type field, and names
and not the numerical values. That way I can convert the combos to text
boxes with a button beside each one that would open a seperate form that
would display a list of values to select. Upon selection, the popup would
close and data would then be entered into the type field? Wouldnt this
almost be simpler, and definitely decrease my open connections? Just a
thought.

Thanks,

 

[Allen Browne]

Many years ago, I did a rostering app that had the problem you describe.
Using a callback function for the RowSourceType solved the problem.

The problem is not about whether the bound column is displayed or not: It's
that fact that each combo needs its own RowSource, and you solve that by
using a single array instead of a separate connection for each one.

 

[Guest]

Allen,

Well I am here working with your suggestions tonight and have run into an
issue. I am having a hard time creating a multidimensional array. I am also
not sure how to then send this multidimensional array to the combo box.

I have created a one column array and then sent this to a one column combo
box successfully.


Thanks for any input. It is greatly appreciated.

 

[Allen Browne]

The multi-dimentional array needs to be declared as Static, so the values
are present without having to recreate it every time the callback function
is called to get a value.

You load the array in the acLBInitialize case.

You then assign the return value from the array in the acLBGetValue. Since
Access passes in Row and Col arguments to indicate the row and column it
wants. You use these values as the index to your 2-dimentional array.

Here's another example:
http://allenbrowne.com/ser-19.html
It's a single-element array again, but you just change the function return
value to include Col as well as Row.

 

[Guest]

Allen,

Thanks so much for a quick reply.

Anyways, I was actually able to figure out my questions on my own right
before seeing your response. However, performance on my form was
horrendously slow. but after reading your response, I followed your
recommendations and I declared my array as static and also loaded the array
and opened my recordset in the initialize case. This then improved
performance greatly. Not quite as good as before, but at least i do not get
the "cannot open any more databases" error. Yeah!

I greatly appreciate your help with this.

Thank You