Advanced - No Longer Set (Page 2)

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.

Tools

Simple Fix for Missing DBF Support in Access 2013

Quick fix installers that restore Access 2013 DBF support *PLUS* the techniques I used to build the installers using 7-Zip and Inno Setup.

Advanced

3 Ways to Create Class Instances in VBA

Before you can use a class, you need to create an instance of the class and assign it to an object variable. ... Or do you?

Tools

Application Environment Inheritance

Understanding how applications inherit environment settings from one another can shed light on some otherwise puzzling situations.

Advanced

The Microsoft Access Ternary Operator

It's a function. It's a statement. It's an operator. It's a--what the heck do we call this short-circuit-evaluating IIf() thing and why does it even matter?

Advanced

JetShowPlan Manager

The quickest and easiest way to get started analyzing Microsoft Access query performance is with IslaDogs' JetShowPlan Manager application.

SQL Server

3 Gotchas of the SQL BETWEEN Statement

The SQL BETWEEN statement produces very readable condition expressions. But developers of all skill levels can still get into trouble with it.

Advanced

Using Windows Authentication to Connect to SQL Server from Outside the Domain

You don't need to join your computer to a domain to be able to connect to SQL Server using Windows Authentication. You just need to know this simple trick.

Advanced

Side-by-Side Installs of 32-bit and 64-bit Versions of Access

You can't install two different Office bitnesses side by side on the same computer. Or can you? ...

Advanced

64-bit Unsigned Integers in VBA

VBA is missing a data type. The workaround--while not pretty--is at least straightforward.

Professional Development

UNC Alias for a Local Folder

The subst command lets us mimic mapped network drives. But what if your backend tables are linked using UNC paths? Don't worry. We can handle those, too!

Advanced

Double-declaring APIs

Is it finally time to stop adding the `#If VBA7 Then` guard clause to PtrSafe API declarations? Let's explore.

Forms

2 Ways to Escape Leading Quotes when Adding Items to a List Box

Leading quotation marks can cause problems when adding items to a list box or combo box. Here are 2 easy ways to deal with the issue.

Forms

Escaping Semicolons in a Multi-Column Access ListBox

When adding items to a multi-column list box, the semicolon character is used to separate columns. What if the items themselves contain semicolons?

Defensive Programming

Reduce Logic Errors in Critical Code

Software Developers Can Almost Eliminate Logic Errors With This Powerful Technique

SQL Server

Floating Point Arithmetic in VBA vs. SQL Server

VBA and SQL Server do not perform floating-point operations the same way. Being aware of this difference could save you hours of frustration someday.

Hidden Features

Expressions vs. Code: Implicit Type Conversion

In Microsoft Access, is a whole number literal implicitly treated as a Long or an Integer or a Double? As it turns out, it depends on where you ask.

Combo Boxes

A Lazy-Loading Combo Box In Only 3 Lines of Code

Combo boxes that pull down too many records can slow down your Access forms. Boost performance with my class module and 3 lines of code.

DoEvents

How DoEvents Works: The Nitty-Gritty Technical Details

A peek behind the scenes at the inner workings of the DoEvents function.

Tools

3 Ways to Run the Best Low-Level Windows Debugging Tool

A surprising number of thorny Access performance issues can be resolved with this venerable tool. Here's how to get started with Process Monitor.

DoEvents

A High-Level Overview of the DoEvents Function Call

Why does calling DoEvents save us from the dreaded "(Not Responding)" treatment? It's all about handling "Windows Messages."