A Function that returns a Recordset

[RonPaii]

Unless you're going to be using each of those functions multiple times, seems like overkill.

Simpler and faster then a bunch of dLookups.

Option Compare Database
Option Explicit

' Sample Class to search recordset and return values

Private Type MyTableType
    qd As DAO.QueryDef
    rs As DAO.Recordset
End Type
Private This As MyTableType

Private Sub Class_Initialize()
    ' Do nothing
End Sub

Private Sub Class_Terminate()
    With This
        If Not .rs Is Nothing Then
            .rs.Close
            Set .rs = Nothing
        End If
        If Not .qd Is Nothing Then
            Set .qd = Nothing
        End If
    End With
End Sub

Public Property Let LookupID(ByVal PrimayKey As Long)
    With This
        If Not .rs Is Nothing Then
            .rs.Close
            Set .rs = Nothing
        ElseIf .qd Is Nothing Then
            Set .qd = CurrentDb.QueryDefs("MyTableQuery")
        End If
        .qd.Parameters("ID") = PrimayKey
        Set .rs = .qd.OpenRecordset()
        If .rs.RecordCount = 0 Then
            .rs.Close
            Set .rs = Nothing
        Else
            .rs.MoveFirst
        End If
    End With
End Property

Public Property Get Description() As String
    If Not This.rs Is Nothing Then
        Description = This.rs.Fields("Description")
    End If
End Property

Public Property Get Location() As String
    If Not This.rs Is Nothing Then
        Location = This.rs.Fields("Location")
    End If
End Property

Public Property Get SomeOtherField() As String
    If Not This.rs Is Nothing Then
        SomeOtherField = This.rs.Fields("SomeOtherField")
    End If
End Property

 

[RonPaii]

That is your opinion but you didn't offer any justification regarding what makes the wrapper simpler or what makes it faster. As I said, if you are using the dlookups multiple times from different places, having a "wrapper" protects you from changes should you decide you need to make a change that must be replicated every place the domain function is used. This is the only justification I can see for adding the overhead of "wrappers". The wrapper functions are not more efficient in any way since they still each run a dLookup() which is itself, inefficient. They also add lines of code which must be executed. What I object to with the wrappers, is that individually they mask what is actually happening so if you need to know that the wrapper is using a query rather than a table for example, you have to step out of line to look at the wrapper function. Reusable code is worth writing but separating out code that is not actually reused simply makes debugging harder and obfuscates the code's logic path The other downside of a "wrapper" to replace a single line of code is that some developer in the future could easily change the function not realizing that the change will break other calls to the function.

I'm not saying that you shouldn't create "wrappers", only that you have a reason and that reason should include multiple uses from different sections of code.

I agree this would be over kill for one off lookup of a single column. The OP indicated repeated lookups for multiple columns and the possibility of keeping a recordset open to do it. Assuming this is part of a single function call, initiating the class once and using it repeatedly for each column would be faster with the added benefit of the class handling nulls and other data issues. Also, passing a class instance is as fast as a recordset reference.

 

[The_Doc_Man]

In my amateur perception it just seems so inefficient to create a query for each individual field one wants returned & that's what led me to this thread. It's looking up an ID & returning one of several fields I need dependant on where it is executed from.

So... create a wrapper function that passes in three parameters - one is the named query, one is the value you want to use for the effective LOOKUP equivalent operation, the third is the text name of the specific field you want to return,

This next is AIR code to suggest a general format, it might require a little bit of playing. Things in braces {} are things you would have to name.

Public Function SpclQVal( ByVal SpcQ as String, ByVal SpcRID as {whatever data type the record ID uses}, ByVal SpcFld as String ) As Variant

Dim prm as DAO.Parameter
Dim prs as DAO.Recordset
Dim cdb as DAO.Database
Dim pqd as DAO.QueryDef
   Set cdb = CurrentDB
   Set pdb = AllQueries(SpcQ)
   pdb.Parameters("{whatever name is used for the record's ID field}") = SpcRID
   prs = cdb.OpenRecordset( SpcQ, dbOpenSnapshot, dbReadOnly )
   SpclQVal = prs.Fields(SpcFld).Value
   prs.Close
   Set prs = Nothing
End Function

That has NO error checking and is VERY rough - but it shows a "wrapper" function that could retrieve any one (and ONLY ONE) field from the record picked up based on the value of SpcRID.[/CODE]

 

[dalski]

Thanks Ron, that addresses my requirements more so - in obtaining different fields from the same queryDef. But I am not keeping the recordset open. I am not leaving the recordset open so that is not an issue.

Thanks DocMan - that addresses my requirements more so & similar to what I've been up to but was lacking the return field parameter, thank you. Pretty much what I've been doing.

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.

You are using querydefs because of their efficiency. Don't use querydefs. Do it the way some of the others do by embedding your SQL and execute the hard coded strings. Because you can't have it both ways. The POINT of using a querydef is so that the created execution plan can be saved to make the query more efficient for the second and subsequent uses. But if you change the structure of the query, then the execution plan needs to be rebuilt. The two requirements are incompatible and that is that.

Pat - This is the entire reason that I am using a queryDef, it is used multiple times. I do not believe changing the parameter changes the 'structure of the query' & the benefit of pre-compilation still occurs when entering a different parameter with the queryDef. Granted I'm going through a wrapper function which will add overhead, but I'd sooner a tiny more overhead than storing 1 query opposed to 10.

 

[RonPaii]

So... create a wrapper function that passes in three parameters - one is the named query, one is the value you want to use for the effective LOOKUP equivalent operation, the third is the text name of the specific field you want to return,

This next is AIR code to suggest a general format, it might require a little bit of playing. Things in braces {} are things you would have to name.

Public Function SpclQVal( ByVal SpcQ as String, ByVal SpcRID as {whatever data type the record ID uses}, ByVal SpcFld as String ) As Variant

Dim prm as DAO.Parameter
Dim prs as DAO.Recordset
Dim cdb as DAO.Database
Dim pqd as DAO.QueryDef
   Set cdb = CurrentDB
   Set pdb = AllQueries(SpcQ)
   pdb.Parameters("{whatever name is used for the record's ID field}") = SpcRID
   prs = cdb.OpenRecordset( SpcQ, dbOpenSnapshot, dbReadOnly )
   SpclQVal = prs.Fields(SpcFld).Value
   prs.Close
   Set prs = Nothing
End Function

That has NO error checking and is VERY rough - but it shows a "wrapper" function that could retrieve any one (and ONLY ONE) field from the record picked up based on the value of SpcRID.[/CODE]

For a generic lookup the class will still work, you will need to provide more information.

With AnyTableClass
.QueryToOpen = "Your Named Query"
.ParmName = "Your ID lookup Parameter"
.LookupID = YourKeyValue
Description = nz(.ColumnFromRS(1))
Location = nz(.ColumnFromRS(2))
End With

Option Compare Database
Option Explicit

' Sample Class to search recordset and return values

Private Type MyTableType
    qd As DAO.QueryDef
    rs As DAO.Recordset
    QueryName as String
    ParmName as String
    FieldCount as Long
    IdWasFound As Boolean
End Type
Private This As MyTableType

Private Sub Class_Initialize()
    ' Do nothing
End Sub

Private Sub Class_Terminate()
    With This
        If Not .rs Is Nothing Then
            .rs.Close
            Set .rs = Nothing
        End If
        If Not .qd Is Nothing Then
            Set .qd = Nothing
        End If
    End With
End Sub

Public Property Let QueryToOpen(ByVal QueryName as string)
    This.QueryName = QueryName
end Property

Public Property Let ParmName(ByVal SearchParm as string)
    This.ParmName = SearchParm
end Property

Public Property Let LookupID(ByVal PrimayKey As Long)
    With This
        If Not .rs Is Nothing Then
            .rs.Close
            Set .rs = Nothing
        ElseIf .qd Is Nothing Then
            Set .qd = CurrentDb.QueryDefs(.QueryName)
        End If
        .qd.Parameters(.ParmName) = PrimayKey
        Set .rs = .qd.OpenRecordset()
        If .rs.RecordCount = 0 Then
            .IdWasFound = False
            . FieldCount = 0
            .rs.Close
            Set .rs = Nothing
        Else
            .FieldCount = rs.Field.Count ' ? I have not looked this up
            .IdWasFound = True
            .rs.MoveFirst
        End If
    End With
End Property

Public Property Get IdWasFound() as Boolean
    IdWasFound = This.IdWasFound
End Property

Public Property Get ColumnFromRS(ByVal ColID as long) As Variant
    If Not This.rs Is Nothing Then
        Description = This.rs.Fields(ColID)
    End If
End Property

 

[MajP]

Unless you're going to be using each of those functions multiple times, seems like overkill

If I am using a domain aggregate function I am probably using it more than once. Even if I am uncertain of its reuse I tend to go ahead and put a wrapper around all domain aggregate functions. I find it to be the opposite. You end up wasting time if you do not wrap it.

1. If I think I will only use it once, it always turns out I need it again so it is just easier to wrap it to begin with. Double the work to then make a central function
2. Rarely is this a "one liner". At a minimum there is some validation of the inputs. The wrapper allows for more complex validation of inputs and outputs
3. Far easier to write, read, and debug. If you write a stand alone wrapper you can test and debug easily from the immediate window
4. Makes your code far easier to read

CurrentTrips = GetNumberOfTripsOnPath(pathID)

vs

If not isnull(PathID) then
  currentTrips DCount("*", "tblPathTrips", "pathid_FK = " & pathID
end if

5. If any chance you are using it in a query or calculated control you are totally wasting your time not wrapping into a function. Painful to write and more painful to debug in an expression.
6. Far more understandable when you chain the functions

Public Function GetNumberAvailablePiggybacks(pathID As Variant, EdgeID As Variant) As Long
  Dim CurrentDriver As Long
  If Not IsNull(pathID) And Not IsNull(EdgeID) Then
    CurrentDriver = GetDriverFromPath(pathID)
    If CurrentDriver <> 0 Then GetNumberAvailablePiggybacks = DCount("*", "qrySelectPiggyback", "edgeID_FK = " & EdgeID & " AND driverID <> " & CurrentDriver)
  End If
End Function

There is a wrapper function to get the current driver, and then then the current driver cannot be included in the count of available drivers

 

[MarkK]

It's hard to deal with hierarchies in recordsets, but it's way easier if you use classes. Imagine a class that stores its Field.Values in a Scripting.Dictionary keyed by Field.Name...

Private fields_   As New Scripting.Dictionary
Private children_ As New Scripting.Dictionary

Function Create(Fields As dao.Fields) As cMyDataItem
'   This object stores data from a single row in a table.

    Dim fld As dao.Field
    
    For Each fld In Fields
        fields_(fld.Name) = fld.Value
    Next
    Set Create = Me
End Function

Property Get Key As String
    Key = Typename(me) & " " & fields_("TableID")
End Property

Property Get Text As String
    Text = fields_("SomeDictionaryField")
End Property

Property Get Icon As String
    Icon = "DataItemIcon"
End Property

Property Get Children() As Scripting.Dictionary
    Set Children = children_
End Property

Then, if you have a function...

Function GetMyDataItems() As Scripting.Dictionary
    Const SQL_SELECT = "SELECT * FROM tMyDataItems"
    
    Dim item As cMyDataItem
    
    Set GetMyDataItems = New Scripting.Dictionary
    With CurrentDb.OpenRecordset(SQL_SELECT)
        Do While Not .EOF
            Set item = New cMyDataItem
            GetMyDataItems.Add !TableID.Value, item.Create(.Fields)
            .MoveNext
        Loop
        .Close
    End With
End Function

... you return a Dictionary keyed by ID corresponding to all the data in the table, filled with class instances each corresponding to a row in that table with all the field values keyed by name. This is very fast. Moving the data across the network is slower than constructing this dictionary.

Then do the same with another table, say all the child rows for each cMyDataItem, which might be cMyDataChild objects, but this time when you enumerate the table, you assign each instance to the Children dictionary of the parent it belongs to, like....

Sub AssignMyDataChildren(MyDataItems As Scripting.Dictionary)
    Const SQL_SELECT = "SELECT * FROM tMyDataChildren"
    
    Dim item As cMyDataChild
    
    With CurrentDb.OpenRecordset(SQL_SELECT)
        Do While Not .EOF
            ' create child with all data from the row
            Set item = New cMyDataChild
            item.Create .Fields
            ' assign child to the Children Dictionary of the correct parent
            MyDataItems(item.ParentID).Children.Add item.ChildID, item
            .MoveNext
        Loop
        .Close
    End With
End Sub

Now you have a totally complete, fully resolved, in-memory hierarchical data structure. This is waaaaaay faster than opening a distinct recordset for each parent to find its particular children. Also, if you maintain a Dictionary of all the Child objects (rather than just distribute them to their parents, as this code does) then loading and distributing the children of the children is equally fast and simple.

And, presenting this data in a tree is much simpler, because the data structure itself is already hierarchical. Similarly each of the data classes that compose the hierarchy, since they are free of their recordset, can expose whatever fields they contain by any name, so if all the data class nodes in your hierarchy expose fields Key, Text, Children and Icon, building a tree can be accomplished with a single recursive loop.

 

[dalski]

Thanks RonPaii, another nice technique there.

Thanks MajP, yes it makes far more sense to wrap in a function in my mind & I find it comforting that I discovered the multiple benefits on my own.

Thanks MarkK.

Imagine a class that stores its Field.Values in a Scripting.Dictionary keyed by Field.Name...

This is such an elegant solution to hierarchical data in Access it beggars belief. I'm inexperienced with procedures which replicate constructors atm & find them a little scary atm. Was a tough read up until this part; to which I found myself actually cheering out loud

Then do the same with another table, say all the child rows for each cMyDataItem, which might be cMyDataChild objects, but this time when you enumerate the table, you assign each instance to the Children dictionary of the parent it belongs to, like....

• If duplicating table contents in memory, then surely there is a limit to the number of items/ class instances/ dictionaries which can be stored in memory?

Sadly I'm too far down the line to use this on this project I think, but I absolutely when complete will be looking to redesign the project & implement this strategy.

 

[RonPaii]

Thanks RonPaii, another nice technique there.

Thanks MajP, yes it makes far more sense to wrap in a function in my mind & I find it comforting that I discovered the multiple benefits on my own.

Thanks MarkK.

This is such an elegant solution to hierarchical data in Access it beggars belief. I'm inexperienced with procedures which replicate constructors atm & find them a little scary atm. Was a tough read up until this part; to which I found myself actually cheering out loud

• If duplicating table contents in memory, then surely there is a limit to the number of items/ class instances/ dictionaries which can be stored in memory?

Sadly I'm too far down the line to use this on this project I think, but I absolutely when complete will be looking to redesign the project & implement this strategy.

I previously posted sample databases using tree controls.

Multi-value field (MVF) using tree control - fills the tree using recursion, it's easy to follow in the debugger because of the limited data. See TreeFillAvailable function in Form_MVF_Control_Tree form.

This 2nd attached ACCDB works with Northwind to supply a contact form using tree control. I does not use recursion, instead it expands branches on demand by calling functions for each type of contact. You will need the Northwind db in the same folder. This one may be more like what you are looking for.

 

[MajP]

This 2nd attached ACCDB works with Northwind to supply a contact form using tree control. I does not use recursion, instead it expands branches on demand by calling functions for each type of contact. You will need the Northwind db in the same folder. This one may be more like what you are looking for.

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 order to get around that for every record that has at least one child you have to add that node as well. This then brings the + sign visible. I discuss that in detail here. This is the method I use for loading extremely large tress (10s of thousands of nodes), which I refer to as light loading vs full loading which loads all nodes.

Solved - MS Forms Tree View Slow Loading Issue

Hi, I want to use MS Forms tree view for one of my folder and used span folder option to populate the tblFileFolder using demo shared by @MajP at Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assis. No of records in the table are 3000. I am then using TreeViewRecordLoader...

www.access-programmers.co.uk

In contrast see the image below. Expandable nodes show the plus sign and it is clear what can expand and what can not. However this three has 10k nodes and at this time only 33 nodes are actually loaded. 1 per visible node and an additional for each plus sign.
So charlotte has no child nodes but the rest of the nodes do.
When you expand you then flush out the expanded level and add 1 child node for each expanded node that has at least one child.

In that thread you can also view @Edgar_ example on loading a tree with a two pass technique that does not require recursion. However, this does load all the nodes and not dynamically.

I gave up writing code to load a tree a long time ago. I load all my trees with a single line of code using the class module here. It only requires a properly formatted query.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to...

www.access-programmers.co.uk

This thread and the class demo/support some advanced concepts
1. Single line of code to load trees
2. drag and drop
3.Auto numbering
4. icons and dynamic icons
5. Light and full loads

Most importantly it demos using the properties of the tree control to support associations to the table kind of a pseudo binding. Shows you how to add, edit, delete nodes or records and synch the two up.

 

[The_Doc_Man]

If duplicating table contents in memory, then surely there is a limit to the number of items/ class instances/ dictionaries which can be stored in memory?

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.

 

[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.

 

[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.

 

[MarkK]

If duplicating table contents in memory, then surely there is a limit to the number of items/ class instances/ dictionaries which can be stored in memory?

I don't know much about how memory works, but my machine has 12GB of RAM. Task Manager says I've occupied 61%, so I've got 7.3GB of wiggle-room, and the max size for an Access file is 2GB. I think it'll be fine.

 

[The_Doc_Man]

The basic rule is, the more libraries you have (as Tools >> References), the less general memory you can use. The good news is that most of the common libraries you would normally use aren't terribly big. The better news is that if you have a split DB, the libraries are in the FE file. The 2 Gb limit applies individually to the FE & BE files, so you usually only worry about data blowouts. The bad news is that embedded graphics & images will quickly eat your socks if you aren't careful. The memory that counts up quickly will be what you use for really big strings or very large, dynamic numeric arrays. Dynamic matrices are worse.

 

[dalski]

Thanks Pat, I honestly do not at all care about my bubble or ego, so I am grateful for input received. Far easier to pretend I'm right or ignore a post so thanks for explaining (& to everyone).

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.

Correct I switched priorities (bad-form) apologies.

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.

My recordsets are quite large so this is happening; around 10k records being worked.

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.

I probably should be a little less fussy & try to boost production at this stage.

 

[MarkK]

I probably should be a little less fussy & try to boost production at this stage.

I find this a hard problem. Like, I've committed to a pattern, but it's not quite right, so making it work is painful and laborious. So do I stop, back up and get the pattern right, or push on and get the job done? Getting the job done will be way easier if I get the underlying pattern right, but getting the pattern right might make the job take way too long.
F&^%king software.