Microsoft Access 2007

Posted on 2012-04-06
Last Modified: 2012-04-23
I created a disconnected ado recordset when a form is open

When I open the form in datasheet view, all data is shown correctly.
However, when I right-click and use the built in Filter I recieve either "Cannot apply filter on one or more fields specified in the Filter property" or "Data provider could not be initialized"

Could you please tell me how can I apply filter using disconnected ado recorodsets

Your help is highly appreciated
Question by:RDLFC
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
LVL 40

Expert Comment

ID: 37818399
May be this link can help you:
LVL 75
ID: 37819680
I've been messing with this over the last couple of months, and using the right click FilterBy/For/Excluding seems to be problematic. I have not really found a way around this per se.  Sorting seems to work ok, except ... Remove Filter/Sort. You have to trap that event, then reconnect your dataset.  But, the filtering is problematic - same errors as you get.  You pretty much have to do it via manipulating the recordsets in code.

Leigh Purvis has some good examples ... which I used as a jump start.  He has examples for both List boxes and datasheet forms. But, in the datasheet form example .. Sorting works,  but Filtering has the issue you (and I) are encountering.

You can find the examples HERE

Scroll down toward the bottom to "Bind Any Data to an Access Form by Generating Recordsets "

LVL 75
ID: 37819718
RE "Remove Filter/Sort. You have to trap that event, then reconnect your dataset. "
I'm noticing right now in Leigh's example with datasheet form that ... if you immediately do a Remove Filter/Sort, you get the 'Data provider ...' error. But, if you right click and Sort Ascending/Descending first, then you do not get the error when removing the Sort/Filter.  Again, I believe I was able to trap this On Apply Filter or On Filter event, and just rebind the recordset.  Of course, that seems kind of silly.

So, overall ... kind of problematic.  Let me know what you find.

btw ... Leigh does show how to do Sorting in code, but I don't think filtering.  Also, there are mdb examples to download. cool.

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.


Author Comment

ID: 37820191
mx thanks for the suggestion.  I was able to get the sort to work fine without errors, but as you say the filter is problematic and the source of my problem.

I was playing around with the on apply filter event by re-creating the link to the data and setting the recordset to the form again, but this method kept crashing the database.

I am thinking that if I am able capture what a user is trying to filter I can create a recordset to pull from the source using the criteria entered and reopen the form binding it to the new filtered recordset. How can I capture the criteria being entered by the user?

Another point is that prior to this method I created a passthrough query to a SQL server that is bound to the form to display records, filtering and sorting works fine with that method.  however, I am attempting the disconnected recordset route because I am about to distribute the front end to a number of users and I am assuming once all the users have the same form open and it is bind via the passthrough query performance at the server can be affected negatively and the disconnected recordset is the perfect workaround for this.  am I correct with my assumption?
LVL 24

Accepted Solution

Bitsqueezer earned 500 total points
ID: 37841229

the built-in filters of Access are really unstable, at least when using forms with a high number of records and SQL Server (I guess with other database servers it's the same). I often had problems with these filters, with ACCDB the same as with ADP. The problem here seems to be that filters are no part of the SQL queries, the records will be downloaded completely and then everything which doesn't fit into the filter criteria will be hidden - but still available, changing the filter will not cause a new record retrieving. This advantage seems to be also the disadvantage with filters: I guess the procedures which performs the filters sometimes get out of ressources or have any other problems with a big number of records and at last Access crashes completely in such cases. For example, in my current project a simple continous form which uses only one table in the query and has around 15 simple fields get in trouble with around 80,000 records and higher.

I cannot say anything about disconnected recordsets, I never tried that. Pass-through queries are only a solution if you don't need any updates as they are not updatable. I can tell you from my current project that linked tables/views with a normal connected recordset doesn't make any performance problems with hundreds of users working on the same SQL Server, dozens of them in the same form displaying the same data. But as I decided to end the ACCDB way because of a lot of other problems with this (like always need to update the links when changing something on the server) and changed the complete frontend to ADP I can say that also here is no problem in performance. Unfortunately you have the same problems with filters here although JET/ACE is not been used here (which proves that the Access filters are not part of the JET/ACE machine). The better news: In ADP you have the option to use the ServerFilter instead. Behind the scene this simply performs a new query with the RecordSource applying an additional WHERE with the filter criteria. Normally one would say this must cost a lot more performance as the data will downloaded everytime the filter is changed but that's not the case. Indeed this is the really very much fastest way to filter the data in the frontend, you can enter any complicate filter and press enter and you see no delay in displaying the result (anything around 1 second) where with the Access filters you would have to wait a lot longer. The next is: While Access filters extremly slows down the performance in display if you use conditional formatting the ServerFilters doesn't have this problem, scrolling through the records is as fast as with unfiltered data.

Then you must keep in mind the way Access loads the data and refreshes it: When you open the form it will display the first page really fast and then download the complete recordset relatively slow using Lazy Loading (you see that behaviour with the scrollbar, you cannot reach the end of the table until the load is completed). The idea seems to be that the user gets the form opened very fast and immediately sees the first page of data. The problem with this behaviour is that in both, ACCDB and ADP, this can lead to strange error messages on other user's frontend because normally you use shared locks when reading data (if you don't use READ UNCOMMITTED or WITH (NOLOCK)) and so sometimes another user gets errors because a lot of other users gets the data very slow because of the Lazy Loading behaviour which sometimes locks other users. The solution here is to use a "MoveLast" in recordsets (also with forms) and a "ListCount" on comboboxes and listboxes. This forces Access to download the recordset immediately and quickly, I never had problems with such errors anymore since using this method. The users have to wait a little bit longer until a form is opened but now they can also scroll down immediately to the end.

If you really want to know what Access is doing with SQL Server I strongly recommend to start a SQL Server Profiler session, you will learn a lot about the behaviour of Access and where you can speed up the things.

The connected forms of course have one big disadvantage: You need a 100% connection to the server as Access for example refreshes the data of the displayed page from time to time. If the connection is lost only for a short time (which is often the case with WLAN) the result is often that you need to close the application and start it again, Access is not able to reestablish the connection in a stable way. I didn't found any workaround for this, disconnected recordsets may be a solution, but a lot more programming is needed.

And last I also want to say that using the ServerFilter option of ADP has also some disadvantages: You cannot combine them with Access filters so the built in Datasheet filters must be disabled. The filter criteria must be very exact, any syntax error leads to a form which displays a lot of errors which in the end means you must use the taskmanager to end the application to get out of the error message loop. So you cannot let the user enter the filter completely, your application must check the input and assemble the filter correctly, but then it works perfectly (and as I said above, lightning fast).


LVL 75
ID: 37841347
"When you open the form it will display the first page really fast and then download the complete recordset relatively slow using Lazy Loading (you see that behaviour with the scrollbar, you cannot reach the end of the table until the load is completed)."

And therein lies the problem.  Loading all record (1000's) into a Form when it opens is an antiquated paradigm.  It's only done in Access 'because you can'. In the real world of data entry and other similar manipulations of data, when a user opens a Form, pretty much only one of two things is going to happen:

1) The user is going to add a new record or 2) lookup/find and existing record(s).  So ... consequently, a Form should open to ZERO records.  Then you, the developer provide search mechanism to Find/Search for existing records. This can be done several ways, with the end result either (re)setting the SQL of the Form's RecordSource or manipulating the Form's Filter property.  This approach can be quite effective even with linked tables (over a stable WAN for that matter) and several users.

An alternate approach is a pseudo disconnected paradigm, wherein there are no linked tables (actually, there is one linked table, one field, no records - to maintain a persistent connection/LDB file).  So ... when db opens, you load a local temp table with whatever records (relevant to your situation) from the back end table on the server. User can right click, sort, find a record(s) VERY FAST, then make changes/edits, and hit a 'Submit' button, which 1) in a Transaction finds/updates that record on the server, and 2) if successful, does the same on the local table.

This is the general ideal.  I use this paradigm on 3 of the 25 dbs that I handle at work, which have typically 40-80 simultaneous users.  Now, did I mention that ... this is all being done over a high speed fiber optic 1Gbit super stable WAN, with a 62 mile round trip between our location and our shared drive(s). Corruption is virtually non existent. This is super fast also.  When the user hits the Submit button, it takes typically 0.5 seconds or less (not kidding) to execute 1 & 2 above. Don't believe me?  I invite anyone to stop by and see it in action on any given day.

For the smaller dbs with fewer users, I typically use the first paradigm I described above (linked).  This is just as fast.  And get this:  I have a 'configuration' mdb on the server that ... when any front end loads (local of course), a persistent connection is made to this mdb (same as I described above). This is done because I have a Forced Shutdown Utility (used by my semi-automated backup and compact & repair utility each night) ... wherein each FE pings the config db every 3 minutes - to see if a flag is set, which if so, will activate FSD for that local FE.  The point here is ... I've seen (so far) as many as 170 simultaneous connections to this config db during the day. Of course the theoretical max is 255, but I don't think we will every go over 200.  Typically, there are 105-110.  This encompasses multiple users across the 25 dbs.  Many users have 3-4 dbs open at one time.

So, in summary ... JET & DAO are truly AMAZING.  Many (most in fact) people are living in the past as to what they 'think' ... Access can do.  You see it posted all the time here on EE ... yada yada yada. Yeah, that was all true (re WANs, etc) in the 1990s, even early 2000s.  But guess what? With today's super fast systems and stable networks ... it's a whole new game. It turns out that Access, to a large degree, has only been limited by the state of the art of systems/networks at a given point in time.  

Long Live Access, JET, DAO & VBA. (and I suppose ADO).

LVL 24

Assisted Solution

Bitsqueezer earned 500 total points
ID: 37841418
Hi mx,

yep, you're right: The method you describe is used in the most Internet applications where you usually don't have a connection to the database - you start a request and get some data back. Best example is Google (or any other search machine) where you get a blank page and you must enter what you want to get back - and you get back only the first page of data with a hint how many results you would see if you go on walking through the result pages.
It works perfectly, uses very small resources on the server and the client, it needs no stable connection other than for downloading one result page. Everyone has accepted to work with a (real) big amount of data in the Internet so it would be the correct idea to translate this behaviour to databases.

Unfortunately our "antiquated" world doesn't want that. The user experience is: "I open my continous form with all my stock entries and I want to see what's in the stock, walk through the records, change something here and there, scroll up and down while being in edit mode in some record"... and so on. In fact, what they want to have is to work with the data like working with Excel.
From the "modern" view and the well known Internet experience already accepted by even these users it would be the normal result to also implement the same (or similar) way of working with data also with the local databases. Unfortunately that's not the decision of the developer but of the one who gives the money. And they want it like Excel, the nearer it works like Excel the better. They don't ask for "fine" code or "modern" way of working with data, they only want all their data on the screen, although in fact the most users first filters their data after opening such forms (and they also do it in Excel: Open a file, see all, then switch on the AutoFilter and filter their data...). And so our "good old antiquated" methods will not die to be used as long as the developers are forced to do so. And so we are forced to use such workarounds and help methods like which I described above to get the experience the user wants.

And, by the way, if you think of Google: How often didn't someone find the thing he was searching for because he didn't know what to search for? The forums are full of people who asks things which are answered thousand times somewhere. OK, in a database you have some known fields where you can assume some things like data structure so it is easier than a free-text search but the "feeling" of getting access to the data you want to see is that you are lost on a blank page with only some search fields and also you have the feeling of maybe the data is available but you entered the wrong search criteria like experienced with Google (I often get calls from users which didn't find the data they searched for (which is available) even with the full data listing with filter options - guess how often I would be called with an empty page with search fields only...).

The real joke is: If you would present the same database as an Internet application only the users would again accept the method of the blank search field page... "it's normal in the Internet", they would say.


LVL 40

Expert Comment

by:Vadim Rapp
ID: 37842509
> Could you please tell me how can I apply filter using disconnected ado recordsets

For best results, never touch Access recordsets; let it handle the data by its own means. Technically it's possible, but it really makes Access unstable. Access is most happy and most stable working with bound forms, when nobody but him touches the recordsets it has created for itself. If you need more, consider using Visual Studio.

If in Access you need to do something "manual", create new separate ADO connection with native OLEDB  provider (unlike Access, which is using its own, which is the core reason of this unpredictability), and work with that connection.

On top of that, even without touching the recordset, the filters traditionally have been the source of major headaches in Access for many years, largely because of Access' efforts to remember those filters, make them permanent by storing in the file, etc.

So if  you are using both of these explosive features, it's hard to expect anything but the explosions on every step.
LVL 75
ID: 37843494
"Unfortunately our "antiquated" world doesn't want that. T"
Sorry, but I don't agree with that.  I was doing this long before Google :-)  And again, I invite you to stop by and see 200+ users who don't believe in the old paradigm. And this is why Access gets a bad rap, because it is used in this fashion. How many EE Q's do you see wherein the subject matter is "My db is slow over the network".  Just sayin' ....

"I open my continous form with all my stock entries and I want to see what's in the stock, walk through the records, change something here and there, scroll up and down while being in edit mode in some record"."

Fine. Then Find using * or Like <somecriteria>.  Again, this is done in Access only 'because you can'.  Same with Maximizing forms.  ONLY in Access is this done ... 'because you can'.  Imagine if, when opening a 'form' (register) in Quickbooks Pro it was automatically maximized. That would be maddening. No other professional commercial application does this maximizing gig.   Windows is plural for a reason :-)  MDI means something.

Even our slightly antiquated CSS system (non Access) works this way - for the most part. No one opens up a form in CSS and scrolls through 10 of 1000's of customer records. They look up a record by various means ... account number, customer, etc.  And then of course there are several 1 to many screens for a given customer record.

The 'open form to all the records in the backend' paradigm is the default behavior of Access, and was adopted by most developers, w/o considering the ultimate ramifications ... and hey, the navigation buttons at the bottom are convenient :-)

LVL 24

Expert Comment

ID: 37843715
Hi mx,

I already agreed with you above, you don't need to convince ME..:-)

The problem is, as I also said above, this is not wanted by the people who gives the money to develop the databases. Maybe you are in the lucky situation of having customers which could be easily convinced by your concept, unfortunately I'm not.

Do you want to hear which "great" ideas I got the last days? To write an Excel VBA code which is able to download data from the database so that it can be edited "easily" with Excel and then the Excel VBA tool should upload the data back into the database....
This should give you an idea of what people really thinks about the comfort of an Access database frontend even if it would be displayed as Datasheet to get a similar feeling like with Excel: They would love nothing better to implement the complete Excel into Access...

And now please tell me or show me any Access demo database which works with your (correct) concept which such users / investors would convince.

By the way: MDI is also outdated if you follow Microsoft design rules. They want to push the new browser style to work with backward and forward buttons (see WPF where you can't create MDI forms anymore, only with a lot of tricks).

But to repeat: You don't need to convince me, I personally fully agree with a disconnected concept which loads data on demand and not load data and then decide what I don't want to see. But that's not the real world today. Maybe it is in 10 years if anyone only uses tablet PCs and only talk about apps and clouds - but not today. Humans don't like to change things they had done in certain ways for years.


LVL 75
ID: 37843730
"MDI is also outdated if you follow Microsoft design rules"
Well, when they stop calling Windows 'Windows' ... I will believe that, lol.

Sorry that your customers are living in the past ... I feel your pain.


Author Closing Comment

ID: 37883568
Thanks for the feedback, i now have a better understanding of disconnected recordsets.

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

726 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