Solved FAYT keeps open connections (1 Viewer)

[dokk]

Hi,

I love MajP's fayt class and I use it constantly. But I have detected a little problem. As I keep opening and closing forms, finally I receive error 3048: cannot open any more databases. It seems that intensively using fayt class leaves opened connections. To check the available connections I use a utility called available connections that I fount in isladogs' page (I can't putting the link it says it's spam )

In a form without fayt combos, I open a form and close it and I have the same available connections.

In a form with fayt combos, after openning and closing the form, I lose as many connections as fayt combos I use in the form. If I don't initialize the filtercombos, I don't lose connections.

Do someone know a way to close those opened connections?

Thank you, and sorry for my english.

 

[MajP]

When I built the FAYT class I did it for ease of use and flexibility and not efficiency. So it is extremely easy to use and requires the user to write one line of code. I probably need to relook at it to see if I can do it more efficiently. I can see why it would leave open connections, but not sure how to fix without testing.

When the code runs it stores the original recordset of the combo. When you type it creates a new recordset by applying a filter to a recordset not by opening a new recordset based off a new criteria. This is inefficient. I would think these connections would close.

Try modifying the terminate event to close the original RS

Private Sub Class_Terminate()
    Set mForm = Nothing
    Set mCombo = Nothing
    mRsOriginalList.Close
    Set mRsOriginalList = Nothing
End Sub

 

[isladogs]

@dokk
See if my free utility can help diagnose the problem:

Available Connections

This utility is used to monitor the number of open databases/tables and prevent 'cannot open any more databases' errors.

www.isladogs.co.uk

 

[dokk]

Thanks to both. @isladogs that's the utility I am using. I've been searching for something like that a lot of time.

@MajP it gives an Error 91 object variable or with block variable not set. Any other idea?

 

[MajP]

I will have to trouble shoot. Although i doubt closing msoriginallist will do anything, do make it the first line of the terminate event. That should avoid err 91.

 

[dokk]

Do you mean this?

Private Sub Class_Terminate()
    mRsOriginalList.Close
    Set mForm = Nothing
    Set mCombo = Nothing
    Set mRsOriginalList = Nothing
End Sub

It returns the same error.

 

[MajP]

Not sure why because does not for me, but would not spend any time. I am not confident it would help.

 

[dokk]

I'll continue checking. Thank you.

 

[isladogs]

@dokk
Apologies for not reading post #1 properly. Glad you found the utility useful.
The reason you can't post links at the moment is that you need a minimum number of posts to do so. This is designed to deter spammers.

Good luck solving the issue

 

[MajP]

@dokk,
I did not modify the class, but simply set all the fayt intances to nothing in the forms close event and this seems to work. Please test too to verify what I am seeing. Using @isladogs tool I was able to open my demo form and then close it without losing a connection. Prior to adding the code I kept losing a connection.
In my demo I had 5 FAYT and closed them.

Private Sub Form_Close()
  Set faytProducts = Nothing
  Set faytProductForward = Nothing
  Set FAYTSuppliers = Nothing
  Set faytCascade = Nothing
  Set faytMulti = Nothing
End Sub

If this really works, I have no idea why this happens and why this works. I would have strongly believed it would not. I have written on this forum that I do not believe in just randomly setting objects to nothing. It is extremely rare that you need to do this. Usually I understand those cases, but do not understand this.
I am a big believer in this article below.

When Are You Required To Set Objects To Nothing?

docs.microsoft.com

However these types of classes are the one time I do set things to nothing in the class. The form references the class and the class in turn references the form along with the combo, and the recordset of the combo.

 

[arnelgp]

I am a big believer in this article below.

it's an Article and i believe is His/Her opinion also (who is the author anyway?)
there are comments that says otherwise (and are also based on experienced).

 

[dokk]

Ok. Let's see what happens. I'm working with a form where I have 3 fayt comboboxes:
faytSuppliers1 → form filter
faytOrders → form filter
faytSuppliers2 → record data

the .rowsource of each combobox is empty and I only set it in the Enter event. For example:

Private Sub supplier_Enter()
    On Error Resume Next
    supplier.RowSource = supplier.Tag
End Sub

And in the Exit event I empty the .rowsource (not sure if this really does anything)

Private Sub supplier_Exit(Cancel As Integer)
    On Error Resume Next
    supplier.RowSource = ""
End Sub

When I open the form I have -4 available connections. If I don't do anything and close it I have +4. This is OK

Case 1: Open form→ focus to cmbOrders → -4 || Close form → +4 → no lost connections
Case 2: Open form→ focus to cmbSuppliers1 → -9 || Close form → +4 → 5 lost connections
Case 3: Open form→ focus to cmbSuppliers2 → -9 || Close form → +9 → no lost connections
Case 4: Open form→ focus to cmbSuppliers1 → focus to cmbSuppliers2 → -14 || Close form → +9 → 5 lost connections

Now I set one by one the fayt to nothing and using it in case 4:

Set faytOrders = nothing → does nothing
Set faytSuppliers1 = nothing → no lost connections
Set faytSuppliers2 = nothing → no lost connections

So, yes. It works, at least with this forms. Setting the faytCombos to nothing closes connections. There is some inconsistency, because there is a difference between cases 2 and 3, but setting faytSuppliers1 and 2 to nothing closes the connections indistinctly.

In any case, it seems that setting all these instances to nothing is a good practice for not leaving open connections.

I think that the thread can be set to Solved.

If I find any other problem I'll keep you informed. Thank you for the help.