UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Cannot Open Any More Database Error - I Think I Know Whats Causing It But Need Help Finding Solution, Access 2007    
 
   
Lateral
post Feb 24 2018, 06:49 PM
Post#1



Posts: 184
Joined: 29-November 13



Hi guys

After using my Access 2007 application for a short period of time (say 10 minutes), I get the following message:

Cannot open any more databases.

I have spent a lot of time googling etc and found a great little utility on another forum that displays the number of used and available connections as I am using the application. I have attached a screen shot of what I am seeing.



The database is a BE / FE design.

Using this utility (it's actually a form) I can clearly see the number of available connections dropping each time I open various forms etc until I reach around 40 available connections and then I open another form and I get the error message. I can also clearly see connections being "returned" when I close forms. There is one form that consumes 100 connections when opened. this form has a number of Tabs that has subforms that have queries attached to them....

How can I solve this issue?

thanks for any help you can provide.

Cheers
Greg
This post has been edited by Lateral: Feb 24 2018, 06:51 PM
Attached File(s)
Attached File  AvailableConnections.png ( 7.78K )Number of downloads: 0
 
Go to the top of the page
 
theDBguy
post Feb 24 2018, 06:54 PM
Post#2


Access Wiki and Forums Moderator
Posts: 72,444
Joined: 19-June 07
From: SunnySandyEggo


How many forms do you have open at the same time when you get the error?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
moke123
post Feb 24 2018, 07:03 PM
Post#3



Posts: 1,279
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



I've had that utility for a long time and I cant recall ever having a form that consumed 100 table connections. Bear in mind that many controls use more than 1 connection and I believe you consume multiple connections when you have a lot of joins in your queries.
Go to the top of the page
 
DanielPineault
post Feb 24 2018, 08:22 PM
Post#4


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



When you get into issues with hitting the maximum connections, you are typically looking at having too many objects open simultaneously, and/or your objects are too complex with too many controls.

The solution is to
  • ensure other objects are closed prior to opening new ones
  • and/or simplify your forms into separate objects that are called on an 'as needed' basis
  • and/or implementing dynamic loading of subforms.



Also, for anyone else who might be looking for the "Available Connections" utility, it can be found at http://accessblog.net/2014/04/

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
zaxbat
post Feb 24 2018, 08:51 PM
Post#5



Posts: 952
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


My first thought is....maybe not closing files and objects and setting them to nothing in VBA routines. Have you checked that?

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Lateral
post Feb 24 2018, 09:01 PM
Post#6



Posts: 184
Joined: 29-November 13



Hi Guys

Thanks for the prompt replies....

Since posting, I have been digging into the application and have discovered a few forms that have multiple tabs that in turn have subforms that are running queries etc.

Based upon my googling and testing, it looks as though these tabs are the main culprits as they are consuming the connections.

I am in the process of changing one of the forms that has 10 tabs so that the subforms aren't automatically being "loaded" when the main form is opened. I am changing the code so that the tabs only load the subforms/queries etc when the tab is clicked.

I have added the following code to the "On Change" event of the Tab Control (I have some debugging code as it is working for the first 4 tabs but not the rest for some reason):

Private Sub TabCtl88_Change()
Select Case TabCtl88

Case 0 'First Page
MsgBox "1"
MsgBox Me.TabCtl88.value

Case 1 '2nd page
MsgBox "2"
MsgBox Me.TabCtl88.value
Me.SubformContainer01.SourceObject = "fWorkorderPartsSold"

Case 2 '3rd page
MsgBox "3"
MsgBox Me.TabCtl88.value
Me.SubformContainer02.SourceObject = "fWorkorderPartsSold_01"


Case 3 '4th page
MsgBox "4"
MsgBox Me.TabCtl88.value
Me.SubformContainer03.SourceObject = "fWorkorderPartsSold_02"

Case 4 '5th page
MsgBox "5"
MsgBox Me.TabCtl88.value
Me.SubformContainer04.SourceObject = "fWorkorderPartsSold_03"

Case 5 '6th page
MsgBox "6"
MsgBox Me.TabCtl88.value
Me.SubformContainer05.SourceObject = "fWorkorderPartsSold_03"

Case 6 '7th page
MsgBox "7"
MsgBox Me.TabCtl88.value
Me.SubformContainer06.SourceObject = "fAuditTrail_02"

Case 7 '8th page
MsgBox "8"
MsgBox Me.TabCtl88.value
Me.SubformContainer07.SourceObject = "fCustomerParts_02"

Case 8 '9th page
MsgBox "9"
MsgBox Me.TabCtl88.value
Me.SubformContainer08.SourceObject = "fAuditTrail_04"

Case 9 '10th page
MsgBox "10"
MsgBox Me.TabCtl88.value
Me.SubformContainer09.SourceObject = "fAuditTrail_05"

Case 10 '11th page
MsgBox "11"
MsgBox Me.TabCtl88.value
Me.SubformContainer10.SourceObject = "fAuditTrail_05"

End Select

End Sub

Zaxbat, you mention:

"My first thought is....maybe not closing files and objects and setting them to nothing in VBA routines. Have you checked that?"

How do I implement this?

Cheers
Greg
Go to the top of the page
 
zaxbat
post Feb 24 2018, 09:17 PM
Post#7



Posts: 952
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


If you create a dataset object and open a file for any reason....you need to always close the file and set the object to nothing before you exit the routine in order to free up connection and memory....other wise you can use up all resources and get errors that don't even make sense sometimes.

But, since you mentioned that the problem seemed to be connected to all of those tabs....that rang a bell in a distant memory in the cobwebs of my moth-eaten brain. Yeah, I had that same problem with a project where I was using tabs like a wildman. Don't recall how I got around the problem...but glad that you found it.
This post has been edited by zaxbat: Feb 24 2018, 09:17 PM

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Lateral
post Feb 24 2018, 09:54 PM
Post#8



Posts: 184
Joined: 29-November 13



Hi Zaxbat

Can you please give me an example of creating a dataset object and then closing it?
Go to the top of the page
 
GroverParkGeorge
post Feb 25 2018, 08:32 AM
Post#9


UA Admin
Posts: 32,835
Joined: 20-June 02
From: Newcastle, WA


The solution you adopted is the most common way around the problem of "heavy" forms. I.e. Forms with many subforms, combo or listboxes.

It has earned a nickname. Some developers refer to it as "lazy loading", or loading subforms into subform controls only when you need them.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Feb 25 2018, 08:38 AM
Post#10


UA Admin
Posts: 32,835
Joined: 20-June 02
From: Newcastle, WA


Here's a short example, with no code other than the recordset:

CODE
Public Function ShowMe()
Dim rst as DAO.Recordset
Dim db as DAO.Database

On Error Goto errHandler

    Set db= CurrentDB
    Set rst = db.OpenRecordset("Select * FROM tblYourtTableNameGoesHere WHERE PrimaryKey = 12", dbOpenDynaset)

    Do something with the recordset

Cleanup:

    On Error Resume Next
    rst.Close
    db.Close
    Set db = Nothing
    Set rst = Nothing

ExitProc:

    Exit Function

errHandler:

    MsgBox "That was bad. Don't do it again."
    Resume Cleanup
    Resume

End Function


Note that I, at least, always include a Cleanup section where I do things like closing objects, rather than in the main code body because, in the event of an error I want it to cleanup after itself every time before exiting.

--------------------
Go to the top of the page
 
Lateral
post Feb 25 2018, 02:30 PM
Post#11



Posts: 184
Joined: 29-November 13



Hi George,

Thanks for the reply.

I have taken over looking after this Access application and it is quite a beast and a lot to learn!

I did a search of the VBA code and found the following example of where it appears not to be properly closing:

Private Sub Save_Kit_Click()

Dim KitName As String
Dim KitDesc As String
Dim checkName As String
Dim KitId As Integer
Dim rs As Recordset
Dim SQL As String

On Error GoTo ErrorHandler


DoCmd.OpenForm "frmSelectKit", , , , , acDialog, "Name"
KitName = "" & Forms!frmSelectKit.Controls!txtKitName
KitDesc = "" & Forms!frmSelectKit.Controls!txtKitDesc

DoCmd.Close acForm, "frmSelectKit"


If KitName <> "" Then
checkName = Nz(DLookup("KitName", "tKits", "KitName='" & KitName & "'"), "")

DoCmd.SetWarnings False

If checkName <> "" Then
If Dialog.Box("Replace existing kit " & KitName & "?", vbYesNo) = vbNo Then
Exit Sub
End If
KitId = DLookup("ID", "tKits", "KitName='" & KitName & "'")
Else
DoCmd.RunSQL "INSERT INTO tKits (KitName, KitDescription) VALUES ('" & KitName & "', '" & KitDesc & "')"
Set rs = CurrentDb.OpenRecordset("SELECT MAX(tKits.ID) AS ID FROM tKits")
KitId = rs("ID")
End If

DoCmd.RunSQL "DELETE FROM tKitsWorkOrderParts WHERE KitID = " & KitId
DoCmd.RunSQL "DELETE FROM tKitsWorkorderLabor WHERE KitID = " & KitId

SQL = "INSERT INTO [tKitsWorkOrderParts] (KitID, [PartID], Quantity, [UnitPrice], Notes, DisplayOrder) SELECT " & KitId & " AS KitID, [PartID], Quantity, [UnitPrice], Notes, DisplayOrder FROM [WorkOrder Parts] WHERE [WorkOrder Parts.WorkOrderID] = " & Me.WorkorderID
DoCmd.RunSQL SQL

SQL = "INSERT INTO [tKitsWorkorderLabor] (KitID, [EmployeeID], BillableHours, BillingRate, Comment) SELECT " & KitId & " AS KitID, [EmployeeID], BillableHours, BillingRate, Comment FROM [Workorder Labor] WHERE [WorkOrderID] = " & Me.WorkorderID
DoCmd.RunSQL SQL

Dialog.Box "Kit " & KitName & " saved."

End If

Cleanup:
DoCmd.SetWarnings True
Exit Sub

ErrorHandler:
Dialog.Box Err.Number & " - " & Err.Description
Resume Cleanup

End Sub



Am I correct in that the Cleanup section is missing the following important bit of code?:

rst.Close
db.Close
Set db = Nothing
Set rst = Nothing



Thanks again for helping me out with this as I want to make the application as robust as possible.

Oh, am I correct in assuming that if I use the utility to check the number of connections and before I open a form it displays that there are 200 connections available and then I open a form and it drops to 150 connections and then I close the form, should I get the entire 200 back again if everything is being done "correctly" (close recordsets etc) when the form closes? Currently, I have a form with lots of tabs etc that before I open it it says that there are 200 connections available and when I open it the number of available connections drops to 150. I then close the form and it say there are 197. I repeat this and it seems to "retain" 3 connections each time I open and close the form.......this form is used a lot.

Cheers
Greg
This post has been edited by Lateral: Feb 25 2018, 02:42 PM
Go to the top of the page
 
DanielPineault
post Feb 25 2018, 02:40 PM
Post#12


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



In a general set, if you Set something you should Set it to = Nothing when you're all done. Similarly, if you Open something, you should close it when you are done with the exception of currentdb, you can't close that, but you should Set it to = Nothing.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
DanielPineault
post Feb 25 2018, 02:47 PM
Post#13


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



I'd probably modify your procedure along the lines of

CODE
Private Sub Save_Kit_Click()
        Dim KitName               As String
        Dim KitDesc               As String
        Dim checkName             As String
        Dim KitId                 As Integer
        Dim db                    As DAO.Database
        Dim rs                    As DAO.Recordset
        Dim SQL                   As String
    
        On Error GoTo ErrorHandler
    
        Set db = CurrentDb
    
        DoCmd.OpenForm "frmSelectKit", , , , , acDialog, "Name"
        KitName = "" & Forms!frmSelectKit.Controls!txtKitName
        KitDesc = "" & Forms!frmSelectKit.Controls!txtKitDesc
        DoCmd.Close acForm, "frmSelectKit"
    
        If KitName <> "" Then
            checkName = Nz(DLookup("KitName", "tKits", "KitName='" & KitName & "'"), "")
            If checkName <> "" Then
                If Dialog.Box("Replace existing kit " & KitName & "?", vbYesNo) = vbNo Then
                    Exit Sub
                End If
                KitId = DLookup("ID", "tKits", "KitName='" & KitName & "'")
            Else
                db.Execute "INSERT INTO tKits (KitName, KitDescription) VALUES ('" & KitName & "', '" & KitDesc & "')"
                Set rs = CurrentDb.OpenRecordset("SELECT MAX(tKits.ID) AS ID FROM tKits", dbOpenSnapshot)
                KitId = rs("ID")
            End If
    
            db.Execute "DELETE FROM tKitsWorkOrderParts WHERE KitID = " & KitId
            db.Execute "DELETE FROM tKitsWorkorderLabor WHERE KitID = " & KitId
    
            SQL = "INSERT INTO [tKitsWorkOrderParts] (KitID, [PartID], Quantity, [UnitPrice], Notes, DisplayOrder) SELECT " & KitId & " AS KitID, [PartID], Quantity, [UnitPrice], Notes, DisplayOrder FROM [WorkOrder Parts] WHERE [WorkOrder Parts.WorkOrderID] = " & Me.WorkorderID
            db.Execute SQL
    
            SQL = "INSERT INTO [tKitsWorkorderLabor] (KitID, [EmployeeID], BillableHours, BillingRate, Comment) SELECT " & KitId & " AS KitID, [EmployeeID], BillableHours, BillingRate, Comment FROM [Workorder Labor] WHERE [WorkOrderID] = " & Me.WorkorderID
            db.Execute SQL
    
            Dialog.Box "Kit " & KitName & " saved."
        End If
    
    Cleanup:
        If Not rs Is Nothing Then
            rs.Close
            Set rs = Nothing
        End If
        If Not db Is Nothing Then Set db = Nothing
        Exit Sub
    
    ErrorHandler:
        Dialog.Box "The following error has occured" & vbCrLf & vbCrLf & _
                   "Error Number: " & Err.Number & vbCrLf & _
                   "Error Source: Save_Kit_Click" & vbCrLf & _
                   "Error Description: " & Err.Description & _
                   Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                   , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Cleanup
    End Sub





Also note that SQL is a Reserved Word and should be avoided when naming objects, VBA variables..., if you adapted a Naming Convention with a prefix you could standardize the code, make it easier to follow and avoid the Reserved Word issue. I'd recommend renaming SQL to sSQL or strSQL.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Lateral
post Feb 25 2018, 03:04 PM
Post#14



Posts: 184
Joined: 29-November 13



Thanks Daniel!

I'll have a look and tidy things up.

Would you please provide an answer to the following question?

Oh, am I correct in assuming that if I use the utility to check the number of connections and before I open a form it displays that there are 200 connections available and then I open a form and it drops to 150 connections and then I close the form, should I get the entire 200 back again if everything is being done "correctly" (close recordsets etc) when the form closes? Currently, I have a form with lots of tabs etc that before I open it it says that there are 200 connections available and when I open it the number of available connections drops to 150. I then close the form and it say there are 197. I repeat this and it seems to "retain" 3 connections each time I open and close the form.......this form is used a lot.

Cheers
Greg
Go to the top of the page
 
DanielPineault
post Feb 25 2018, 04:17 PM
Post#15


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



Yes, it would indicate some connections were opened and not closed.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Lateral
post Feb 25 2018, 04:54 PM
Post#16



Posts: 184
Joined: 29-November 13



Thanks Daniel,

Is there a way to close all open connections at the same time?

Cheers
Go to the top of the page
 
DanielPineault
post Feb 25 2018, 06:06 PM
Post#17


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



I've never done so, but you could try something along the lines of (untested)
CODE
Public Sub CloseAllRecordsets()
      On Error GoTo Error_Handler
      Dim rs                    As DAO.Recordset
  
      For Each rs In CurrentDb().Recordsets
          Debug.Print rs.Name
          rs.Close
      Next rs
  
  Error_Handler_Exit:
      On Error Resume Next
      If Not rs Is Nothing Then Set rs = Nothing
      Exit Sub
  
  Error_Handler:
      MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
             "Error Number: " & Err.Number & vbCrLf & _
             "Error Source: CloseAllRecordsets" & vbCrLf & _
             "Error Description: " & Err.Description & _
             Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
             , vbOKOnly + vbCritical, "An Error has Occured!"
      Resume Error_Handler_Exit
  End Sub


Personally, I'd be going through the code and cleaning it all up. And be careful, because the above code will close all connections, including those of other objects you might have open at the time it is run!

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
moke123
post Feb 25 2018, 07:11 PM
Post#18



Posts: 1,279
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



Just a mention of something that caught my eye in the code you posted in post #11 and one of the pitfalls of using docmd.setwarnings.

CODE
DoCmd.SetWarnings False

If checkName <> "" Then
If Dialog.Box("Replace existing kit " & KitName & "?", vbYesNo) = vbNo Then
Exit Sub
End If
KitId = DLookup("ID", "tKits", "KitName='" & KitName & "'")
Else ...


If i'm following your code correctly it appears that if the user selects no the warnings are not set back to true and you exit the sub.

Go to the top of the page
 
Lateral
post Feb 25 2018, 10:02 PM
Post#19



Posts: 184
Joined: 29-November 13



Thanks Moke123
Go to the top of the page
 
GroverParkGeorge
post Feb 25 2018, 10:06 PM
Post#20


UA Admin
Posts: 32,835
Joined: 20-June 02
From: Newcastle, WA


Hence error handling that includes a Cleanup section where all of that has to be performed before the sub exits.

Instead of

Exit Sub

you would have

GoTo Cleanup

I always have as single place in a Sub or Function that exits it, and always after any cleanup code.


--------------------
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    24th June 2018 - 02:16 AM