Microsoft Access Field Data Type Reference

This page lists every possible field data type that a native Access (Access Database Engine) database can contain. It also shows which methods can be used to create fields of the given type. The information here applies to Access 2007+ (the ACE engine) but most information holds true for earlier versions as well.

Contents

List of Microsoft Access Data Types

Types of Types

Officially, there are 13 primary data types in Microsoft Access. See SQL Data Types. There a number of subtypes, however, depending on how an individual field is configured. Some data types add behaviors or formatting, and others allow multiple values to be stored in the same field. Read more


Arrays in VBA – Part 2: Array Assignment Rules

Arrays are powerful tools in VBA. However, the many types of arrays and the fact that in VBA arrays are value types means that assigning array variables is tricky business. In this post I'll explain all the rules involved. In a future post I'll dive into the implications of value-type semantics for arrays. This post refers to reference and value types a lot. If you need a refresher on what that means, start with Values and References in VBA.

Read more


Values and References in VBA

Visual Basic for Applications has a unique system of data types. One of the most fundamentally important concepts to understand is the difference between values and references in VBA, but it is very difficult to find clear explanations of this concept. I attempt to present one here. In this article I describe the difference between values and references, and between value types and reference types. In another article I will describe how the ByRef and ByVal keywords work.

Read more


The Bang! (Exclamation Operator) in VBA

One of the most peculiar aspects of Visual Basic for Applications in the ! operator, called the "bang". There's a lot of confusion about the bang operator, and nowhere have I seen the correct whole story about it. So, I present it here!Read more


Arrays in VBA - Part 1: Types of Arrays

There are many kinds of arrays in Visual Basic for Applications. Specifically, there are three fundamental attributes of each array, determined at compile time by how the array is declared:

  • Element data type
  • Number of dimensions
  • Fixed or Dynamic Length

Each of these attributes will be discussed in detail in this article. Read more


Collections in VBA - Overview

In VBA programming collections of all types are indispensable. For both the new and experienced VBA programmer there is a lot to know about collection types in VBA, so I'm going to cover them one by one. In this post I'll provide a quick survey of the available collection types.

Types of Collections in VBA

There are three fundamental collection types in VBA

  • Array
  • Collection
  • Dictionary

Read more


Accurate Performance Timers in VBA

There are many times when you want to know how fast your code really is. Especially if you find your VBA application responding slowly you need to know where the bottleneck is occurring. You can build a Stopwatch type class around the built-in VBA Timer function, but this will only get you resolution up to a few milliseconds and also suffers from some quirks in the Timer function. I won't cover those in this article, but I will show how to make a super accurate Stopwatch class using the Windows API functions QueryPerformanceFrequency and QueryPerformanceCounter. For an in-depth look at these functions, see the MSDN Magazine article Implement a Continuously Updating, High-Resolution Time Provider for Windows.
Read more


Copy and Paste in VBA

It can be really useful to have simple string Copy and Paste functions in VBA. I use them frequently, as during development on MS Access applications I often use the Immediate window almost like an interactive command prompt. Sometimes I want to copy the output of a function directly to the clipboard, or fetch (Paste) data from the clipboard as input to a function. In this article I illustrate how to create simple Copy and Paste functions using the Microsoft Forms DataObject class.

Read more


Organizing Code With Namespaces in VBA

Many programming languages provide ways of logically grouping code by name at multiple hierarchy levels, called namespaces. Java, ActionScript and others call them packages, and they are tied to the file system organization of the source code. .NET programming languages just call them namespaces and they can be defined anywhere independent of file structure. In this article I show how to achieve a similar effect in VBA using a combination of standard and class modules.

Read more


Increasing Performance of Regular Expressions in VBA

In a previous post I presented some general-purpose wrappers for adding regular expressions functionality to VBA and SQL in Microsoft Access.

These wrappers work well, but we can make them much more efficient for situations where the same pattern is used many times, such as in a SQL query where the regular expression will be executed on each row of the input table or query. This article describes how to use a simple factory pattern to cache and reuse RegExp objects, dramatically increasing performance when the same pattern is used more than once during the lifetime of a program.

Read more