hen database developers must deliver time values that are accurate to the millisecond, many assume Access can't handle that requirement. They might deem SQL Server the obvious choice, not realizing that only the most recent version, SQL Server 2008, is capable of millisecond precision. Earlier versions offer a resolution of only 3.3 milliseconds, while SQL Server 2008's new date/time data type supports time values as small as 0.1 milliseconds (or 100 nanoseconds).
However, the assumption that Access can't handle time values to the millisecond is not entirely true. In fact, Access and Jet both can store milliseconds because Double, the underlying data type of date/time, has a resolution one thousand times larger than date/time's smallest increment of one second. However, manipulating time values with millisecond precision in Access requires some crafty programming. You need the help of a couple of API calls, which you wrap in functions that mimic the standard timing functions in Access. This article explains how to apply this technique, which will enable you to create, retrieve, and measure time values down to the millisecond.
What's in the Demo?
Because this technique requires a great deal of code, this article does not outline every single step for recreating the demo. Doing so would just be too impractical. For your convenience, you can download demo .mdb and .accdb files in the accompanying source code instead of creating the example yourself. If you want only the code, both code modules are also included in the download.
The demo database files contain the following components:
- A table (tblTimeMsec) for storing date/time values with a resolution to the millisecond: You can work with any table of time values you like, as long as they contain the appropriate precision. This table simply helps demonstrate the code you'll need when applying this technique to your own database.
- A module (basTimeDemo) that contains a sub procedure named FillTime(): This procedure creates and enters time values into tblTimeMsec. These values include millisecond values. This procedure is for demonstration purposes only, but you can customize it to fit your needs. In addition, it uses Data Access Objects (DAO), so be sure to set a reference to that library.
- A module (basTime) with all the user-defined functions (UDFs) and API calls required to pull off this bit of timing magic: This is the only component that you need to add to your own database. You must use this module intact; don't change the names of any constants or calls. In addition, you might find several other user-defined functions useful for timing techniques beyond the one discussed here (See Sidebar 1. Useful UDFs for Other Timing Techniques).
- A simple query interface for displaying the results of the API calling expressions: This module contains quite a bit of code, much of which can be useful in ways beyond just this technique. Be sure to read the in-line comments for a helpful explanation of how everything works together.