Advanced - No Longer Set

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?

Advanced

Details about the Mark-of-the-Web (MOTW)

Microsoft announced that VBA will be blocked soon in all files from the web. Let's dive into how Windows manages this "Mark of the Web."

SQL Server

How Access Reads Data From SQL Server

How does Microsoft Access pull data from SQL Server? You'll be amazed when we pull back the curtain to expose how Access interacts with SQL Server.

Deployment

Adding a Machine-Wide Trusted Location in MS Access

Let me show you how to add a Trusted Location for every user on a device even if that device doesn't have a full copy of Microsoft Access.

Tools

Using ProcMon to Troubleshoot Registry Calls

Finding the correct registry keys for JetShowPlan and ODBC TraceSqlMode can be tricky. Let ProcMon take the guesswork out of the process.