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?

Static Linking vs. Dynamic Linking

Distributing and registering third-party libraries with a VB6 or Access application can be a real pain.

Shared dynamic link libraries (DLLs) made a ton of sense in the '80's when hard drive storage was at a premium and memory was extremely limited.  Having multiple applications sharing the same external code library for managing things like file compression and decompression was a great way to save hard drive space.

It also made it possible to update multiple applications by replacing a single file.

It also made it possible to break multiple applications by replacing a single file.

And thus was born DLL Hell.

If the road through Windows application hell is paved with dynamic link libraries, the scenic bypass that avoids the fiery inferno altogether is beautifully lined with statically-linked libraries.

Static vs. Dynamic Linking

Let's define some basic terms:

  • static: lacking in movement, action, or change
  • dynamic: characterized by constant change, activity, or progress
  • linked: make, form, or suggest a connection with or between
  • library: a collection of programs and software packages made generally available (for our specific purposes, we can define this as "a collection of related software subroutines and functions")

Thus, the key difference between a statically and dynamically linked library is that the former does not change while the latter is subject to constant change.

At a practical level, a statically linked library is a collection of procedures packed alongside the application code inside an executable (an .exe file) that get referenced by the application at compile time.

On the other hand, a dynamically linked library is a collection of procedures packed inside a separate standalone file (normally a .dll file) that get referenced by the application executable file at runtime.

Pros and Cons of Static Linking

There are pros and cons to each approach.  The pros of one tend to be the cons of the other, so I'll simply list the pros and cons of static linking.  Astute readers can infer the pro/con list for dynamic linking from the list below.

Pros of Static Linking

  • Easy deployment: one single executable; no need to register files in the Windows Registry
  • Controlled environment: no need to worry that an update to an external library will break a deployed application
  • Limited damage: introducing a bug into a shared library function only affects those applications where you've explicitly updated the linked library (this is more important for custom libraries than mature third-party libraries, such as Windows libraries)

Cons of Static Linking

  • Harder shared library updates: updating a shared library requires separately updating every application that references it
  • Lack of consistency: there is no guarantee that identically named library functions will work the same across applications
  • Updates may need to be synchronized: if the shared library has an external dependency (such as a database schema), then changing the external dependency may require synchronizing updates to the static library
  • Increased executable size: embedding the static library inside the application executable will result in a larger executable

Despite the lengths of the above lists, I generally find that the pros of static linking outweigh the cons.

In fact, I wrote about the pros and cons of static versus dynamic linking (though I didn't use those terms) in my article on building a VBA code library for Access:

Building Your Library
Thirteen years ago, I chose to maintain a folder of text files rather than a single Access database library. I’ve never once regretted that decision.

Static vs. Dynamic Linking: An Access Example

To help better illustrate the difference between static and dynamic linking, perhaps a Microsoft Access example will be helpful.

Here's an excerpt from the above article, Building Your Library:

It's about this time that you realize you need a more structured approach to managing these functions that you keep reusing. You do some research and decide that you have three options:

  1. The status quo: copying and pasting functions among projects as needed
  2. A code library: a set of text files where you maintain copies of common functions [and copy and paste them into your Access applications as needed]
  3. An Access library: a common .mdb or .accdb file that you reference from your other projects.

Static Linking: The Text File Code Library

Here's how I described my code library approach:

This probably sounds fancier than what it is.  It is nothing more than a folder full of exported Access objects: standard modules, class modules, forms, reports, etc.  This folder would be kept under version control.

When you want to include a standard module or class module in one of your Access applications, you would simply import the module or class module from the folder on your hard drive into your Access application.  If you want to include an individual Sub or Function, you would copy the required procedure from the text file in the folder and then paste it into your front-end Access application.

Importantly, if you make changes to the text files inside your code library folder, those changes will not automatically propagate out to all of your applications.  You would have to individually update each application.

Dynamic Linking: The Common Access .accdb File

Here's how I described this approach:

This is a common Access database file that you reference from other Access database files.  Daniel Pineault has a good article about how and why you might take this approach.

With this approach you maintain a single Access .accdb file with all of your common Functions and Subs.  To call the procedures contained therein, you would add the .accdb file as a reference in VBA via the Tools > References... menu option.  Refer to Daniel Pineault's article for details.

Static vs. Dynamic Recap

Ultimately, the key difference between static and dynamic linking is that:

  • Static linking happens at compile time
  • Dynamic linking happens at runtime

So What?

So what prompted this sudden article on static vs. dynamic libraries?  twinBASIC

With the release of twinBASIC BETA 424, the language learned a new trick: static linking of C libraries, such as the SQLITE3 and libdeflate libraries:

BETA 424
• added: support for using C static libraries (LIB/OBJ files) from within twinBASIC, using the new Import Library syntax, see SQLITE3 Static Library sample

Watch here for future articles about this new capability.

twinBASIC - No Longer Set
twinBASIC is a modern version of the classic BASIC programming language, with the goal of providing 100% backward compatibility with existing VB6 and VBA projects.

Cover image created with Microsoft Designer and DALL-E-3.

All original code samples by Mike Wolfe are licensed under CC BY 4.0