RDLFC
asked on
Microsoft Access 2007
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
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
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 "
mx
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 "
mx
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.
mx
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.
mx
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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).
mx
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).
mx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> 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.
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.
"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 :-)
mx
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 :-)
mx
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.
Cheers,
Christian
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.
Cheers,
Christian
"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.
mx
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.
mx
ASKER
Thanks for the feedback, i now have a better understanding of disconnected recordsets.
http://www.rlmueller.net/ADODisconnected.htm