Dark Mode, Bubble Sort, In-Memory ADO Recordsets

In Access Developer 56, we continue building the dark mode system for Microsoft Access by integrating it with Windows settings, allowing your database to read and set the Windows dark mode. We will walk through cleaning up the background form using Windows API, syncing your theme with Windows, and letting Access control the system theme. We will also discuss sorting techniques, including implementing a bubble sort in VBA, using DAO record sets with temp tables, and moving to a more efficient disconnected ADO record set. By the end, you will have practical new tools for your Access development projects.

Lessons

•  00. Welcome (4:45)
•  01. Dark Mode 5 (22:14)
•  02. Dark Mode 6 (20:10)
•  03. Bubble Sort (22:31)
•  04. In-Memory Recordset (18:56)
•  05. What's Next (3:55)
• Total Runtime: 1 hour 32 minutes

Lesson Summary

Access Developer 56: Sync w Windows, Bubble, ADO - Welcome to Microsoft Access Developer Level 56. We will continue refining our dark mode system by properly controlling the background form using Windows API calls and ensuring it behaves correctly within Access. We will walk through how to sync the database's theme with the Windows system theme using registry settings and VBA. Additionally, we will take a practical look at the bubble sort algorithm in pure VBA, and then compare that to more efficient data sorting and handling with DAO and in-memory ADO record sets. This lesson requires familiarity with prior dark mode classes.

Lesson 1: Windows API Form Position & Sizing - In Lesson 1, we improve the dark mode system for Access by handling the background form using a Windows API call (SetWindowPos) instead of looping through open forms. I show you how to ensure the background form stays behind other windows reliably and how to size it to fit the Access window neatly, eliminating unwanted scroll bars. We discuss how to use proper API declarations, the timing required to send the form to the back, converting window dimensions, and the differences between Me.Move and DoCmd.MoveSize. Limitations and alternative approaches for resizing are also addressed.

Lesson 2: Dark Mode To & From Windows - In lesson two, we will walk through how to read the Windows Dark Mode setting from the registry and use it to automatically control your Access database's theme when it loads. We will discuss locating the correct registry key, reading its value using VBA, and applying the corresponding theme within Access. I will show you how to set the Windows theme from inside Access using VBA code, so Access and Windows dark modes stay in sync. Error handling and the limitations of automatic updates while Access is open are also addressed. Additional optional ideas are discussed for further automation.

Lesson 3: Bubble Sort Arrays in VBA - In lesson three, we focus on the bubble sort algorithm, explaining how it works and why you might use it in Access VBA instead of relying on .NET dependencies. I walk through a step-by-step demonstration and code example of bubble sort, loading form names into an array and sorting them for use in a combo box. We'll talk about related compatibility issues, why avoiding external dependencies is important, and preview upcoming lessons where we will explore more efficient methods using DAO and ADO recordsets. This lesson provides a practical introduction to both bubble sort and key programming concepts within Access.

Lesson 4: DAO & ADO Disconnected Recordsets - In lesson four, we will focus on using an in-memory recordset to sort and manage data more efficiently in Microsoft Access. First, we will discuss using a temporary table combined with SQL and a DAO recordset to handle sorting and loading form names. Then, I will show you how to use a disconnected in-memory ADO recordset, eliminating the need for any temporary table and making the process cleaner and safer, particularly for use with SQL Server. We'll compare these practical approaches to what you learned in the previous bubble sort lesson.

Up Next: Review & Next Steps for Developer 57 - In this lesson, we review techniques you learned in Access Developer 56, such as working with the background form and discussing how these skills, while not always immediately needed, can be useful in future projects. We also talk about concepts like the disconnected ADO recordset and touch on VB.net dependencies. Looking ahead, I discuss what is next in Developer 57, where I will show you how to enable Office dark mode, discuss related challenges with different Office installations, and mention plans to build a complete dark mode system for Access, alongside integrating the VB editor.

Navigation

• Prev: D55
• Next: D57

Keywords