Advanced - No Longer Set

How VBA IDE Addins Get Loaded From the Windows Registry

A deep dive into how VBA IDE addins get loaded from the Windows Registry to help you avoid getting stuck in DLL hell.

Static Linking vs. Dynamic Linking

You've likely heard the terms "dynamic link library", "DLL Hell", and "static linking." But what do they mean? Why should you care? And how do they apply to Access?

Code Library

How to Set the Background Color of the Microsoft Access Application Window with VBA

A series of Windows API calls will "repaint" the Microsoft Access canvas with the color of your choice. It is a bit rough around the edges, though...

VBA

The Curious Case of the Return Type of the hWndAccessApp Method in 64-bit VBA

Is everything I thought I knew about 64-bit vs. 32-bit VBA wrong? Or is there a problem with the Access object model? Or is it something else?

VBA

Standard String Concatenation in VBA is Slow and Inefficient

A deep dive into the inner workings of string concatenation in VBA and why appending a single character to a long string can be so expensive.

VBA

The vbNullString Constant in VBA

What is the vbNullString constant in VBA and how does it differ from a simple empty string ("")?

Advanced

Permutation Table

We use Python's handy itertools.permutations() function to populate a local Access table with unique permutation values.

Backwards Compatibility

How to Use VBA to Set the ControlSource Property of an Access Image Control (and Other Deeply Hidden Object Properties)

Ever wonder why you can set some values in the form/report designer Property Sheet but not in VBA? You may just need this well-hidden workaround.

Advanced

Microsoft Access's Surprising Folder Locking Behavior

Microsoft Access could be the source of file and folder locks in ways that might surprise you.

VBA

MS Access Windows API Viewer

Here are three good options for looking up proper Declare statements when calling Windows API functions from VBA.

Advanced

VBA Performance Tip: How to Cache Results of Slow Processes

Store expensive function call results with memoization. Learn to implement this technique with our step-by-step instructions and sample code.

VBA

Choosing the Proper Lifetime of Variables in VBA

Some values should be calculated every time you need them. Others should be stored for performance reasons. But how do you decide which way to go?

VBA

Scope vs. Extent in VBA

Master the extent and scope of your VBA variables for code that's efficient, effective, and easy to maintain.

SQL Server

Epoch Differences Between VBA and SQL Server

Do you have unexplained dates of December 30, 1899 in your SQL Server database? VBA's epoch (i.e., zero date) could be the culprit.

Fluent API

Diving Deeper into the World of Fluent APIs: An Unusual Way of Constructing Class Modules

Martin Fowler first coined the term fluent interfaces back in 2005. What are they, what do you need to know about them, and what do they look like in VBA?

VBA

Dollar Signs at the End of VBA Functions

What's the difference between Left() and Left$()? Why would you use one or the other? And what do *I* do, personally?

Tools

Checking Version Compatibility with NetOffice

Take advantage of the open-source NetOffice project on GitHub to look up version compatibility for the Access/Office/Word/Excel object models.

VBA

Namespaces in VBA

VBA may not have the same level of formal support for namespaces as VB.NET, but with a little creativity, we can realize the same benefits in other ways.

Advanced

Unicode Private Use Areas in VBA

Looking for a safe temporary character or text delimiter that is guaranteed to not exist in your data set? Look no further than the Unicode Private Use Areas.

Advanced

Understanding Signed and Unsigned Numbers in VBA

Signed and unsigned numbers–and their hexadecimal representations–can interact in surprising ways in VBA.

Advanced

HRESULT: Use "Long" and Not "LongPtr"

If the return type of an API function call is an HRESULT in the C++ header file, it should be declared as a Long in VBA (and not a LongPtr).

COM

ActiveX vs. Standard DLLs

What's the difference between an ActiveX (COM) and Standard DLL? When would you choose one over the other?

Advanced

References, Protocols, and Language Specifications

They're not always easy to read, but when you are looking for official answers, it's hard to beat these low-level protocol and language specifications.

Signal vs. Noise

Signal vs. Noise: ShowPlan.out Edition

Reading showplan.out files can be intimidating for new and experienced Access developers alike. This simple rule will help get you started.

Advanced

DISTINCT vs. GROUP BY: Microsoft Access Speed Test

The GROUP BY and DISTINCT clauses can both be used to generate identical results. But which one is faster in Access? And--more importantly--why?