Information on optimizing ADO transactions?

Banana

split with a cherry atop.
Local time
Yesterday, 23:19
Joined
Sep 1, 2005
Messages
6,318
I'm probably not doing a good job in getting information about how I would generally want to handle ADO objects, such as Connection and Recordsets.

One example is when a table was linked in Access and the recordsource is properly written, Jet will fetch only a few rows at a time.

This is not true with ADO recordset, even asynchronous. Maxfields seems to be just a TOP clause, thought I certainly could use CacheSize property to hold more records in memory.

Ideally, I would like to have same behavior as Jet, fetch only few records as needed and fill up the recordset, with periodical resync to ensure that the data stays current. But maybe I'm supposed not to expect that behavior and do something else.

Can anybody enlighten me on how to ensure that I'm conservative as possible with ADO recordset without having to requerying for new records due to excessive limitations or whatever?
 
Generally in a disconnected world when it comes to working with back-end data (say, in ASP.NET) the (current) best practice is to be chunky rather than chatty. That is, you should usually grab as many records as you think you might need from your back-end and bring them on over to your presentation layer. Access is funny, though. It's connected and calls to the data store in a desktop DB are very fast and all neatly done for you, hiding much of the complexity so you generally don't have to worry about this stuff...which is why you can usually develop faster using Access rather than something like Java or .NET...

Not sure if that was helpful... I'm sure you know as much... But reading this and another one of your posts you seem to be evolving (or devolving?) toward a .NET or Java world...

Regards,
Tim
 
Interesting!

I would have thought it better to fetch few records to load the form, and fetch the rest just in time, rather than sucking a full table across the network and cross your fingers that nobody else made edits to the same table. Of course, I had assumed that having a big local cache would relieve the "chatty" aspect.

In that respect, Access is pretty good so the end users get quite current dataset, and that would be the approach I'd stick with had not Access chose to be a stubborn prick about handling transactions. It has a tendency to Commit transactions when there wasn't any explicit commit on my part, and for my needs, is unacceptable. So hence me looking at ADO connections, rather than linked tables and managed queries, as an option.

Love your last assessment. ;)
 
I would have thought it better to fetch few records to load the form, and fetch the rest just in time, rather than sucking a full table across the network and cross your fingers that nobody else made edits to the same table.

Most DBMSs lock all the records in a result set while that result set is being fetched so there's no worry of someone modifying your data during the fetch. Access is good about only loading the records it needs immediately and casually kind of getting the rest in the background.

Of course, this causes another whole set of problems, especially if the recordset is large. In that case, it is best to limit your result set to the data you need now/soon, in a multi-user environment.
 
That's just the another thing- I don't want to have too many locks just to read the data. Furthermore, how would one handle dealing with a table where generally all rows are needed? (Say that this table represents a list of all active clients for a business, and there's no good WHERE clause to narrow the set because the end users can be unpredictable)

Hence my expectation that it should be easy to pass off a SQL statement like "SELECT * FROM foo;" and get only rows you needed just in time, even if you move to random rows all over the table.
 
Hello Banana!
(That just never gets old...)

There's been quite a bit of chat in this thread about how Access behaves (primarily towards a Jet BE - but applies to linked tables too, as Jet is doing the parsing).

It is indeed designed to be iterative in it's processes.
You call a query or a form in the Access UI - a single record is loaded (or a sufficient batch to fill the available displayed rows - often with a cache enough to go beyond that). The rest of the data is loaded in batches as Access sits idle (or you explicitly request to navigate to the end of the recordset - in which case it is filled immediately).
This behaviour mimics DAO quite closely. (No surprise there - with the three springing to life so synchronously (Access, Jet and DAO).
Technically all separate technologies. (In reality - more like born to complement and facilitate each other).

However when we're talking about ADO - we can't easily apply Access behaviours.
Unless we're in an ADP (where we're kinda dealing with ADO as the underlying technology) then ADO data access is not influenced at all by Access being the VBA environment from which the calls are made.

Sure if you're binding an Access form to an ADO recordset then you'll perhaps see some UI lag. But there's every chance the data is loaded by then (i.e. your complete recordcount pops up all the quicker).

The fundamental issue to how ADO data is loaded speaks to the versatility of it. i.e. what does your code look like?
Cursor location and type are very big influences on the type of behaviour you get. The Cursor services job (be it the DB's service or ADO's) is to fetch what you need when you need it.
So by that token we can see that not everything should necessarily be loaded. However it can work that way.

ADO (and hence Access) can, of course, be coerced into the .NET mentality of disconnected bulk fetches and updates.
And IMO there's nothing wrong with doing that - from within Access!
I'm not an advocate of using Access in an unbound manner either - but I feel strongly that the option that Access offers to behave in any or all of these manners within the same application gives it great strength.
(Yes - the default binding offers faster development time than code accessed data which in turn is faster than going fully unbound. But the choices are there :-)

As for the issue of the table where all rows are needed - I say again we need to look at the scenario.
Are the rows likely to change? i.e. if we maintain that table data locally - might the data be changing on the server and we need to know that?
Or can we use a local cache of the data throughout that instance of the application. Perhaps refreshing it a defined intervals.

In one scenario where I've interfaced with existing server data - a convoluted query returned a set of rows. To run that every time would be madness. So it was deemed acceptable that a daily refresh of a local cache was enough for that data. (Which is even beyond an application instance dataset).

Dynamic recordsets (for full, live visiblilty) are expensive - but when they're truly needed so be it.
 
Let's check if I'm understanding you right.

Basically, how a recordset loads is not really important, as the actual acts of fetching are all wrapped up in cursor services?

(Incidentally, that reminds me- I was quite baffled when I wrote an asynchronous query which showed up in MySQL's log as single query, rather than a set of fill-it-up-as-we-go queries)

This may be a bit of problem, because from what I understand, asynchronous query that really fetch only few rows at a time is only available if you use a server-side cursor, which is not available if you want to bind this ADO recordset to an Access form. Furthermore, the only cursor type for client side is Open Static cursor, which are indeed updateable, but won't expose others' edits/additions/deletions without requerying.

As I have argued before, when you go unbound, you might as well go the way of .NET, because the biggest benefit of Access is that it does the heavy lifting for you. However, I could never get around the problem of Access's commiting transactions when I haven't told it to do so. This is particularly frustrating because while Access's general behavior is generally sane for most scenarios out there, it's just not modifiable whenever it is needed. So, I'm basically thinking of developing forms using linked table, then at runtime, bind it to ADO recordset with same SQL statement....
 
Couple things I want to check on:

Asynchronous ADO recordset simply means that we fill the recordset in background, but doesn't necessarily mean we only fetch few rows as we need a la Jet's behavior with bound form.

I suspect that "Server Side Cursors" is not actually on the server itself but rather with the ADO provider... correct?

I could just go for a chunky method, and use BatchUpdates even on disconnected recordset to synchronize without requerying, correct?
 
Hi Banana.

Sorry for the delay.
This is a big subject - and I wanted to wait until the weekend to address it properly.
OK - having tested a few things to confirm my belief of the recordset asynchronous processing (it's not something I really use - have just "played" with it in the past).

Am I correct in saying that your ultimate goal is to bind an opened ADO recordset to your form (to wrap the entire process up in a transaction which you can control)?
If so - then a) that's fine, it's entirely possible and b) good we're on the same page.

The crux comes that you hope to open said recordset asynchronously?
Presumaby because you have a lot of data, or a query which crunches a good bit before returning data?

The processing of the query request is, of course, done on the server.
So if you have a lot of crunching to do - that must be performed first before the server can begin making the rows available.
If it's purely a numbers game then it is about how we aquire the data.

Just a few comments as we meander to the point.

Jet doesn't fetch data only as required - but as requested. i.e. a query request is completed. An Access form's recordset is filled progressively - whether we ask for the remaining records or not. It's just done in a background process unless we ask to navigate to the end (or just beyond where it's filled thus far) and then the local cache needs to be filled without delay.

An Access form can be bound to either a Client or Server side cursor.
(It's the list controls that demand it be Client Side).
Though you'll only be able to get Asynchronously fetched recordset events by using a client side cursor.
Your comment of
"the only cursor type for client side is Open Static cursor, which are indeed updateable, but won't expose others' edits/additions/deletions without requerying"
is indeed correct - but, of course, the inability to see the changes to the result set by other users is an inevitable consequence of having fetched the records to a local location. It's a trade off, a choice, as we make so often in development - choosing one technology for a particular reason - accepting that has certain implications. I personally view Clientside cursors as a wonderful option - and happily accept the logic of their use (/limitations ;-).

A client side cursor loads the recordset into a local (on the client PC) cache - and the cursor service is then implemented by ADO. The cursor still only holds the record(s) specified to be maintained in the cursor - but with a Static cursor type (as your rightly pointed out is inevitable) we are loading the full record into the cursor when we request it. (Keysets load the PK values only in the recordset and call the rest of the record data only when required into the cursor).
Loading the entire dataset into the local data cache takes time.
This is where the potential for asynchronous recordset fetching can seem advantageous.

A server side cursor is implemented by the database engine - and it is responsible for navigation and data fetching. The choice of cursor types is, as we well know, greater here - particular in, say, SQL Server (compared to Jet) where the Dynamic cursor type is supported (with all its inherent expense). The cursor here is responsible for fetching the required record(s) from the server. Options such as Keyset and Dynamic exist on the server - where changes to the records can be easily viewed in the recordset without another round trip (which would be involved in a client side - and hence very much negate the point of it). A Keyset offers the potential advantage of only loading PK values - and fetching full records into the client (ADO) cache only upon request from the cursor service. I believe you'll see something like asynchronous behaviour in that scenario anyway.

An asynchronous client cursor recordset itself offers a way to read from an opened recordset while it fills progressively (and ADO exposes events which allow us to monitor this progress).
You can see the asynchronous behaviour of such recordsets in code.
(And use ADO's events to observe the iterative filling).
However IMO binding such a recordset to a form adds overhead and removes the ability to use that incremental nature. The query wants to complete before binding in any case.
You get some data from the client-side static cursor - but you can't work with it until the processing has completed.
While Form binding of recordsets has improved since it's inception in Acc2000 - it still doesn't support all the functionality that ADO offers.
I still think it's a brilliant feature and a vital piece in Access' toolbox.

My advice would be to try a server side Keyset cursor.
See how your performance goes.
(Bearing in mind I'm still not entirely sure what your specific scenario is - or what code you're running to implement it).

As for disconnected recordsets (again compulsorily client side by definition) they are an option.
As you mention - you're still constrained by requeries on the data for updates. But IMO that's exactly how it should be.
You'll be waiting on that data loading still though again. Just as with any client side cursor.

As for the old bound / unbound argument - oh I'll bet I've been through it more lol ;-) My way of thinking is there is indeed less point developing in Access operating in a fully unbound methodology throughout.
But, as a tool for a job whenever required, unbound forms (combined with local tables, reporting and so many data oriented features) leave me more than content using Access in such a scenario. But only when it's merited - not for the sake of it or some falsely perceived lack of control.

Hopefully there's something of use in that monologue anyway.

Cheers.
 
Banana,
Forms are a window into a recordset and they deal with a single record at a time. The scope of a transaction for a form is from when the bound record is read until the bound record is saved. The transaction is committed at that time or cancelled if you elect to cancel the update with Cancel = True or Me.Undo.

Within the scope of a form's transaction you can create your own transaction with DAO/ADO that encapsulates some other update activity and you would have complete control over when that transaction was committed because you created the transaction rather than Access.

Since a form shows only a single record at a time (continuous forms and datasheets aside), what would be the point of bringing an entire table client side? Are the users going to page through the recordset a row at a time? Are they going to randomly access other records? It would be better to add unbound search fields in the form's header and let those control which records are selected. Retrieving a single record, especially if your indexes are set up optimially, is extremely fast. So fast that it is truely not worth the problem you are trying to solve.

I would only worry about transactions and cursors if I had a non-form related batch process that needed to read/update large sets of data and I couldn't perform the update with an action query.
 
Am I correct in saying that your ultimate goal is to bind an opened ADO recordset to your form (to wrap the entire process up in a transaction which you can control)?
If so - then a) that's fine, it's entirely possible and b) good we're on the same page.

Yes, that's correct. There is one more piece of information that I believe needs mentioning. The only reason I wanted more control over transaction is only because I wanted to have a transaction that would be able to span not just the form but all of its subforms. AFAIK, this is not possible with customary bound methods, and I've had a thread months ago asking this specific questions. This is a case of me wanting my cake and eating cake- I want to see if I can somehow accomplish this task without resorting to fully unbound approach.

The crux comes that you hope to open said recordset asynchronously?
Presumaby because you have a lot of data, or a query which crunches a good bit before returning data?

It's more like this- I want to be able to tell my users that there are so many records and they can choose any and go to there. However, I'm starting to question the merits now that Pat has pointed out that it could be accomplished just easily with a good unbound search combobox. I think that my uncertainty is because I don't want to force my users to just search only one column, or waste server's cycles by requerying for everything, which a carelessly written unbound form has a potential to do.

Jet doesn't fetch data only as required - but as requested. i.e. a query request is completed. An Access form's recordset is filled progressively - whether we ask for the remaining records or not. It's just done in a background process unless we ask to navigate to the end (or just beyond where it's filled thus far) and then the local cache needs to be filled without delay.

And this is what I really like- Jet really does a wonderful job and I am kind of wondering why this has not been emulated in other applications outside of Access.

An Access form can be bound to either a Client or Server side cursor.
(It's the list controls that demand it be Client Side).

Hmm, so a form without comboboxes and listboxes would be able to use server side cursor, even for ODBC? I wonder...

Though you'll only be able to get Asynchronously fetched recordset events by using a client side cursor.

Correct. Even so, I don't know if this is really the same thing as Jet is doing. As I indicated earlier, my log shows different things between Jet's querying (e.g. retrieving up to ten rows every so often period) compared to ADO's querying (e.g. single SQL statement sucking in all rows to satisfy this request).

[qutoe]It's a trade off, a choice, as we make so often in development - choosing one technology for a particular reason - accepting that has certain implications. I personally view Clientside cursors as a wonderful option - and happily accept the logic of their use (/limitations ;-).[/quote]

Right, this makes perfect sense. The only thing I'm not quite 100% sure is whether it's possible to go and tell server to give me the new or updated rows only for this static cursor.

However IMO binding such a recordset to a form adds overhead and removes the ability to use that incremental nature. The query wants to complete before binding in any case.
You get some data from the client-side static cursor - but you can't work with it until the processing has completed.
While Form binding of recordsets has improved since it's inception in Acc2000 - it still doesn't support all the functionality that ADO offers.
I still think it's a brilliant feature and a vital piece in Access' toolbox.

Wait, I think this is fairly important point here. If I'm reading you right, even if we had an asynchronous query, Access will force us to wait for the entire table to load because it can't bind the recordset to the form before then?

If that is truly the case, then there is really no point in using ADO anyway, (e.g. for most scenarios where we usually use Jet)?

As for the old bound / unbound argument - oh I'll bet I've been through it more lol ;-) My way of thinking is there is indeed less point developing in Access operating in a fully unbound methodology throughout.
But, as a tool for a job whenever required, unbound forms (combined with local tables, reporting and so many data oriented features) leave me more than content using Access in such a scenario. But only when it's merited - not for the sake of it or some falsely perceived lack of control.

Yes, I can understand how one or two unbound forms would make sense in some scenarios.

However, when you consider that one can just build a bunch of bound forms without any line of code, without worrying about if you had introduced a bug in your elaborate runtime binding code, it's almost a no brainer in most scenarios.

It's only the transactions across the linked subforms that I can't really get a handle on, because when it's linked, Jet wants to commit everything ASAP, and this plays havoc with my transaction signals.

Hopefully there's something of use in that monologue anyway.

Cheers.

As usual, Leigh, your monologue was valuable. :)
 
Banana,
Forms are a window into a recordset and they deal with a single record at a time. The scope of a transaction for a form is from when the bound record is read until the bound record is saved. The transaction is committed at that time or cancelled if you elect to cancel the update with Cancel = True or Me.Undo.

I didn't make this explicit as I was concentrating on understanding the ADO, but the only reason I was investigating this was because I needed control over transactions for the linked subforms, which we had discussed months ago in another thread. IF it was all within single form, then you are absolutely correct that we only really need a BeforeUpdate event to transact the updates/inserts/deletes.

Since a form shows only a single record at a time (continuous forms and datasheets aside), what would be the point of bringing an entire table client side? Are the users going to page through the recordset a row at a time? Are they going to randomly access other records? It would be better to add unbound search fields in the form's header and let those control which records are selected. Retrieving a single record, especially if your indexes are set up optimially, is extremely fast. So fast that it is truely not worth the problem you are trying to solve.

Quote a excellent point- I may have been digging a hole that I forgot to look around. There's just one thing though. I'm just not sure exactly yet how I want to allow my users to search by what criteria. Suppose I provided a dynamic search functionality that allows them to select any columns to search by, wouldn't that necessitate bringing over the whole table anyway?

I would only worry about transactions and cursors if I had a non-form related batch process that needed to read/update large sets of data and I couldn't perform the update with an action query.

In my case, I do not want to have a partial inserts/updates caused by filling in a parent form, then moving away from the record without completing the child subform, among other business rules, and I think this necessitate a transaction. Yes, I can write a validation routine in the BeforeUpdate to trap such movements and raise a flag to force users to complete the required subforms, but I've already discovered that it can be bypassed.

You know what? Now having written this paragraph, I find this very very ironic that back then when I had found the bugs, I decided to myself, "better to use the built-in functionality than me re-inventing wheels" and tried to use transactions, but here I am, re-inventing wheels! :)
 
To be honest - I'd viewed this as largely some intellectual exercise you were engaged in rather than some final user based functionality.
Exploring rather than decision making.

Generally - entire tables held in memory locally are a bad idea.
It goes a long towards entirely negating the point of a client server application. The server isn't doing the heavy lifting to make requests passed over the network efficient.

However I can never accept that any learning is pointless. It is its own reward. ;-)
So - to continue on a bit about the recordset fetching...

Binding to the forms to encapsulate a transaction around subforms too etc is pretty much exactly what I was expecting your reasoning to be.
Bear in mind the alternative that is one continuous forms equivalent to unbound forms and that's a temporary local table filled with server data, edited locally and then written back to the server only if explicitly requested. It isn't even entirely necessary to have that local table for the continuous form.

I see now the analogy you were making was of Jet querying an ODBC source - hence the several requests you were seeing for data.
There is no analogy for that Access functionality in direct data access (i.e. via code recordsets).
And if you're sticking with ODBC for your data request - then yes, a client side cursor is likely your only choice. Is there a reason you're not using the OLEDB provider instead of ODBC?

>> "The only thing I'm not quite 100% sure is whether it's possible to go and tell server to give me the new or updated rows only for this static cursor."
No - it's not. It's simply not what they were designed for. Not what any recordset type was designed for. Requeries are expensive. It's just the name of the game. If you want updated data you have to pay for it.
To receive recent updates only would require a large amount of participation by the database engine - and ADO in particular can't assume that (being designed to work for a wide range of providers - not necessarily even RDBMS).

>> "Access will force us to wait for the entire table to load because it can't bind the recordset to the form before then?
If that is truly the case, then there is really no point in using ADO anyway"
That's about the size of it - but I disagree with the conclusion.
It's about using technologies appropriately.
ADO is designed for precise data selection. Attempting to use it for an entire table is forcing it into a hole for which it's not intended.
Async fetching works for ADO in code only. It's just in binding to forms (as I said - an impressive but not perfect piece of functionality) that it loses some of that.
A targetted, fast loading ADO recordset would have no such problems though. Async or not it would be irrelevant.


And then on to just general application implementation points (or good database sense as it's also known lol)

>> "I'm just not sure exactly yet how I want to allow my users to search by what criteria. Suppose I provided a dynamic search functionality that allows them to select any columns to search by, wouldn't that necessitate bringing over the whole table anyway?"
Not at all. IMO you either have to create a very generic search form from which users can build up criteria (but may not be as user friendly) or provide sufficient controls (to act as search criteria parameters) that you're able to cater for a wide variety of requests. Often you predict users searching requirements when an application is released - and feedback soon tells you if they need a different set.
There's no reason why you'd be fetching an entire table back.
(Unless you made some misjudgements in forming a local query to be parsed by ODBC.)
However ADO is a perfectly acceptable option - especially now you're coming at this from an angle of a limited request for specific data.
Once holding specifically requested data - requerying isn't the same chore - it's just executing the same precise query again.

By design or accident - I can't see how you ever want to be pulling over an entire table (other than to insert locally - for lookups etc).
 
To be honest - I'd viewed this as largely some intellectual exercise you were engaged in rather than some final user based functionality.
Exploring rather than decision making.

Don't misunderstand- while I do have a practical goal, I would hate to be that developer who tells the users, "Can't do it. Not how things work. It'll never work. You'll have to accept my solution because I'm too lazy to think it through and come up with a reasonable solution.", so this does have some theoretical roots (or should it be lack of? :confused: ;))

Generally - entire tables held in memory locally are a bad idea.
It goes a long towards entirely negating the point of a client server application. The server isn't doing the heavy lifting to make requests passed over the network efficient.

Quite agreed! I may have been fast and loose with my wording, but I definitely wasn't thinking in this terms "Let's pull everything over the wire! Burn that cable, baby!", but more like "Let's tell the users everything about this table, and let the user choose." Kind like how Jet handles linked table as a local database object, which AFAIK, are nothing more than a descriptors of what the table looks like and how to connect to that table. When using ADO, that extra information/functionality seems to be missing.

With linked sources in a bound form, I can give a loose query, and Jet only fetches few rows at a time. A good thing™. But when we go the way of recordset, be it ADO, DAO, Banana's secret sauce® (Available as Seen On TV for low, low price of $9.99), this "loose query" suddenly becomes very expensive. I guess I just can't wrap my head around that Access's been around for so long and nobody bothered to provide similar degree of functionality in other APIs.

Binding to the forms to encapsulate a transaction around subforms too etc is pretty much exactly what I was expecting your reasoning to be.
Bear in mind the alternative that is one continuous forms equivalent to unbound forms and that's a temporary local table filled with server data, edited locally and then written back to the server only if explicitly requested. It isn't even entirely necessary to have that local table for the continuous form.

I hope I'm not muddling the water, but the previous paragraph should have clarified my original intention- I want to be able to provide "loose query" without the cost. Yes, I can just be conservative by pulling only the current row, using search box, and a bunch of codes behind it, but I was afraid that somehow I'd be providing less functionality to what my users were accustomed with a bound form which could show them so many records available. Going unbound would make my user come and ask, "Banana, there were X records yesterday. When you made changes to the database, now there's only 1 records. Where everything went to?" and I would have to do some serious hand-waving and paper scribbling to explain to the users that no, it's basically the same thing, but implemented in different way. Not a good thing.

Regarding multi-form transaction- I suppose I could create a temporary table but there are some concerns:
1) Creating a temporary table would, IINM, entail writing/reading to hard drives. Not what I want, not necessarily because of performance, but rather because it just wastes the drives' life. The dataset is small, but I can see users moving through several rows, each having several child records requiring between 3 to 5 subforms. If it can be done totally in memory, e.g. with a recordset, I'm all for it, except I can't think of a good way to link a subform to a recordset.
2) I get a bit nervous because there's still that window for data corruption- suppose network went down in middle of editing the subform just after editing the parent form. Now I have a contaminated database and it's my job to dig through the data to find that dirty row and sanitize it. By using transactions, I can guarantee that the states between the server and the client are 100% consistent. Yes, I can write checks and all sorts, but that'd be just reinventing wheels, no? The way Jet sends "COMMIT;", "AUTOCOMMIT=0;","AUTOCOMMIT=1"; like there's no tomorrow makes me just teensy bit nervous.

I see now the analogy you were making was of Jet querying an ODBC source - hence the several requests you were seeing for data.
There is no analogy for that Access functionality in direct data access (i.e. via code recordsets).
And if you're sticking with ODBC for your data request - then yes, a client side cursor is likely your only choice. Is there a reason you're not using the OLEDB provider instead of ODBC?

The backend is a MySQL server, and I believe there are two OLE DB providers out there, but I've not tested them extensively as I'm not far along my ADO experiments quite yet to know if this is warranted.

No - it's not. It's simply not what they were designed for. Not what any recordset type was designed for. Requeries are expensive. It's just the name of the game. If you want updated data you have to pay for it.
To receive recent updates only would require a large amount of participation by the database engine - and ADO in particular can't assume that (being designed to work for a wide range of providers - not necessarily even RDBMS).

Interesting. I would have thought it a trivial solution: The provider would create a timestamp for each OpenStatic cursor request, then a method to retrieve new/updated rows is called, then we compare the time since, and send only the rows created/updated/deleted within that timeframe. Not that this can be done manually, but I was under the impression that APIs was supposed to make my life easier, not complicated. ;)

Not at all. IMO you either have to create a very generic search form from which users can build up criteria (but may not be as user friendly) or provide sufficient controls (to act as search criteria parameters) that you're able to cater for a wide variety of requests. Often you predict users searching requirements when an application is released - and feedback soon tells you if they need a different set.

At first, I was thinking of combobox showing the actual values within the table, which would just burn the cable all way to the hell, but I think I may have it wrong- are we talking about a combobox displaying the columns to search by, with a criteria to be entered by the user?

If so, then that's a slap on my forehead.

By design or accident - I can't see how you ever want to be pulling over an entire table (other than to insert locally - for lookups etc).

And to repeat, I do not. If I actually wanted to do this, I should be taken out the back and shot. ;) I was thinking in terms more like "exposing the table structure to the user", like Jet is able to do via several small things, like immediately loading first row, but not displaying the total recordcount until either it has loaded everything or the user has moved to the last record.
 
Last edited:
I'm afraid I'm going to have to quote quite a bit myself now.

"Let's tell the users everything about this table, and let the user choose." Kind like how Jet handles linked table as a local database object, which AFAIK, are nothing more than a descriptors of what the table looks like and how to connect to that table. When using ADO, that extra information/functionality seems to be missing.
It's true - with a linked ODBC table Access stores a local cache of the table's schema when you link it to save a fetch to the server to determine that. (It's why you need to refresh the link after altering the definition on the server - some consider that a pain - but the performance hit of constantly checking schema information would be worse!)
ADO doesn't offer that as such no - naturally not really as it's an in memory API technology, Access maintains a permanent Jet file presense to hold it's definitions).
However I don't see any reason for a wasteful request of data to ever be required to give users a free choice of the table? All you need is the definition. This could be achieved by either storing that definition somewhere in Access (even a linked table!) or in a local table's data - or establishing the schema via ADO in a once-per-session read of it.
Upon application startup - hit the server, read the schema and return it.
You could do this simply via ADO (using an *empty* recordset) - or in greater depth via ADOX. If you had linked tables still - I'd see little reason not to just look at the definition there.
Once you have the schema you can present options to the users and build efficient data requests upon that.

Access (via Jet) is indeed capable of making iterative (batch) requests for data to load a recordset.
DAO is too - and so is ADO really. It's just that the bringing together of the functionality (binding a recordset to a form) isn't letting you use the formal (asynchronous recordset) method of doing so.
(As VBA is single threaded anyway - there's effort involved in watching the loading of the asynchronous recordset.)
Just allowing ADO's default, on demand, recordset data loading should be pretty reasonable. To that end - I'd certainly recommend you try the OLEDB provider and a server side cursor as everything you describe that you want implies this as a requirement. (I'm not a MySQL user so I can't speak to how effective the providers are - and what functionality they expose to ADO but I know folks who do so it must be decent).

With linked sources in a bound form, I can give a loose query
It depends upon what you mean by a "loose" search.
Is it just that one you've not attempted to predict at all? (i.e. to provide a set of unbound controls, each representing a specific field in which to enter criteria). The alternative to that would indeed be one in which you list the fields available in a combo - and enter values in a free text box alongside that field selection.
I rarely implement such search forms myself - they're great for experienced users but for the non-tech savy they can seem daunting.
But they're pretty standard to set up.
The alternative of having combos populated with a full list of values for a given field would indeed be nightmarish performance-wise and one of the reasons I so hate the default FilterByForm functionality in Access.
(Some users who've seen that and expect the functionality like that in any search form feel under-provided for with a free text search but, quite frankly, they've been operating on a small-fry scale).
This would be analogous to your concerns of
>> "but I was afraid that somehow I'd be providing less functionality to what my users were accustomed with a bound form which could show them so many records available"
Users just have to accept - we're building them a scalable professional application that needs to support multiple users and not drag down their server or network. We do that by implementing efficient and appropriate solutions for them.

Creating a temporary table would, IINM, entail writing/reading to hard drives. Not what I want, not necessarily because of performance, but rather because it just wastes the drives' life
I have to say - that's a new, unique concern to me. I've never been shy about using the local disk - and I never will be. I'll make no apologies to a client for using their client PC hardware to offload server work when appropriate :-)
The local Jet tables we have at our disposal in Access are a massive benefit.

The dataset is small, but I can see users moving through several rows, each having several child records requiring between 3 to 5 subforms.
Now that's an interesting comment.
The issue you're having is that you have a hierarchy displayed? More than just parent child?
Or is the issue here that you're displaying many parent records to the user - leaving them free to navigate through the resultset at will?
Again - that comes down to selectivity then. Forcing the user to bring over what they will want. Not browse at leisure through what's there already.

If it can be done totally in memory, e.g. with a recordset, I'm all for it, except I can't think of a good way to link a subform to a recordset.
I don't follow you. You mean other than by direct binding of the recordset as we've been talking about?
There are ways of holding continuous data in a form recordset without having a table to bind to (using a constructed ADO recordset). I wrote an article on it for Access Advisor last year and the accompanying MDB can be found near the bottom of my examples page.
But you still need to fill such disconnected recordsets with something.
And that data needs to be fetched. You'd likely be as well to stick with a disconnected recordset loaded directly from the source.
I'm still not clear on the overall structure that you're intending on implementing - and so what would be of most benefit.
Obviously with form binding to recordsets - we don't have Access Master/Child link fields for subform convenience...
If you do have a few parent records and each has child records then you could maintain a larger recordset than is currently visible for the child records and filter it appropriately as the parent records are navigated.

like immediately loading first row, but not displaying the total recordcount until either it has loaded everything or the user has moved to the last record.
A form bound to a non clientside cursor recordset will generally display something approaching this type of behaviour - albeit if not as iterative as Access bound to Jet data. (Client-side curors pop that complete could right up at you - they've already loaded the data by the time the form's usable).

But it does come back to the point that we should be waiting for ADO to load a massive resultset. We don't want to be pepparing the server all the time - I'd take little and often over heavy and less frequent if possible.
 
Excellent points all around.

It depends upon what you mean by a "loose" search.
Is it just that one you've not attempted to predict at all? (i.e. to provide a set of unbound controls, each representing a specific field in which to enter criteria). The alternative to that would indeed be one in which you list the fields available in a combo - and enter values in a free text box alongside that field selection.

One thing I dislike about free textbox is that forces the end users to guess a bit, and it becomes my responsibility to ensure that all possibility the users may have imagined in entering the criteria is consistent with what is actually returned (e.g. case sensitivity, wildcard match, whether space character counts, and that kind of stuff).

Or is the issue here that you're displaying many parent records to the user - leaving them free to navigate through the resultset at will?
Again - that comes down to selectivity then. Forcing the user to bring over what they will want. Not browse at leisure through what's there already.

From what I see from my end users, they usually do something like this;

Select a date, then browse all records that they did for that day, or select a client, then browse all past appointments... It's actually the "browsing" part that makes me think that I somehow need a loose query. But now I think that if I just had a bunch queries that just provide all records for a particular date or for a particular client, then it'd be good enough. Date can be entered freehand, with a input mask. Client name would have to be with a combobox, to enable consistent input and autoexpand, though.

The alternative of having combos populated with a full list of values for a given field would indeed be nightmarish performance-wise and one of the reasons I so hate the default FilterByForm functionality in Access.
(Some users who've seen that and expect the functionality like that in any search form feel under-provided for with a free text search but, quite frankly, they've been operating on a small-fry scale).
This would be analogous to your concerns of
>> "but I was afraid that somehow I'd be providing less functionality to what my users were accustomed with a bound form which could show them so many records available"
Users just have to accept - we're building them a scalable professional application that needs to support multiple users and not drag down their server or network. We do that by implementing efficient and appropriate solutions for them.

I have to agree with you. What worked yesterday may not work tomorrow. Still, like it or not, end users are creature of habits, and the transition is usually easier when the functionality are still present, even if it's actually a entirely different beast underneath. Therefore, I'd be amiss if I didn't completely research this through before telling my users that they no longer can expect this and that because we've moved on to a different system.

I have to say - that's a new, unique concern to me. I've never been shy about using the local disk - and I never will be. I'll make no apologies to a client for using their client PC hardware to offload server work when appropriate :-)
The local Jet tables we have at our disposal in Access are a massive benefit.

The issue is that if it's going to be persistent, I'm all for writing it to the hard drive. Lookup tables are very good candidate in that regards. But when it's just a couple of rows so my users can browse through, it should be confined to the client PC's memory only. In MySQL, I can create a table using Memory storage engine, which not only nothing is persistent, but performance is very fast, and this is a good thing for the server's hard drive. AFAICT, the nearest equilvant in Access is recordset (though I wonder, but have yet to experiment, if I can do same thing by creating a new tabledef but not actually appending it to the TableDefs collection).

Now that's an interesting comment.
The issue you're having is that you have a hierarchy displayed? More than just parent child?

Yes, there's a hierarchy involved, and there's also the possibility of independent subforms, which both relate to the same record. All will need to be displayed in a detail subform to the end user.

I don't follow you. You mean other than by direct binding of the recordset as we've been talking about?
There are ways of holding continuous data in a form recordset without having a table to bind to (using a constructed ADO recordset). I wrote an article on it for Access Advisor last year and the accompanying MDB can be found near the bottom of my examples page.
But you still need to fill such disconnected recordsets with something.
And that data needs to be fetched. You'd likely be as well to stick with a disconnected recordset loaded directly from the source.
I'm still not clear on the overall structure that you're intending on implementing - and so what would be of most benefit.
Obviously with form binding to recordsets - we don't have Access Master/Child link fields for subform convenience...
If you do have a few parent records and each has child records then you could maintain a larger recordset than is currently visible for the child records and filter it appropriately as the parent records are navigated.

You got it right. As I said before, I want to have my cake and eat it, by having a bound form/subforms with full control over the transaction across the parent records and the related child records. Using recordsets take away from bound forms just a tiny little, and I have to do the linking manually. Not too bad in my book, though this has potential to be more buggy than if I just sticked to what Microsoft provided. At least this is supposed to be less work than a totally unbound form, because even with a recordset binding, I can still bind the controls to the recordsource with same SQL, then set the form to the recordset prior to loading it (actually, to be precise, I'd delete all recordsource prior to distributing it and use Load event to bind the recordset at runtime, to avoid hammering the server with two duplicate requests).

I think the problem can be summed up thus: I want to be a lazy but also an anal-retentive developer. ;)

But it does come back to the point that we should be waiting for ADO to load a massive resultset. We don't want to be pepparing the server all the time - I'd take little and often over heavy and less frequent if possible.

We are in total agreement- I had came to ADO with assumptions that it was capable of behaving in similar manners to what Jet does with a bound recordset, retrieving rows in a iterative manner and filling it in background. It seems that I may have to reconsider my assumptions- by enabling the server side cursor or something else.
 
Last edited:
The only reason I wanted more control over transaction is only because I wanted to have a transaction that would be able to span not just the form but all of its subforms
This has been discussed already. I would use a client side table to hold the transaction as I was building it. I would not use arrays and an unbound form. That is way too much coding for too little gain. And, as I told another poster today, you would need to hold a gun to my head to get me to use this method to manage updates but for "new" actions, I'm OK with it. Of course since I get paid by the hour, it truely doesn't matter how much money the client wants to spend, I just need him to be aware that bound forms will cost x and unbound forms or temp tables will cost xxxxxxxxxxxxxxxxxxxx.
or waste server's cycles by requerying for everything, which a carelessly written unbound form has a potential to do.
This has nothing to do with bound/unbound. It has everything to do with how the query is structured.
Quote a excellent point- I may have been digging a hole that I forgot to look around. There's just one thing though. I'm just not sure exactly yet how I want to allow my users to search by what criteria. Suppose I provided a dynamic search functionality that allows them to select any columns to search by, wouldn't that necessitate bringing over the whole table anyway?
I don't give the option of selecting which columns to query by. We decide together which columns he wants and I use combo boxes whereever practical. There are other methods for sectioning combos which involve overlaying the combo with a text box so you can capture the first letter and change the RowSource of the combo based on the first letter or first two letters or whatever will work to minimize the actual number of rows in the combo.
No - it's not. It's simply not what they were designed for. Not what any recordset type was designed for. Requeries are expensive. It's just the name of the game. If you want updated data you have to pay for it.
This is just another reason to severly limit the number of rows returned from the server with each query.
With linked sources in a bound form, I can give a loose query, and Jet only fetches few rows at a time. A good thing™. But when we go the way of recordset, be it ADO, DAO, Banana's secret sauce® (Available as Seen On TV for low, low price of $9.99), this "loose query" suddenly becomes very expensive. I guess I just can't wrap my head around that Access's been around for so long and nobody bothered to provide similar degree of functionality in other APIs.
Jet doesn't stop fetching. It just continues fetching in the background until every requested row is retrieved. The good thing Jet does is to populate the form ASAP without waiting for the entire query.
And to repeat, I do not. If I actually wanted to do this, I should be taken out the back and shot. I was thinking in terms more like "exposing the table structure to the user", like Jet is able to do via several small things, like immediately loading first row, but not displaying the total recordcount until either it has loaded everything or the user has moved to the last record.
I would never try to reproduce the QBE. When I have users who need that much flexibility, I give them their own front end along with training. Since they are using their own IDs to access the server, if they bring it to its knees, the DBA will go after them not me:)
 
This has been discussed already. I would use a client side table to hold the transaction as I was building it.

I thought I had missed or forgotten about that but when I went back to that old thread, it seems that it must have been another thread but I couldn't find it.

Client-side tables sounds reasonable; how would you manage the uploading? Do you embed it in a transaction as a bulk update, or something like that? What about resolving conflicts (e.g. two users edited the records at same time, and one already has uploaded the edits, but other doesn't know it)? I'd imagine you use something like timestamps?

This has nothing to do with bound/unbound. It has everything to do with how the query is structured.

I was being loose with my wording, you are correct that it's all about how we form the query, and whether this uses Jet, ADO or whatever- In that case, unbound = ADO, and bound = Jet.

I don't give the option of selecting which columns to query by. We decide together which columns he wants and I use combo boxes whereever practical. There are other methods for sectioning combos which involve overlaying the combo with a text box so you can capture the first letter and change the RowSource of the combo based on the first letter or first two letters or whatever will work to minimize the actual number of rows in the combo.

Thanks for that info.

I would never try to reproduce the QBE. When I have users who need that much flexibility, I give them their own front end along with training. Since they are using their own IDs to access the server, if they bring it to its knees, the DBA will go after them not me:)

Makes sense.
 
Client-side tables sounds reasonable; how would you manage the uploading? Do you embed it in a transaction as a bulk update, or something like that? What about resolving conflicts (e.g. two users edited the records at same time, and one already has uploaded the edits, but other doesn't know it)? I'd imagine you use something like timestamps?
That's why you would need to hold a gun to my head to get me to do this:) Doing disconnected updates requires significant coding and error handling. It is really the error handling that becomes the issue, not that it is so hard to do. But, you are disconnected from the user and so have to save the bad data so you can create an appropriate error message. Plus you must use transactions so you can roll back the update when an error is encountered. Then, once the user fixes the first error, you start all over again with the validation because something might have changed. Meanwhile, what locks have you set on the server side tables? If the records are not locked, they can be changed as you are working on the local temp table. In an environment where you would be very unlikely to run into conflicts, this could be made to work but never in an environment where multiple people can be working on the same set of data at the same time.
 
Unfortunately, I would really need that functionality since my end users do work jointly on some clients case, but do their own reporting for their portions of the work. It's beyond me as to why multi-table transaction can't be done with bound form/subform.

We both agree that it's always best to use built-in functionality wherever possible, but this seems to be one of case where both (e.g. bound form/subforms and transactions) can't be had at same time.

Regarding locks, I'm going to start out with optimistic locking and see how well it works out and if necessary, switch to pessimistic locking.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom