AL: Recursive Functions with Crystal Long

Description:
What are Recursive Functions and why would you use one? How do you design and use a recursive function? Join Crystal Long as she explores two recursive functions. Also discussions about vbNullString, Collections, and Memory.

Download presentation + ACCDB databases for ListFile and RelPath
https://msaccessgurus.com/presentation/Recursive_Functions.htm

List files recursively by Allen Browne
http://allenbrowne.com/ser-59.html

0:00 introduction by Maria Barnes for Recursive Functions
1:11 crystal
1:37 first database is Allen Browne's ListFiles application where the recursive part calls itself for subfolders to list path and filename to a collection
2:37 using the demo form to call Allen's ListFiles
with save files to a table
5:38 query to show number of files for each batch
filter the FileList table
6:13 add more fields to the FileList table
7:08 use to get information from Access databases
List Objects, Loop files – names of Access objects
https://msaccessgurus.com/tool/ListObjects_LoopFiles.htm
7:50 Concepts for Allen's code
7:57 Dir function
9:05 vbNullString
11:01 Collection object
11:37 TrailingSlash
Type Declaration Character
12:31 Allen's VBA code
12:34 Call ListFiles
13:22 ListFiles
15:35 FillDir

18:54 chat about vbNullString
and helpful links by Mike Wolfe
The vbNullString Constant in VBA
https://nolongerset.com/vbnullstring/
How to Efficiently Check for Empty Strings in VBA
https://nolongerset.com/check-for-empty-strings-in-vba/
An Article About Nothing
https://nolongerset.com/an-article-about-nothing/

19:21 Save Relationship Paths - another recursive function
21:00 import table and module to use
21:39 DEMO
23:27 SaveCollectionToTable
25:10 additional error handling in FillDir
27:29 What is ASCII for each character?
https://msaccessgurus.com/VBA/WhatisAscii.htm
27:55 file names
28:26 FSO File System Object
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/filesystemobject-object
30:08 RelPath example
Relationship Paths
stop VBA each time recursive function is called
31:13 information from MSysRelationships
31:48 table of relationship paths
33:04 Links
34:07 Maria - *next Lunchtime meeting*
Interfacing with an Outlook Calendar
35:24 danger of recursive functions
36:40 end point to avoid infinite loop, added self-join note and stop loop
37:10 vulnerable to available memory
37:51 open discussion
38:13 Maria- recursion is a powerful technique
39:04 two different collections
40:19 Adrian - ByRef and ByVal
43:32 Maria - does anyone have a good example of where they've used recursion?
43:39 Duane - list of all my image files on a bunch of different hard drives to get rid of duplicates and find them
45:04 really like the way Crystal has taken it further to dig into Access files to find a table or function when you can't remember which file it was in
45:50 escape route
infinite loop
46:24 Neil - muscle memory, stack space and consequences of running recursion
Every time called, gobbles up all you've dimmed so Dim as little as possible.
Keep it lean.
47:35 my question: what is the limit? If we run out of memory space with a collection, does it cache to a disk?
48:07 Adrian - memory, stack, heap
48:32 Maria - once you run out of memory, it's not very graceful. Would love to see a presentation on how to monitor memory to avoid out-of-memory errors, and keep track of when I'm getting close.
50:10 Neil - system resources
52:34 Peter - navigation control
53:09 Neil - that messes up Screen.ActiveForm because its always the navigation form because they didn't put in support on where you've navigated to
53:30 wrap-up

Next Meeting: Interfacing with the Outlook calendar by Maria Barnes
Tuesday March 31, 12 noon central
https://accessusergroups.org/calendar/lunchtime-2-2026-03-31/
join us!

related videos

AL: Using Dictionaries and Collections to work with JSON in Access by Maria Barnes, Neil Sargent, Leo theDBguy (1:03:47)
https://youtu.be/c7PwDuXPLeA

AL: 2 free tools: 1. List Access Objects 2, VBA Code Documenter, and step-thru OpenBypass -crystal, Adrian, Peter (1:03:31)
https://youtu.be/ePMIb3LuL6s

Access Lunchtime Playlist on YouTube: https://www.youtube.com/playlist?list=PLwIULo1M2EWvapx3zAhd3jhX_ITigxM6M