A Function that returns a Recordset (1 Viewer)

[RonPaii]

Yes... you know you have hit the limit when you see the error message "Heap overflows Stack" (or its most common partner, "Stack overflows Heap"). Which of those you get depends on what specific action hits the limit first. There is also the family of error messages "Out of memory", "Out of virtual memory", "System resources exceeded" and a few more of that ilk. You cannot easily determine how much memory you have from inside Access without using some API calls. However, anything involving recursive subroutine calls will surely increase your risk of consuming memory quickly. And, unfortunately, a tree diagram potentially includes recursion. Don't get me wrong, recursion is a useful tool. It just requires you to be careful.

I learned this the hard way the first time my genealogy database ran into a low-level case of incest, in which two people got married who were cousins - 4th cousins once-removed. My original recursion handler didn't like that very much, because (I admit it) I was careless and didn't expect a recursion loop. But I got one. And got a lot of the aforementioned error messages.

What he said.
Anytime you use recursion, add a level counter or something to detect infinite loops. Look at your data for errors in child/parent links.

 

[Pat Hartman]

It seems madness to go creating an individual query for each individual field I want returned when I already have all of these in a query which has been built already.

I don't want to burst your bubble but that is EXACTLY what you are doing. The only difference is that you are not saving the querydef. That means that each time you execute the wrapper, Access will create a new query and then create an execution plan. I think Access might even save the temporary query. The Access programmer's guide might help with understanding that.

The dLookup() is what I suggested back in #14. Doc put it in a wrapper for you. If that makes you feel like it is more "efficient", you are only fooling yourself. There is nothing more efficient about domain functions, although there is a substitute that you might want to use. Sorry, I don't have a link but someone will help me out here. I can't look for it now but I'll check back later. The Wrapper substitutes one line of code for another line of code that executes more lines of code. Hmmm. At least passing all three arguments means that someone reading the code does not necessarily need to go to look at the function you are calling to understand what it is doing and where the data is coming from.

Still can't figure out why you are replicating code which works very well for your own version when that is not even your final objective and you have time constraints. Learning is good and I don't want to discourage that but there is a time and a place for everything but at this time, your attempt to recreate MajP's project is a diversion.

 

[RonPaii]

IMO that is unusable. Because it does not show which nodes are expandable there is no plus signs. You have to guess if you can expand it or not. Something this simple it may suffice but in a tree of any size with uneven branch depth it would not. The user is not going to want to click on everything and guess if it will expand.

I have looked at your sample DB, VERY IMPRESIVE. The 2 samples I supplied were to more simply show both recursive and non-recursive methods that are easer to follow.

The Northwind sample was based on my production contact form which I developed before your sample. Most users click the search button which does a show as you type, taking 2 or 3 characters to locate a contact. It then expands the tree to the selection. The tree is mostly for visualizing relationships.

Our contact form allows persons to be related to multiple company's or divisions. For example a project manager may work with more then 1 division and will show in the person list when the tree is on one of those divisions. If in the person branch, multiple divisions will be listed for that person.

 

[dalski]

I don't want to burst your bubble but that is EXACTLY what you are doing. The only difference is that you are not saving the querydef. That means that each time you execute the wrapper, Access will create a new query and then create an execution plan. I think Access might even save the temporary query. The Access programmer's guide might help with understanding that.

Ah I see, thank you for persevering.

 

[Pat Hartman]

I really didn't want to burst your bubble but you needed to know how the various methods actually compare since you chose your original method based on efficiency and you are ready to jump ship to a dLookup() inside a wrapper. Your original choice was the most efficient execution wise. But sometimes we want something less cumbersome for development which seemed to be what you were asking for. Once the code/queries are built you're done and your execution is as efficient as you can get it but maybe the tiny fractions of a second each querydef saves, makes the app more difficult to maintain and so the conciseness of the domain function becomes attractive. If you actually need to do validation, then the wrapper would be less code to write (although not less to execute) but in reality, you still may need to check the return value from the function if it does validation so now we're back to redundant coding. Is the wrapper saving anything? You add validation inside the wrapper but you then need to add code to the call to determine if the wrapper found an error and then you have to handle it because the handling part of the validation may not be uniform and so can't be incorporated inside the wrapper. Think this all through before you go with the wrapper.

Unless you are performing the domain functions inside the wrapper thousands of times within a short period, you are very unlikely to ever experience at a human level,, slowness from the inefficient domain function.

If I were coding this, I would probably have gone directly to domain functions and if I wasn't doing validation which I would never do if I expect the data from the table to have been validated prior to its being saved, it would never occur to me to use a wrapper. You should only abstract your code (ie use a wrapper) if the wrapper is going to reduce your coding because everything can be done within the wrapper.

My need for efficiency diminished greatly once I switched to Access in the 90's. The reason is simple. When I was creating CICS transactions written in COBOL, they ran on the mainframe and were competing for resources with thousands of other transactions and batch jobs running at the same time. Therefore, every fraction of a second mattered. Not only for my transaction but for my footprint and how much of the resource pool I was using. If my transaction took .9 seconds when it could have taken .7, that slowed everything down if everyone else was also being inefficient. Access is pretty much a solo development environment but the mainframe was frequently a group effort since every program was torn apart by group code walk throughs to ensure that it was as efficient as it possibly could be. So, at least in the large insurance companies where I learned my trade, efficiency was critical.

With Access, the need for efficiency is still important but not paramount since my code is executing on a stand alone computer and not competing with other jobs for resources (normally). So the focus is more on making code loops or queries as efficient as possible. In the mainframe, data retrieval/update needed to be as efficient as possible because that is also a shared resource but with Access, it is the only shared resource so that is where you need to concentrate your effort so you were right to want to make data retrieval as efficient as possible. Here is the trade off. Let your development be less efficient to improve your production speed or let your development be more efficient at the expense of losing at least some production speed.