Toggle Filter Causing Multiple MSSQL Connection Threads to Remain Open

Question

When I toggle from Filtered to Unfiltered I am getting many (25) threads of the same connection in MSSQL Server.  I am on Access 2013 with a MSSQL 2012 DB Server.  I have a simple form with a grid that is bound to a linked table.  I understand that there is a work around for this by EOF then BOF for the recordset.  Outside of this, is there any other way to correct this behavior?

Answer 1

Hi Rick, Is your form bound to only one table? Do you have any combo or list boxes on it with row sources pointing to other tables? Just curious...

Answer 2

Hi,

Sorry for the delay.

it might be related with your forms and how you bind with linked table.

I suggest you share more information with us, so that we could reproduce and check if there any solutions or workarounds and how using EOF and BOF could work around your issue.

Regards,

Celeste

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

I have been out so no problem with any delay.  The form is bound to a single linked table.  On initial load I do not see any additional connections, it just when the grid is unfiltered when the multiple connections are created.  I am guessing that each connection is a page, there are 102440 records.

Answer 4

When you say you are using a "grid" I take it you are referring to an ActiveX control. I don't use that control so I can't say as to the number of connections to expect. If you use the native controls you shouldn't see all those connections.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 5

It is the native grid of the form and is defined as the Record Source of the Form.  When the form first loads all is good, it is after I unfiltered that I see multiple sessions of the same SID.  If I go to the last record in that record set then there is only one SID.  I am viewing this in Activity Monitor of MSSQL Server Management Studio.

Answer 6

Hi Rick, I never saw this issue here.

Besides, suggest you to change the source of your datasheet from linked table (I presume you did it) to a passthrough query. You can work with the datasheet filters as well.

Answer 7

Thanks Allan, Yes I have tried a passthrough with the same result.