CODEWITHSUNDEEP
vbams-accessruntime-error
NewSites
NewSites

Reputation: 1739

MS Access: "Cannot open any more databases."

The Access error, "Cannot open any more databases.", has been discussed several times on StackOverflow[1, 2, 3, 4]. There's also an interesting discussion of the error on Bytes.com, in which Allen Brown and David Fenton weigh in on what can cause the error and argue about whether the limit behind it is about connections or table handles. None of the causes or solutions in those places apply to my situation, as far as I can tell.

I've got a complex database that was working well, but then started throwing this error. I've boiled down the VBA code and data structure to the following bare bones:

Option Explicit

Public Function TestFunction(ID_test_1 As Integer, nID_test_2 As Integer) As Variant

Dim oCurDb_Ftn As DAO.Database, oTestData As DAO.Recordset, sSQL As String, vTestCode As Variant

Set oCurDb_Ftn = CurrentDb()
sSQL = "SELECT * FROM t_test_2 WHERE ID_test_2 = " & nID_test_2 & ";"
Set oTestData = oCurDb_Ftn.OpenRecordset(sSQL)
vTestCode = oTestData![bValue]

oTestData.Close
Set oTestData = Nothing
Set oCurDb_Ftn = Nothing

TestFunction = vTestCode

End Function    ' TestFunction

enter image description here

The numbers in the tables are arbitrary. The fields ID_test_n are primary keys. The code above is the entire contents of TestModule. The query TestQuery is:

SELECT t_test_1.ID_test_1, TestFunction(ID_test_1,3) AS [Test code]
FROM t_test_1;

When the query is opened, it at first appears to be okay. But if I scroll down through it, it throws the "cannot open any more" error: enter image description here

I've discovered one way that I can get rid of the error. If I remove the first parameter of the function TestFunction(), so that it's definition becomes:

Public Function TestFunction(nID_test_2 As Integer) As Variant
...

and the call to it in the query becomes just:

TestFunction(3)

then I can scroll down and up through the query sheet multiple times without error. Those changes are possible because in the bare-bones code of the function, there is no reference to ID_test_1. But in the actual database, that parameter is passed for a reason and omitting it is not an option. Still, it is mysterious to me that whatever is causing the error does not happen if that parameter is not passed.

Can anyone see what's going on here, why I'm getting that error, and how to fix it without excluding parameters from the function?

Environment: Windows 10 Pro 64-bit, Access 2019.

Upvotes: 0

Views: 1786

Answers (1)

Usually Confused
Usually Confused

Reputation: 146

This may be due to a bug in an Office update, about Jan 26, 2022. See Reddit post

Try system restore to roll back the update; or...

Go into Access, File Options, Trust center and add the local front end directory and then check the box to then add the backend data location as a trusted location.

Upvotes: 1

Related Questions