Cannot Open anymore databases

[Snowflake68]

I am using on of Allen Browne's functions (see below) to concatenate related records into a string. However I am occasionally and randomly getting an error 'Cannot open anymore databases' and the title in the error message suggests that it is this function.

One suggestion is that the function is in a continuous loop which would explain why that I am unable to exit out of the error and keep getting the same message over and over again and then the only way I can exit out is to use the Task Manager to end the task and close Access.

Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = ", ") As Variant
On Error GoTo Err_Handler
    'Purpose:   Generate a concatenated string of related records.
    'Return:    String variant, or Null if no matches.
    'Arguments: strField = name of field to get results from and concatenate.
    '           strTable = name of a table or query.
    '           strWhere = WHERE clause to choose the right values.
    '           strOrderBy = ORDER BY clause, for sorting the values.
    '           strSeparator = characters to use between the concatenated values.
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    Dim rs As DAO.Recordset         'Related records
    Dim rsMV As DAO.Recordset       'Multi-valued field recordset
    Dim strSQL As String            'SQL statement
    Dim strOut As String            'Output string to concatenate to.
    Dim lngLen As Long              'Length of string.
    Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
    
    'Initialize to Null
    ConcatRelated = Null
    
    'Build SQL string, and get the records.
    strSQL = "SELECT " & strField & " FROM " & strTable
    If strWhere <> vbNullString Then
        strSQL = strSQL & " WHERE " & strWhere
    End If
    If strOrderBy <> vbNullString Then
        strSQL = strSQL & " ORDER BY " & strOrderBy
    End If
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
    'Determine if the requested field is multi-valued (Type is above 100.)
    bIsMultiValue = (rs(0).Type > 100)
    
    'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & rsMV(0) & strSeparator
                End If
                rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
            strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close
    
    'Return the string without the trailing separator.
    lngLen = Len(strOut) - Len(strSeparator)
    If lngLen > 0 Then
        ConcatRelated = Left(strOut, lngLen)
    End If

Exit_Handler:
    'Clean up
    Set rsMV = Nothing
    Set rs = Nothing
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler
End Function

I am using a query to filter the records for a specific quote reference called 'qry_Concatenate_SPFBGetValues'

SELECT tblMainProductDetail.Reference, tblMainProductDetail.QRef, tblMainProductDetail.SpecialPrintFinishBACK.Value AS SPFBValues, tbl_SpecialPrintFinish.SpecialPrintFinish AS SPFB
FROM tblMainProductDetail INNER JOIN tbl_SpecialPrintFinish ON tblMainProductDetail.SpecialPrintFinishBACK.Value = tbl_SpecialPrintFinish.CodeBACK;

and then this one queries the above query to concatenate the related values. this all seems to work fine.

SELECT [qry_Concatenate_SPFBGetValues].[QRef] AS Ref, ConcatRelated("[SPFB]","qry_Concatenate_SPFBGetValues","[QRef] = """ & [QRef] & """") AS SpecialPrintFinish
FROM qry_Concatenate_SPFBGetValues
GROUP BY [qry_Concatenate_SPFBGetValues].[QRef], ConcatRelated("[SPFB]","qry_Concatenate_SPFBGetValues","[QRef] = """ & [QRef] & """"), ConcatRelated("[SPFBValues]","qry_Concatenate_SPFBGetValues","[QRef] = """ & [QRef] & """")
HAVING ((([qry_Concatenate_SPFBGetValues].[QRef])=[Forms]![frmMain].[txtCurrentRecord]));

I then have a text box on a form that does a dlookup to that query

=DLookUp("[SpecialPrintFinish]","qry_Concatenate_SPFF")

All of the above works but just occasionally I am getting this error and I cant replicate the error as it is random and I don't know what is triggering it.

Hope someone can help.
thanks for looking

 

[isladogs]

Access is only able to use a maximum of 255 connections though in practice it will slow to a crawl well before that. Each open object (table, form, report etc) 'uses' one or more connections. If objects aren't closed after use those connections aren't released. That may explain why it is random in that it may depend on what was being done previously
You can test what is happening using a utility available from http://www.mendipdatasystems.co.uk/available-connections/4594418530.

 

[Deleted member 147267]

not sure what is calling that or how often before the db ever closes but what I see is that you don't set db to Nothing

Exit_Handler: 
'Clean up 
Set rsMV = Nothing 
Set rs = Nothing 
 Exit Function

Maybe not the cause but it's worth implementing nonetheless.
EDIT - thought I saw a db variable but was mistaken so unless using that method helps, disregard. Maybe with each rs.MoveNext you are creating a new db object because
Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
is being called each time. I'd use a db variable.

 

[Snowflake68]

Access is only able to use a maximum of 255 connections though in practice it will slow to a crawl well before that. Each open object (table, form, report etc) 'uses' one or more connections. If objects aren't closed after use those connections aren't released. That may explain why it is random in that it may depend on what was being done previously
You can test what is happening using a utility available from http://www.mendipdatasystems.co.uk/available-connections/4594418530.

thanks for this. I have been using it this morning and it is very useful. I need to do some more testing using this but hopefully I will get to the bottom of the issue.

 

[Snowflake68]

not sure what is calling that or how often before the db ever closes but what I see is that you don't set db to Nothing

Exit_Handler: 
'Clean up 
Set rsMV = Nothing 
Set rs = Nothing 
 Exit Function

Maybe not the cause but it's worth implementing nonetheless.
EDIT - thought I saw a db variable but was mistaken so unless using that method helps, disregard. Maybe with each rs.MoveNext you are creating a new db object because
Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)
is being called each time. I'd use a db variable.

I just need to find out how to close them after they are no longer required. As I say I am no expert and still learning.

 

[isladogs]

As Micron suggerted I would use a current database variable in place og DBEngine(0)(0). It should be more efficient & only need to be set once

First add lines like the following in a module so they apply globally

Global dbs As DAO.Database
Set dbs = CurrentDb

Then use this line in your code

Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)

instead of

Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenDynaset)

Change other similar code to use dbs instead of DBEngine(0)(0). or CurrentDb.
Doing that means you aren't repeatedly setting variables so you don't need to keep closing/killing them
You should still set recordsets to nothing after use

Here is some related reading you may find useful: http://www.mendipdatasystems.co.uk/speed-comparison-tests-2/4594428908

 

[arnelgp]

you can even put the concatenation on your first query.
if you don't want to modify it, just make a copy and put the concatenation on the other.
make this the recordsource of your form.
so you get rid of the Unbound textbox.

 

[Snowflake68]

you can even put the concatenation on your first query.
if you don't want to modify it, just make a copy and put the concatenation on the other.
make this the recordsource of your form.
so you get rid of the Unbound textbox.

I cannot make the concatenation query the record source of the form because I need to use a different query for that. But thanks anyway.

 

[arnelgp]

ok, just make a new query Grouped by QRef, and Concatenate(QRef).
then dlookup this query.

access is evaluating first your first query (the one you posted) before grouping them (on second query you posted).
when you can make it into 1.

 

[Snowflake68]

ok, just make a new query Grouped by QRef, and Concatenate(QRef).
then dlookup this query.

access is evaluating first your first query (the one you posted) before grouping them (on second query you posted).
when you can make it into 1.

I tried that originally but couldn't get it to work, i think its either because I have a parameter looking up a reference on another form to filter the results or because I am concatenating a multi valued field.

I might just remove the text box altogether to see if the issue goes away first just to make sure its actually is the query causing the issue.

Thanks gain.

 

[The_Doc_Man]

You have already been given the advice I normally would offer. When you have issues in running out of some limited ability of Access, like how many DBs you can open, your problem is often failure to close something properly. I will state for the record that not everyone agrees with my position; however, we have continued to revisit the issue and I still believe it is a good programming practice to always close what you open.

However, there is another issue: Opening a DB opens a file even if it is the same file you just opened in a prior loop. Failure to explicitly close it leaves some doubt about the file handle that is a Windows resource per-user and for which the system offers a limit. You could be runninging into a "max files" issue. See this thread for more info on the topic:

https://access-programmers.co.uk/forums/showthread.php?t=298876&highlight=Resources

 

[MajP]

I do not see the cause of the problem, but I have seen something similar recently and believe this may be a bug and not that you are creating multiple connections. The proposed solutions in this thread really make no sense to me and some cases are not correct.
For clarity here is a good discussion. You can try the function provided, but it did not fix the case that I can replicate. It would address the suggestions made here for pulling a reference from a persistent variable.

https://stackoverflow.com/questions...verhead-when-using-currentdb-as-opposed-to-db

One suggestion is that the function is in a continuous loop which would explain why that I am unable to exit out of the error and keep getting the same message over and over again and then the only way I can exit out is to use the Task Manager to end the task and close Access

The function is not in a continous loop. The function is instead being called many times. Every row in the query calls it. If you have 1k records and it fails on record 100 then it will call the function 900 more times and give you the error message.

There was a mention that setting a global variable is more efficient than getting a direct object reference from DBEngine(0)(0). I guess, but I cannot imagine it is of any consequence.
You are simply pulling a reference from a collection instead of pulling from a global variable. Nothing is being created or destroyed. Difference would be
Public glblFrm as Access.form
set glblfrm = forms("someform")

now referencing
txtbx = forms("someform").sometextbox
vs
txtbx = glblform

There was mention that should use CurrentDB vice dbengine.

Both return a pointer to the database currently open in the Access UI. DBengine(0)(0) uses an object reference to do it and currentdb() uses a function as a wrapper. The big differences are:

DBEngine(0)(0) returns the collections as they were initialized when the database currently open in the user interface was first open, unless you manually refresh the collections. So, if you add a new saved QueryDef, for it to be available in code using DBEngine(0)(0), after adding the new QueryDef you have to call
DBEngine(0)(0).QueryDefs.Refresh
Before that, your new query won't be in the QueryDefs collection, but after it, it will. CurrentDB, on the other hand, refreshes all collections each time it is called, so you never have to worry about refreshing any of your collections.

DBEngine(0)(0) returns the internal pointer the Access Jet workspace uses to point to the database currently open in the Access UI. CurrentDB returns a copy of the database structure, and each call to CurrentDB creates a new copy. Thus, CurrentDB will use more memory, because it creates a copy of the structure that points to the database currently open in the Access UI, while DBEngine(0)(0) uses no additional memory, because it returns not a copy, but simply a pointer to an existing memory structure. Likely the refreshing of the collections is the reason why CurrentDB is slower , but probably some of the extra time is taken up by the process of setting up the copy of the database object, as well


I modified the code to show that dbengine(0)(0) is far faster

Public Sub TimeDB()
  Dim db As DAO.Database
  Dim i As Integer
  Dim sec1 As Single
  Dim sec2 As Single
  
  sec1 = Timer()
  
  For i = 1 To 10000
    Set db = CurrentDb
    Set db = Nothing
  Next i
  sec2 = Timer()
  Debug.Print "End CurrentDB Elapsed: " & sec2 - sec1

  sec1 = Timer()
  For i = 1 To 10000
    Set db = DBEngine(0)(0)
    Set db = Nothing
  Next i
  sec2 = Timer()
  Debug.Print "End DBEngine(0)(0) Elapsed: " & sec2 - sec1
End Sub

End CurrentDB Elapsed: 6.859375
End DBEngine(0)(0) Elapsed: 0.015625

So it is way faster, but as pointed out no one cares and would write code like that. However, in your case you might care. Not that you would loop opening the recordset, but you are calling your code multiple times from a query. In your case your query could have a thousands of records each calling the concatenate function making a possible difference in time.

Change other similar code to use dbs instead of DBEngine(0)(0). or CurrentDb.
Doing that means you aren't repeatedly setting variables so you don't need to keep closing/killing them
You should still set recordsets to nothing after use

That does not make any sense to me. You cannot open or close currentdb or dbengine(0)(0). Even if you created a local db variable you cannot close it, and setting it to nothing is meaningless. Some old versions of Access will actually crash when trying to close the DB variable based on DBengine(0)(0)

So I would try the function in the link. This does what is being suggested in this thread, but not for the reasons described in this thread. If that fixes your problem I will be surprised.

In your error check you could put the following to check the open recordsets which I doubt is a problem.
MsgBox "Error " & Err.Number & ": " & Err.Description & " Open connections: " & dbengine(0)(0).recordsets.count , vbExclamation, "ConcatRelated()"


As pointed out external databases should be closed, but there is no database being open or closed in the code. You are just pulling a reference.

 

[MajP]

sorry. In your error handler you would add dbengine(0).databases.count to see if somehow you opened other dbs. I was thinking connections.

 

[isladogs]

The proposed solutions in this thread really make no sense to me and some cases are not correct.
For clarity here is a good discussion https://stackoverflow.com/questions...verhead-when-using-currentdb-as-opposed-to-db

I also read that thread in detail before writing my article discussing the use of CurrentDB vs DBEngine. I agree that David Fenton made some excellent points but after that significantly disagree with much of your post.
In fact David Fenton wasn't making the same points as I believe you are making.

There was mention that should use CurrentDB vice dbengine ....
.....
I modified the code to show that dbengine(0)(0) is far faster....
So it is way faster, but as pointed out no one cares and would write code like that.

In the case of the code you supplied DBEngine is indeed far faster. FWIW I got:

End CurrentDB Elapsed: 3.921875
End DBEngine(0)(0) Elapsed: 0.0078125

However the code supplied is (as you suggest) meaningless. It doesn't actually measure the speed of CurrentDB or DBEngine in use when looping through a recordset.
By comparison, my article [url]http://www.mendipdatasystems.co.uk/speed-comparison-tests-5/4594460439 [/URL] compared using both of these in 3 different meaningful ways. The difference wasn't usually massive but CurrentDB was definitely faster than DBEngine in each test I did. I repeated the tests in various different scenarios including different recordsets, multiple workstations and Access versions. The result was always the same.

It may well be that in the days of A97, using DBEngine was faster than CurrentDB. However that is no longer the case

You cannot open or close currentdb or dbengine(0)(0). Even if you created a local db variable you cannot close it, and setting it to nothing is meaningless.

Agreed ...but I often see it done. I wasn't suggesting it should be done.

My recommendation was to set a variable dbs=CurrentDB ONCE and ONCE only. Nothing you have written makes me change that advice

 

[The_Doc_Man]

Concur with Colin on the probable success of the strategy to create a "Public" variable as a DB object for using .Execute and other functions that require a database object. It would be far better to simply create one pointer to the DB early after app launch and neither open nor close it for the lifetime of the app session. The fewer times you attempt to change it, the better.

 

[MajP]

It would be far better to simply create one pointer to the DB early after app launch and neither open nor close it for the lifetime of the app session.

Sounds nice, but what do you think this actually accomplishes, and how is this in anyway "far better" or even better? Why is adding a layer of complexity in any way better? Again there is no "opening" or "closing" or even a variable declaration. You are getting a direct object reference by simply grabbing the open database from the database collection of the workspace object. Would you do this?

Public GlobalFormCollection as Forms
Set GlobalFormCollection = Forms

then in code instead of
Application.forms("somformname").dosomething
you do
GlobalFormCollection("someFormName").dosomething

So now instead of going through the application object to get the Forms collection you have a pointer to the forms collection. That is exactly what you are suggesting is a better approach. Sure it will work but is is not faster, more efficient, or even simpler. And guarantee will have no impact on the OPs original issue.

But going back to the OPs original issue and why this may be intermittent, is that it could be dependent on what other connections are open at the time (forms, reports, controls). If this is a complex db, you could be at the limit if a lot of things are open. I would check the connection count before running the query. The solution from similar threads is to reduce connections. As explained in another thread

The wording of this error message is misleading. It has nothing to do with how many distinct databases you have open. What it is actually talking about is an internal array or collection of what are called TableIDs. Every open recordset -- not just ones that you open, but all the ones that Access is using to load forms, reports, combo boxes, list boxes, etc. -- uses up at least one TableID. And if you are using linked tables, IIRC, each recordset opened on a linked table uses 2 TableIDs.
If you exceed that limit, you get the "cannot open any more databases" error. You might think, "Pfft! How can I possibly use up 2000 TableIDs?" but it's not uncommon for a complicated application with lots of complex forms & subforms open at once to bump into this limit. In your case, since the problem only shows up when you open report in Report view, I have to wonder if the issue arises because data in Report view is "live" -- I *guess* that Access is keeping a lot of recordsets open at once, rather than opening a recordset, formatting the data, and closing the recordset. That's only speculation, of course, because I haven't investigated the way Report View is implemented. Does your report or its subreports use combo boxes to display related data? If so, you may be able to relieve the problem by getting rid of the combo boxes and adding a join to the related table directly to the report's recordsource query. That would eliminate the need for Access to open another recordset (using up 2 TableIDs) to support each instance of each combo box. Similarly, if you use DLookups to get values on the report, you would want to replace them with joined tables to supply the data directly in the recordsource.
Another, though less likely, way you might be using up TableIDs is if you have code behind the report that opens recordsets and (maybe) doesn't close them. Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.htm

 

[isladogs]

But going back to the OPs original issue and why this may be intermittent, is that it could be dependent on what other connections are open at the time (forms, reports, controls). If this is a complex db, you could be at the limit if a lot of things are open. I would check the connection count before running the query. The solution from similar threads is to reduce connections.

That was where the responses to this thread started. See post #2.

 

[Snowflake68]

Thank you all so much for your input. I have a lot to read and digest here so I will come back to you once I am able to comment. Not sure what I will understand as I am no expert like you guys are.

One thing I am wondering is how to properly close a connection once I close a form because it does seem to be the issue here from what I can tell because when I open up the pop up form in design view the VBA window says it is running but when I close the form it appears that code is still be running in the background which might explain why sometimes when I open up the pop up form again in form view that I am unable to edit the values and appears read only.

 

[The_Doc_Man]

It would be far better to simply create one pointer to the DB early after app launch and neither open nor close it for the lifetime of the app session.

Sounds nice, but what do you think this actually accomplishes, and how is this in anyway "far better" or even better?

What the static-variable DB pointer accomplishes, when compared to repeatedly just invoking CurrentDB directly in code, relates to the fact that CurrentDB consumes memory, almost certainly on the heap as opposed to the stack, and there is no good garbage collection for heap-resident items. Stacks are self-cleaning. Heaps? Not so much.

For something that is used once or twice per session, no biggie. The effect is minuscule. However, if a program loop is involved and you DON'T make it a Static variable or use it from a Public variable in a general module, you consume heap space with each call. What is so bad about that is that heap space and stack space share a pre-allocated impure area where the stack goes downwards from the top of that space but the heap grows upwards from the bottom.

If a really LONG program loop is involved, a LOT of heap space is consumed and you might see the dreaded "Heap overlaps Stack" error (or is it "Stack overlaps Heap"?). Doesn't matter which order the error is stated. Either way, it won't crash the system but it WILL crash the process. We've seen that error a few times on this forum.

Granted, another way to see that error involves a LOT of ugly string manipulation inside a long loop. There are other object-related and pointer-related methods, where you are "churning" the referenced object, that will have this effect - even though the pointer itself is not being churned. I am not claiming exclusivity on the cause - only a commonality of effect.

Making excessive references to CurrentDB in a production environment (where the DB.Refresh method isn't an issue) is wasteful of memory in a subtle i.e. not immediately visible way. Because the impure memory segment is pre-allocated, not dynamic, you won't see a growth of virtual memory, and if you are nearing your memory quota you will merely page out the bottom of the heap, meaning that you can't tell anything from physical memory usage either. Remember, since the advent of WinNT, Windows IS a virtual memory, process-quota-controlled, demand-paged, least-recently-used page-out O/S. It will do its best to keep from bothering you about physical memory issues.

Unless you can use some sort of Win32 service calls to determine process internal memory info, you have no easy way of knowing just how close you are to the ragged edge of a fatal heap/stack memory error. And to be honest, since Access is the Main segment and your code will always be a sub, I don't know if you can easily determine the top of the heap anyway.

 

[MajP]

OK thanks, but not relevant to the question asked or the code in question. I am sure you can come up with many hypotheticals where this makes sense. In the code in qustion there is no repeated calls to currentdb only pulling a reference from dbengine(0)(0).