Subform is linked to SQL Server and hangs up when removing a filter

I have a large SQL Server table (9 million rows) linked to Access. This table is used in a subform. I can filter this subform and the response is very good assuming I have indexed it on SQL Server. When I click to remove the filter the screen hangs for a while. Sometimes I have to kill Access. I do not know why this is happening.

Also when I remove a filter from one subform it removes the filter from another subform on the same master form.

Thanks in advance
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Hi xxxyyy22,

It's hanging because bringing back 9 MM records via ODBC is slooooooow.  Don't remove the filter :)


Leigh PurvisDatabase DeveloperCommented:
Bringing back 9 million record via magic fairy dust or quantum entanglement would be sloooooow.
Just don't do it.
Don't even filter.

Limit the records on the server - fetch the bare minumum.
No user (or group of users however large) could ever make use of 9 million records.
OK Jet isn't going to display them all - but there's no point in even asking it to.
xxxyyy22Author Commented:
Most of the time the remove filter works fine and displays instantly. Other times it hangs.

The filtering is immediate.

Access is obviously not bringing back all 9 million records into Access.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Leigh PurvisDatabase DeveloperCommented:
No it won't.
Well - Jet won't anyway.
But nor would you want it to - and to ask it to is surely utterly pointless.
Why drag completely pointless number of records over the wire - straining the network and server (and indeed Access and your processor) unecessarily?
xxxyyy22Author Commented:
I am using this as a reference file. The user needs to use this file for lookups.

Filters bring back few records.

I think the hanging has to do with sorting the filtered set and then removing the filter.

Maybe the sorting remains?
Leigh PurvisDatabase DeveloperCommented:
Fliters bring back few records?
Are you sure about that?
xxxyyy22Author Commented:
Yes, I am testing this now and I am only bringing back a few records (< 50).
When I have a sort and remove the filter it is slow to come back.
Alan WarrenApplications DeveloperCommented:
Is the app an ADP or an MDB?

If ADP then consider writing a stored procedure to return filtered recordsets, you can execute this serverside.
Example serverside fltering here:

An MDB will always return a clientside cursor, this is a limitation of the jet engine, though data pooling may give the illusion of good response for a while, eventually you will hit the wall.

Leigh PurvisDatabase DeveloperCommented:
A point I was trying to make Alan :-)
Without much success it seems :-(
Alan WarrenApplications DeveloperCommented:
I got your point Leigh, kudos to you... ":0)
xxxyyy22Author Commented:
This is an MDB.

I seemed to have solved the problem by resetting the recordsource of the subform in code.

This refreshes the subform and filter with no hesitation, even with 9 million records.

There is definitely an issue with removing the sort portion of the filter.
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    PAQ with points refunded

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer
PAQed with points refunded (250)

Community Support Moderator

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.