Access Developer 56

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)
•  06. Access Developer 56 (0:00)
• Total Runtime: 1 hour 32 minutes

Resources

•  File Download
•  PDF Handbook

Navigation

• Prev: D55
• Next: D57

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.

Outline - 0. Welcome (4:45) How To Sync Dark Mode With Windows and Sort Data Using Bubble Sort and ADO in Microsoft Access Improving Access dark mode background form with Windows API Ensuring background form stays behind all windows Resizing background form to fit Access window Eliminating scroll bars on background form Proper API declaration and usage in VBA Timing API calls to send form to back Converting window dimensions for proper sizing Differences between Me.Move and DoCmd.MoveSize Reading Windows dark mode setting from registry Locating and reading registry key with VBA Applying Access theme based on registry value Synchronizing Access and Windows dark mode settings Setting Windows theme from Access with VBA Bubble sort algorithm explained in Access VBA Demonstration and coding of bubble sort in VBA Loading and sorting form names into an array Using sorted array in Access combo box Sorting data using temporary tables and DAO record sets Creating and using disconnected in-memory ADO record sets Eliminating temporary tables with in-memory ADO record sets Best practices for sorting with SQL Server and Access 1. Dark Mode 5 (22:14) How To Use Windows API For Background Form Position And Sizing In Microsoft Access Dark Mode Using Windows API SetWindowPos to keep background form behind Explanation of window handles (HWND) in Access forms Public declaration of API and related constants in modules Setting the background form Z-order with SetWindowPos Timing and use of the Timer event for API calls Difference between Me.Move and DoCmd.MoveSize Resizing the background form to match the Access window Using Windows API GetClientRect to get inner window size Explanation and usage of RECT type in VBA Converting pixel dimensions for Move/Resize operations Handling timing quirks when sending forms to back Limitations when resizing the main Access application window Suggestions for workarounds to monitor window resizing 2. Dark Mode 6 (20:10) Read Windows Dark Mode From Registry And Control System Theme From Access Reading Windows Dark Mode setting from the registry Using VBA to read registry keys in Access Retrieving the AppsUseLightTheme registry value Applying Access theme automatically at startup Writing VBA code to check Windows dark mode status Debugging and adjusting logic for light/dark mode detection Handling potential errors when reading registry keys Setting all forms to dark mode in Access Using SaveKey to write to the Windows registry Changing Windows system theme from Access Creating buttons in Access to control Windows dark/light mode Understanding registry key types (REG_DWORD) Synchronizing Access theme with system dark mode settings 3. Bubble Sort (22:31) How To Implement Bubble Sort in Microsoft Access VBA to Sort Arrays Identifying .NET dependency issues in Access VBA Explanation of COM interop and System.Collections.ArrayList Risks of relying on external frameworks in production Why .NET version compatibility matters for VBA solutions Loading form names into a VBA array Implementing bubble sort in VBA with nested loops Step-by-step bubble sort logic and demonstration Swapping array values using a temporary variable Populating a combo box with a sorted array in VBA Avoiding external dependencies when sorting in Access VBA 4. In-Memory Recordset (18:56) DAO with Temp Tables & ADO Disconnected Recordsets for Sort Data in VBA Creating and dropping temporary tables in Access using SQL Loading form names into a temporary table with VBA Sorting data in Access using SQL ORDER BY in a temp table Populating a combo box from a sorted DAO recordset Cleaning up by dropping temporary tables in VBA Creating a disconnected in-memory ADO recordset Appending fields to an in-memory ADO recordset Populating an in-memory ADO recordset with form names Sorting an in-memory ADO recordset by a field Loading a combo box from a sorted ADO recordset Using late binding to avoid reference conflicts in VBA Advantages of in-memory recordsets over temp tables in Access 5. What's Next (3:55) Reviewing Background Forms, Bubble Sort, ADO Recordsets. What's Coming Next? This is a summary/wrap-up and preview message for a Microsoft Access Developer course session. It mentions techniques and topics covered in previous and upcoming classes but does not itself provide a tutorial or walkthrough.

Keywords