Introduction

Welcome! Recordsets, Loops & Tree View

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Access Developer Level 20. In this course we will cover working with recordsets to connect to external databases, including setting up a secure connection with an encrypted password. We will discuss adding records using different methods, exploring custom numbering logic with ASCII codes and nested For loops, and how recordsets can streamline those operations. You will also learn how to use the tree view control to display hierarchical data, such as employees and supervisors, and how to manage nodes, prevent selecting yourself as your own supervisor, and open related forms. Prior knowledge of recordsets and VBA is recommended.

Navigation

Prev: D20
Next: Lesson 01

Keywords

Access Developer, recordsets, external databases, OpenDatabase, connect string, password protected database, tree view control, hierarchical data, nested loop recordsets, self join, employee supervisor, add records, ASCII codes, Chr function, VBA

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Access Developer Level 20 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class is going to cover recordsets to external databases, multiple nested loop recordsets, and working with the tree view control.

In Lesson 1, you will learn how to use the OpenDatabase command so we can connect a recordset to an external database. This is handy because not only can you use it to connect to other databases on your network - maybe the accounting department has their own database or the sales department - but you can set up a password-protected database that could even be in a shared folder somewhere that lots of people have access to. You can put a password on it; then you can open this database with your encrypted password that is stored in your VB so that users cannot get hold of your encrypted data. I will show you how to send the password in the connect string, and that is a good way to secure your data in the back end without using a database server. Yes, a database server is better, like SQL Server, but this is pretty good.

Lessons 2 through 4 have to do with adding records to your database in a couple of different methods. Lesson 2 is simply adding follow-ups in intervals of 5, 10, 30, and 90 days. Lesson 3 is actually a request that came from one of my students. He has custom boxes with customized numbering, and he wants to be able to add those automatically. We will do some custom work with his box naming conventions. I will teach you about ASCII codes and the Chr function, and we will work with some nested For loops.

Then Lesson 4 is doing the same thing we did in Lesson 3, but we are going to use recordsets, which makes it much, much faster.

Lessons 5 and 6 have to do with the tree view control. I will show you how to set up the tree view control in a form so we can display hierarchical data. This would be, for example, employees with their supervisors - a self-join in the table - and we will learn how to load up the nodes in a tree view using a recordset.

We will do a lot more with that tree view in Lesson 6. We will expand and collapse all the nodes. We will open up the employee form as a subform when you click on one of the nodes, one of the employees. We will set up the combo box there for the supervisor, so you cannot be your own supervisor. That is a common question I always get asked: How can I set it up so I cannot pick myself as my own supervisor? There is a lot you can do with this tree view control.

I am using Microsoft 365. I have a subscription. If you are using Access 2019, that is the closest to the retail version. You should be okay going back all the way to about 2007, though. This version should work with all of those.

Prerequisites: Of course, this is a developer-level class. You should know VBA from my Developer Level 1 class. Of course, I strongly suggest you take the Beginner and Expert series first. Developer 13 is definitely recommended, and of course, 16 and on for the introduction to recordsets. If you have not taken Developer 16, 17, 18, and 19, you may be lost in today's class.

Even if you just picked this up for the tree view lessons, you have to know how to work with recordsets in order to load up that tree view.

Now it is time to sit back, relax, and enjoy Access Developer Level 20.

Thank you.

Quiz

Q1. What is the main purpose of using the OpenDatabase command in Lesson 1?
A. To connect a recordset to an external database
B. To export tables to Excel
C. To create forms automatically
D. To generate reports

Q2. What is one benefit of connecting to a password-protected external database using the connect string in VBA?
A. Users can easily access and modify the backend data
B. The database can be opened without any authentication
C. The encrypted password keeps the data secure
D. It bypasses the need for a front-end interface

Q3. Why might someone choose to set up a password-protected shared Access database instead of using a database server?
A. It is a good free alternative to cloud storage
B. It offers an easier setup for multiple user access without a server
C. It allows integration with web-based applications
D. It eliminates the need for any user authentication

Q4. What is the focus of Lessons 2 through 4 in this class?
A. Generating advanced reports
B. Adding records to the database in various ways
C. Creating custom forms using templates
D. Setting up security permissions

Q5. In Lesson 2, what specific type of records are being added?
A. Records with random values
B. Follow-ups at intervals of 5, 10, 30, and 90 days
C. Inventory records for monthly sales
D. Employee vacation records

Q6. What unique feature is covered in Lesson 3 regarding adding records?
A. Sorting records by date
B. Adding custom boxes with specialized naming and numbering using ASCII and Chr
C. Automating email reports
D. Creating cascading combo boxes

Q7. How does Lesson 4 improve on the method from Lesson 3?
A. It uses queries instead of VBA
B. It imports data from Excel
C. It uses recordsets to perform the same task much faster
D. It focuses on designing the user interface

Q8. What is the main function of the tree view control discussed in Lessons 5 and 6?
A. To display charts and graphs
B. To present hierarchical data like employee and supervisor relationships
C. To filter data by date
D. To summarize sales figures

Q9. In the example for the tree view control, why is a self-join used in the employee table?
A. To allow each employee to have multiple addresses
B. To track employee attendance
C. To link employees with their supervisors in a hierarchical structure
D. To store employee photos

Q10. Which of the following is a feature added to the tree view control in Lesson 6?
A. Exporting tree nodes as images
B. Sorting employees by salary
C. Expanding and collapsing all nodes, opening employee forms, setting supervisor combo box restrictions
D. Sending emails to employees

Q11. What restriction is enforced on the supervisor field in the tree view example?
A. Only supervisors can be selected
B. An employee cannot select themselves as their own supervisor
C. Supervisors must be from a different department
D. Only department heads can supervise

Q12. Which version of Microsoft Access does the instructor recommend as the closest retail version?
A. Access 2021
B. Access 365 Standard
C. Access 2019
D. Access XP

Q13. What are the recommended prerequisites before taking this Developer Level 20 course?
A. No prior experience is necessary
B. Only basic table design knowledge is required
C. Knowledge of VBA from Beginner and Expert series, especially Developer Levels 13 and 16-19
D. Certification in SQL Server is required

Q14. Why is it important to understand recordsets before using the tree view lessons?
A. Because the tree view relies on recordsets to load hierarchical data
B. Because recordsets are used for printing reports
C. Because recordsets control user security permissions
D. Because recordsets improve query performance

Answers: 1-A; 2-C; 3-B; 4-B; 5-B; 6-B; 7-C; 8-B; 9-C; 10-C; 11-B; 12-C; 13-C; 14-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is Access Developer Level 20. I'm Richard Rost, your instructor, and in this lesson we're going to cover how to work with recordsets connected to external databases, handling multiple nested recordsets, and setting up the tree view control.

In the first lesson, I'll show you how to use the OpenDatabase command, which allows us to create a recordset that connects to another Access database. This is very useful when you want to pull in information from databases in other departments, like accounting or sales. You can also secure a shared backend database by putting it in a shared folder and adding a password. I'll explain how to include your password directly in the connect string in VBA so only authorized users can access the data. While a dedicated database server like SQL Server is ideal, this approach adds a solid layer of security for Access back ends without one.

Lessons 2 through 4 focus on adding records to a table in various ways. In lesson 2, we'll automate the process of adding follow-up reminders at certain intervals, such as 5, 10, 30, and 90 days. Lesson 3 was inspired by a student's question about automating the creation of custom numbered boxes using specific naming conventions. I'll walk you through using ASCII codes and the Chr function in VBA, as well as working with multiple nested For loops to get the job done.

Lesson 4 takes what we've done in lesson 3 and speeds up the process by using recordsets instead of Dlookup or record-adding functions in VBA. Recordsets are much faster and more efficient for these tasks.

In lessons 5 and 6, we'll focus on using the tree view control to display hierarchical data in a form. I'll show you how to set up a tree view to represent data such as employees and their supervisors using self-joins. You'll learn how to load records as nodes in the tree, expand and collapse all nodes, handle events when a node is clicked, and even open up a subform for the selected employee. We'll make sure the combo box for the supervisor does not allow you to pick yourself as your own supervisor, which is a frequently asked question.

I am using Microsoft 365 for these tutorials, but if you have Access 2019 or even going back to Access 2007, you should be able to follow along without any issues.

For prerequisites, keep in mind that this is an advanced-level course. You need to understand VBA from my Developer Level 1 class and have a solid grounding in the Beginner and Expert courses. I strongly recommend you have completed Developer 13 as well as Developer 16 and later because those cover the introduction to recordsets. If you have not worked through Developer 16 through 19 yet, you might find yourself a bit lost in today's material. Even for the tree view lessons, a solid understanding of recordsets is necessary because that's how we load data into the control.

Now is a good time to get comfortable and join me for Access Developer Level 20.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Connecting to external databases with OpenDatabase
Using passwords in the connect string for security
Adding follow-up records at set intervals
Custom auto-numbering with ASCII codes and Chr function
Working with nested For loops for data entry
Adding records with recordsets for speed
Setting up the tree view control on a form
Displaying hierarchical employee data in tree view
Loading tree view nodes using a recordset
Expanding and collapsing tree view nodes
Displaying subforms on tree view node click
Setting up supervisor combo box to prevent self-selection

Article

Welcome to this tutorial on advanced Microsoft Access development, where we will explore how to connect to external databases using recordsets, efficiently add records with VBA, and work with hierarchical data using the tree view control. This article assumes you are comfortable with basic and intermediate Access development, including using VBA and recordsets.

One key skill to learn is how to open and work with data stored in another Access database file without having to import everything into your current project. This is accomplished using the OpenDatabase method. This feature is very useful if your company has multiple Access databases for different departments such as sales or accounting. For example, suppose the accounting department has a secured back-end database that is protected by a password and located in a shared folder on your network. With VBA, you can connect to this external database by specifying the password in your connection string, allowing you to access and manipulate the data programmatically. This method is not as robust as using a dedicated database server like SQL Server, but it provides a practical solution for many small to mid-sized setups.

Here is how you would open an external Access database using VBA, including sending the password in the connection string for added security:

Dim dbExternal As DAO.Database
Set dbExternal = DBEngine.Workspaces(0).OpenDatabase("PathTo\ExternalDatabase.accdb", False, False, "MS Access;PWD=yourPassword")

Now that the dbExternal object is connected, you can open recordsets on tables or queries within that database just as if they were part of your current file. For example:

Dim rs As DAO.Recordset
Set rs = dbExternal.OpenRecordset("SELECT * FROM Customers")

This approach keeps your sensitive data safe in a backend file and users cannot easily retrieve the password as long as your VBA is protected.

In addition to connecting to external data, another important skill is adding multiple records automatically based on patterns or custom logic. Suppose you want to automate creating follow-up records for your customers at specific intervals, such as 5, 10, 30, and 90 days from today. You can use a simple VBA loop to add these records. Here is an example:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("FollowUps")
Dim intervals As Variant
intervals = Array(5, 10, 30, 90)
Dim i As Integer

For i = LBound(intervals) To UBound(intervals)
rs.AddNew
rs!CustomerID = SomeCustomerID
rs!FollowUpDate = DateAdd("d", intervals(i), Date)
rs.Update
Next i

rs.Close
Set rs = Nothing
Set db = Nothing

This snippet creates new follow-up records for the specified intervals. You can further customize this logic for other needs, such as generating custom-named boxes with specific numbering or naming conventions.

For more complex naming schemes, you might need to use nested For loops and work with ASCII values. For example, suppose you want to create custom box names that follow a pattern like BoxA01, BoxA02, BoxB01, and so on. The Chr function helps you convert ASCII values to letters. Here is how you can use nested loops to achieve this:

Dim letter As String
Dim number As Integer

For i = 65 To 90 ' ASCII values for A to Z
letter = Chr(i)
For number = 1 To 10
Dim boxName As String
boxName = "Box" & letter & Format(number, "00")
' Add code here to insert record into your table with boxName
Next number
Next i

If you are adding a large number of records, using the recordset's AddNew method in a tight loop like this is much faster than methods that rely on action queries or requerying forms.

Working with hierarchical or tree-like data structures is another skill that greatly enhances your Access applications. A common example is an employee directory where each employee might have a supervisor, which is a classic self-join relationship in a table. The tree view control is ideal for visually representing these kinds of relationships.

To set up a tree view control in an Access form, first make sure you have the Microsoft TreeView Control installed as an ActiveX control. You can then use VBA to load nodes into the tree by looping through your records and assigning child nodes under their respective parents. Here is a simplified example to load employee-supervisor relationships:

Dim tv As Object
Set tv = Me.TreeView0.Object
tv.Nodes.Clear

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT EmployeeID, SupervisorID, EmployeeName FROM Employees")

Do While Not rs.EOF
If IsNull(rs!SupervisorID) Then
tv.Nodes.Add , , "E" & rs!EmployeeID, rs!EmployeeName
Else
tv.Nodes.Add "E" & rs!SupervisorID, tvwChild, "E" & rs!EmployeeID, rs!EmployeeName
End If
rs.MoveNext
Loop

With this setup, clicking on each employee in the tree can display subforms or other related details. To prevent users from assigning themselves as their own supervisor, you can filter the supervisor combo box so the current employee is not included as a valid selection:

Me.cboSupervisor.RowSource = "SELECT EmployeeID, EmployeeName FROM Employees WHERE EmployeeID <> " & Me.EmployeeID

Tree view controls allow you to add expand and collapse features, open linked forms, and ensure data integrity by enforcing logic such as preventing supervisory loops.

Although this tutorial is written using Microsoft 365, the approaches described above will work with earlier versions of Access, going back to at least Access 2007. The techniques using DAO recordsets, VBA loops, and ActiveX controls are applicable to most modern Access environments.

To fully grasp these topics, make sure you are comfortable with VBA programming, working with recordsets, and designing forms with controls. If you are new to recordsets or VBA, be sure to review the basics first before diving into these advanced techniques. Once you are ready, working with external databases, automating data entry, and visualizing data hierarchies will give your Access applications new power and flexibility.