Welcome

Access Developer 56: Sync w Windows, Bubble, ADO

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

In this lesson, 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.

Navigation

Prev: D56
Next: Lesson 1

Keywords

Access Developer, dark mode system, Windows API, VBA bubble sort, record sets, DAO record set, ADO record set, Windows theme registry, sync Access Windows theme, background form sizing, Me.Move vs DoCmd.MoveSize, in-memory sorting, combo box array, tempor

Subscribe to Welcome
Get notifications when this page is updated

Intro

Transcript

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

In today's course, we are going to continue working with our dark mode system. We are going to clean it up the right way by using a Windows API so our background form behaves properly and looks more polished. Then we are going to tie directly into Windows by reading and even controlling the system theme so everything stays in sync automatically.

From there, we take a short detour into some practical computer science by building a bubble sort in pure VBA. This way, you understand what is happening behind the scenes and can avoid unreliable dependencies. Then we will bring it back to real-world Access development by showing you faster, cleaner ways to handle sorting using regular record sets and DAO record sets with temporary tables.

Next, we are going to step it up to disconnected ADO record sets that run entirely in memory, with no tables required. By the end of today's course, you will have a more robust dark mode system, a better understanding of how Access interacts with Windows, and several practical techniques you can use right away.

As always, I recommend you watch all my previous classes. This continues the dark mode series of classes that we started in Access Developer 55. If you do not watch those, you are not going to understand what we are working on today, unless you are just here for the sorting stuff, the bubble sort and all that. But I always say do not skip levels.

We are working with Microsoft 365. It is currently 2026, so the closest retail version is Access 2024. If you have questions, post them down below. And if you have questions about Access in general, post them in the forums so that everybody can benefit.

Alright, here is what is covered in today's class.

In lesson one, we improved the dark mode system for Access by handling the background form using a Windows API call instead of looping through the open forms. I will 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 those unwanted scroll bars. We will 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.

In lesson two, we will walk through how to read the Windows dark mode setting from the registry and use it to automatically control the Access database 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.

In lesson three, we are going to take a little detour from the dark mode system. We are going to focus on some actual computer science algorithms, specifically the bubble sort algorithm. I will explain exactly how it works and why you might use it in Access VBA instead of relying on .NET dependencies, which is what we did before. I will walk you through a step-by-step demonstration and code example of the bubble sort, loading form names into an array, and then sorting the array for use in our combo box with all the forms in it.

While lesson three was fun for a bit of theory, in lesson four we will focus on using an in-memory record set to sort and manage data more efficiently. First, we will discuss using a temporary table with SQL and DAO record sets. Those are the ones we have been using all along. Then I will show you how to use a disconnected in-memory ADO record set, eliminating the need for the temporary table and making the process cleaner and safer, particularly for use with SQL Server.

So that is what is covered today in Developer 56. Sit back, relax, grab your cup of coffee, and get ready for lesson one. Remember to watch once through and then do it a second time following along with me.

Here we go.

Quiz

Q1. What was the primary improvement made to the dark mode system in lesson one?
A. Adding more color schemes to Access forms
B. Handling the background form using a Windows API call
C. Installing a third-party dark mode plugin
D. Creating new forms for each user

Q2. What method was discussed to ensure the background form stays behind other windows reliably?
A. Setting form properties manually
B. Using themes from the internet
C. Sending the form to the back with proper API timing
D. Locking the form in design view

Q3. How does the updated dark mode system ensure it fits the Access window neatly?
A. By maximizing the form to full screen
B. Using fixed dimensions in the form's properties
C. Sizing the background form based on the Access window and eliminating unwanted scroll bars
D. Allowing the user to resize manually

Q4. Which tools were compared for moving or resizing forms within Access?
A. DoCmd.OpenForm and Me.Close
B. Me.Move and DoCmd.MoveSize
C. Windows Registry and SQL Server
D. Record sets and arrays

Q5. In lesson two, how can Access detect the current Windows dark mode setting?
A. By analyzing the current open forms
B. Reading the setting from the Windows registry using VBA
C. Checking a value in the Access options
D. Using a macro to detect colors on the screen

Q6. What benefit does syncing the Windows and Access theme using VBA code provide?
A. Allows users to change themes through Access only
B. Keeps Access and Windows dark modes in sync automatically
C. Removes the dark mode feature from Access
D. Slows down the application

Q7. Why was the bubble sort algorithm introduced in lesson three?
A. It is faster than SQL queries at all times
B. To avoid reliance on .NET dependencies in Access VBA
C. To teach advanced graphics programming
D. Because it is the only way to sort forms

Q8. What was used as the data source for the bubble sort demonstration?
A. A table of invoices
B. An array of form names
C. The Windows registry
D. Temporary tables with user data

Q9. What method was introduced in lesson four to sort and manage data more efficiently?
A. Linking to external data sources
B. Using Google Sheets add-ins
C. Using an in-memory record set, specifically a disconnected ADO record set
D. Printing sorted lists on paper

Q10. What is one advantage of using a disconnected in-memory ADO record set over a temporary table?
A. It slows down processing significantly
B. It eliminates the need for a temporary table and is cleaner for SQL Server use
C. It makes code less portable
D. It requires more system resources

Answers: 1-B; 2-C; 3-C; 4-B; 5-B; 6-B; 7-B; 8-B; 9-C; 10-B

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 covers Microsoft Access Developer Level 56, and I'll be guiding you through a continuation of our dark mode system. We're going to make improvements by introducing a Windows API to manage our background form more effectively, giving it a polished look and ensuring that it behaves as intended. This new approach allows our form to act properly behind other windows and syncs with the overall Windows theme for a more consistent user experience.

We'll take this a step further by reading and even controlling the system's Windows theme directly from Access. This means your database can automatically stay in sync with the Windows dark or light mode, so any changes you make in the system will also be reflected inside your Access application without any manual intervention.

After establishing that connection between Access and Windows, we'll take a brief detour into practical computer science. I'll walk you through creating a bubble sort using pure VBA. The point of this exercise is to help you understand what's going on under the hood, removing the need for unreliable outside dependencies. Once we've explored the theoretical side, we'll return to real-world Access work, focusing on faster and cleaner sorting techniques. I'll show you how to use standard record sets and DAO record sets along with temporary tables to sort your data more efficiently.

Then we'll level up by working with disconnected ADO record sets, which can run completely in memory. This process doesn't require any actual tables, making it especially useful for cleaner code and for handling data in applications that also work with SQL Server.

By the end of this class, you'll have a dark mode system that's more robust, a deeper understanding of how Access interacts with Windows, and some powerful new sorting techniques that you can put to use right away in your projects.

Just a reminder, you should make sure to work through all my previous classes, especially if you want to understand the full dark mode series, which began in Access Developer 55. If you skip ahead, you might miss some foundational information unless your main interest is in the sorting algorithms we're covering today. But my suggestion is always to go step by step.

We're using Microsoft 365, and although the current date is 2026, the closest retail version is Access 2024. If you have any questions related to today's material, feel free to post them below. For more general Access questions, use the forums so others can benefit from the discussion as well.

Here's a breakdown of today's lessons.

Lesson one focuses on improving the Access dark mode system. We'll manage the background form using a Windows API call, avoiding the need to loop through open forms one by one. You'll learn how to keep the background form reliably positioned behind other windows and sized correctly to eliminate scroll bars. I'll discuss how to use the right API declarations, manage the timing for sending the form to the back, work with window dimension conversions, and clarify the differences between using Me.Move and DoCmd.MoveSize to adjust forms.

In lesson two, I'll demonstrate how to read the Windows dark mode setting directly from the registry, letting us control the Access theme automatically as the database loads. We'll cover how to identify the correct registry key, retrieve its value with VBA, and apply the appropriate theme setting inside Access. Additionally, I'll show you how to let Access change the Windows theme using VBA, ensuring that both Access and Windows stay in sync.

Lesson three shifts focus for a bit as we explore a classic computer science algorithm - the bubble sort. I'll explain how the bubble sort works and why you might want to use it in pure VBA, especially since previous lessons involved .NET dependencies. I'll walk through a step-by-step demonstration, loading form names into an array, sorting that array, and then using the sorted items in a combo box filled with your forms.

While lesson three is more theoretical, lesson four returns us to practical data management by showing you how to use in-memory record sets for sorting and handling data. First, we'll revisit temporary tables and working with SQL and DAO record sets, which we've used before. From there, I'll explain how to use in-memory disconnected ADO record sets, creating a process that's not only cleaner and easier to manage but also well-suited for SQL Server integration.

That summarizes everything we'll cover in Developer Level 56. Get comfortable and get ready for lesson one. I always recommend watching through the lesson once, then going back and repeating it while working alongside me.

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

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

Article

In this tutorial, we are going to refine and expand your Microsoft Access dark mode system, dive into how Access interacts with Windows behind the scenes, and get hands-on with sorting data using both classic algorithms and modern, efficient methods. Everything in this article is explained from the ground up, so you can follow along and bring advanced techniques into your Access applications without frustration.

We will start by upgrading the way our dark mode background form is handled within Access. Previously, you might have managed the background by looping through open forms and sending the background form to the back, but this method is not always reliable. Instead, we will use a Windows API call to control the form at the operating system level. This ensures the background stays in place and never jumps in front of other forms, no matter what the user does.

To do this, you need to use proper API declarations in your VBA module. Here is an example of how to declare the API functions required:

Declare PtrSafe Function SetWindowPos Lib "user32" (ByVal hWnd As LongPtr, ByVal hWndInsertAfter As LongPtr, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long

In your Access form's code, you first obtain the window handle (hWnd) of your form. Then, using SetWindowPos with special arguments, you can send the background form behind all other windows in the application. This approach eliminates the flickering and occasional misplacement you might see if you just loop through forms and use Access's built-in Move methods.

Another problem you might face is with the form size. Sometimes, your background form might create unwanted scroll bars if it is not sized exactly to the Access application window. Instead of using Me.Move or DoCmd.MoveSize - which work relative to the form itself - you can use the API to directly size the background form and match the Access window size. You may need to convert window dimension units as Access uses twips internally, while Windows APIs expect pixels. You can use the TwipsPerPixelX and TwipsPerPixelY system properties to do this conversion.

After your background form is behaving as it should, we will tie your Access theme directly into the Windows system setting. Modern Windows versions store the dark mode user preference in the registry. You can use VBA to read this registry setting when Access starts, so your application's theme matches Windows automatically. The path for the dark mode setting is usually:

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Themes\Personalize

Within this key, the relevant value is named AppsUseLightTheme. If this value is 0, Windows is set to dark mode. If it is 1, Windows is in light mode.

To read registry values from VBA, create a function like this:

Function GetWindowsThemeDarkMode() As Boolean
Dim ws As Object
Set ws = CreateObject("WScript.Shell")
On Error Resume Next
Dim value As Variant
value = ws.RegRead("HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Themes\Personalize\AppsUseLightTheme")
On Error GoTo 0
If value = 0 Then
GetWindowsThemeDarkMode = True
Else
GetWindowsThemeDarkMode = False
End If
End Function

You can use this function in your startup routine to automatically set your Access color theme when your program loads.

If you want, you can go a step further and actually change the Windows theme from within Access, but this involves writing to the registry. Do this carefully, as it will affect the entire system and all applications. In most cases, simply syncing Access to match Windows is best, rather than forcing system changes.

Next, let's talk a little about pure computer science and look at the bubble sort algorithm. This is a simple, classic sorting technique that you can write entirely in VBA, with no dependencies on external libraries or .NET code. Bubble sort works by iterating through a list (an array in VBA), comparing pairs of adjacent items, and repeatedly swapping them if they are out of order. This process continues until the whole array is sorted.

Here is what a basic bubble sort looks like in VBA:

Sub BubbleSort(arr() As String)
Dim i As Integer
Dim j As Integer
Dim temp As String
Dim n As Integer
n = UBound(arr)
For i = 0 To n - 1
For j = 0 To n - i - 1
If arr(j) > arr(j + 1) Then
temp = arr(j)
arr(j) = arr(j + 1)
arr(j + 1) = temp
End If
Next j
Next i
End Sub

Suppose you have an array of form names that you want to display in a sorted combo box. First, load the form names into the array, sort them with BubbleSort, then assign the array to your combo box's RowSource.

While bubble sort is a great teaching tool, in practice, Access offers more efficient ways to sort data. For example, you can use record sets based on SQL queries to retrieve sorted results from your tables. You might also create a temporary table, insert items as needed, and use SQL's ORDER BY clause to get sorted data.

If you prefer not to work directly with tables or want to keep everything in memory for speed, consider using an in-memory, disconnected ADO record set. This approach is faster and avoids the need to manage physical tables, which is particularly useful for SQL Server back ends.

Here's how you can create a disconnected, in-memory ADO record set with VBA:

Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")
rs.Fields.Append "FormName", 200, 255 ' 200 = adVarChar
rs.Open

' Add data
rs.AddNew
rs("FormName") = "frmCustomer"
rs.Update

rs.AddNew
rs("FormName") = "frmInvoice"
rs.Update

rs.Sort = "FormName"

' Now you can loop through sorted results
rs.MoveFirst
Do While Not rs.EOF
Debug.Print rs("FormName")
rs.MoveNext
Loop

This method keeps everything fast and avoids the need for any additional tables or permanent objects in your database.

By the end of these techniques, you will have improved your dark mode system so it stays visually polished and in sync with Windows, and you will know several flexible ways to sort lists - using classic algorithms or modern, efficient record sets entirely in memory. Try integrating these techniques into your own Access projects to streamline your user interface and give your users a better, smarter experience. If you have questions, keep learning, experiment with the code above, and do not hesitate to ask in dedicated Access forums so others can benefit as well.