?
Solved

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

Posted on 2006-03-27
13
Medium Priority
?
555 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:xxxyyy22
  • 4
  • 4
  • 2
  • +3
13 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16306751
Hi xxxyyy22,

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

Regards,

Patrick
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16306812
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.
0
 

Author Comment

by:xxxyyy22
ID: 16306823
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.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16306832
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?
0
 

Author Comment

by:xxxyyy22
ID: 16306941
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?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16306961
Fliters bring back few records?
Are you sure about that?
0
 

Author Comment

by:xxxyyy22
ID: 16306997
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.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 16309005
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: http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20895810.html

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.

Alan
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16309179
A point I was trying to make Alan :-)
Without much success it seems :-(
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 16309215
I got your point Leigh, kudos to you... ":0)
0
 

Author Comment

by:xxxyyy22
ID: 16310341
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.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 16855739
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.

jjafferr
EE Cleanup Volunteer
0
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16880664
PAQed with points refunded (250)

GranMod
Community Support Moderator
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question