Cannot open any more databases Error

[LGDGlen]

Hi All

So i am trying to generate a complex excel sheet from data from my database.

I have currently 4 complex queries (but the need for at least 2 more) some of which use the results of 1 to generate total lines for the sheet. Also as part of some of the queries i am doing currency conversions based on dates to get correct currency rates.

I have been slowly building my sheet a bit at a time and it was working until i added the 4th part to it. This is where i hit too many databases open issue. I know people like to see full bits of code but there is so much involved i am unsure its worth it in the post. The parts where the error is seen is:

    Debug.Print fOpenDatabaseCount
    strLevel1SQL = Replace(Left(CurrentDb.QueryDefs("qryReturnsExcelSheetsLevel1").SQL, Len(CurrentDb.QueryDefs("qryReturnsExcelSheetsLevel1").SQL) - 3), "ORDER BY", "WHERE [FACT-Consignments].cons_albaran In (" & selectedAlbarans & ")  ORDER BY")
    Set level1RS = CurrentDb.OpenRecordset(strLevel1SQL)
    Debug.Print fOpenDatabaseCount
    strLevel1TotalLineSQL = Replace(Left(CurrentDb.QueryDefs("qryReturnsExcelSheetsLevel1TotalLine").SQL, Len(CurrentDb.QueryDefs("qryReturnsExcelSheetsLevel1TotalLine").SQL) - 3), "ORDER BY", "HAVING [FACT-Consignments].cons_albaran In (" & selectedAlbarans & ")  ORDER BY")
    Set level1TotalLineRS = CurrentDb.OpenRecordset(strLevel1TotalLineSQL)
    Debug.Print fOpenDatabaseCount
    strLevel2SQL = Replace(Left(CurrentDb.QueryDefs("qryReturnsExcelSheetsLevel2").SQL, Len(CurrentDb.QueryDefs("qryReturnsExcelSheetsLevel2").SQL) - 3), "WHERE ((([FACT-Deliveries].del_rejected_del_id) Is Null))", "WHERE [FACT-Deliveries].del_rejected_del_id) Is Null And [FACT-Consignments].cons_albaran In (" & selectedAlbarans & ") ")
    Set level2RS = CurrentDb.OpenRecordset(strLevel2SQL)
    Debug.Print fOpenDatabaseCount
'    strLevel2TotalLineSQL = Replace(Left(CurrentDb.QueryDefs("qryReturnsExcelSheetsLevel2TotalLine").SQL, Len(CurrentDb.QueryDefs("qryReturnsExcelSheetsLevel2TotalLine").SQL) - 3), "ORDER BY", "HAVING [qryReturnsExcelSheetsLevel2].[Supplier Ref] In (" & selectedAlbarans & ")  ORDER BY")
'    Set level2TotalLineRS = CurrentDb.OpenRecordset(strLevel2TotalLineSQL)

What i am essentially doing is gathering a groups of groups of records which i intend to put on separate sheets, so i gather the group of records into a record set to loop through a filtered version of the recordset to put on each separate sheet.

When i do this i get the following output of databases used:

Before we start 226
After first record set is created 132
After second record set is created 38
After third record set is created 13

So if i uncomment the 4th record set creation lines it fails as expected as we only have 13 left at this point

I really wanted to do all of this with queries as initially i was gathering total values manually whilst i go through the first and third record sets to populate total values on the sheet.

I also want to total the total lines and put that on a separate sheet and have a query that does that. And there are requests for more information on the sheet.

So i know that i've probably not done this in the best way and i'm sure there is a better way (i've seen people talk about temporary tables but not sure whether thats something i should use or not)

I've attached the full VBA code to this to give more context about what i am doing as it might help. any help or pointers would be gratefully appreciated.

Kind regards

Glen

 

[Gasman]

Search here for that phrase. That appears to be another MS bug?

 

[LGDGlen]

Apologies, the title is not the correct error.

its

"Cannot open any more databases"

if i ignore the error the 4th query returns 0 results even though if i run the query on its own it returns the values i expect

I've updated the title with the correct wording of the error. sorry @Gasman

 

[GPGeorge]

This does indeed appear to be the newest bug release in the latest Current Channel update (Version 2101, build 14827.20158), which just went to production on Jan 26. Roll your version back and see if that corrects the problem. Stay tuned for more details and possible confirmation from Microsoft.

 

[LGDGlen]

@GPGeorge oh, grrr, ok, i'll take a look at the versioning information and see what i can find, thank you for your update.

 

[LGDGlen]

i'm currently running Version 2112 (Build 14729.20260) it does say there are updates available

 

[LGDGlen]

I have noticed a few people in the office who are having issues with shutting down Access in that when they do it doesn't shut properly and they are on the version you mention 14827.20158

 

[GPGeorge]

Thanks for that confirmation of the version. Microsoft is aware of the problem, but I don't think they've yet worked out an official response. As far as a I can tell, they're still trying to pin it down exactly.

 

[LGDGlen]

@GPGeorge so does that mean it affects the current version i am on and the version you mentioned?

 

[Gasman]

Problems With Office Version 2201

Hello All, Our works PC's were automatically upgraded to Office version 2201, which has caused havoc with our in-house database. Even with only a couple of forms open, we were getting "Cannot open any more databases." The cure was to revert back to version 2112. Has anybody else experienced the...

www.access-programmers.co.uk

 

[GPGeorge]

@GPGeorge so does that mean it affects the current version i am on and the version you mentioned?

I really don't know that level of detail, sorry. it's an evolving discovery process.

 

[LGDGlen]

@Gasman thank you, it still looks like it shouldn't be affecting my version but i can see the issue on others who have updated to the version specified.

i'll look out for an new update and hope it fixes my issue as well as the other users

 

[isladogs]

The affected version is 2201 build 14827.20158
Many reports today about this issue on various forums.
As George stated, MS are aware of the issue from feedback they have already received today

 

[LGDGlen]

If you add the folder which the front end resides into the Access Trusted Locations:

File->Options->Trust Center->Trust Center Settings->Trusted Locations

this (for me anyway) fixes an issue that came about as part of the update in that when you shut down Access a process remains running in the background holding onto the lock file so when you try and restart it won't let you

This DOESN'T fix the too many databases issue but does resolve a niggle which has been annoying me since the roll out of the update, just wanted to let others know if they were having similar issues.

I have been checking but can't see any update from Microsoft about a resolution to the issues and when it will be released though