Tag: msaccess

How to validate an IBAN code with VB/A

Bank accounts are identified with a standardized IBAN code that simplifies international transactions. The code identifies the country, the banking institution and a customer account. You’ll find a function to validate an international Bank account IBAN identifier with VB/A in this post, along with a bunch of test IBANs.

There’s a “checksum” contained in the IBAN, that can be validated using a modulo 97 algorithm. You can peek here for a detailed explanation of the validation: https://iban.co.uk/verification.html. I borrowed the modulo 97 algorithm from some code I found online, and wapped it at my sauce.

The encoding standard in Switzerland has its peculiarities; it has a length of 21 characters and the Bank ID is composed of only number digits. Same rules seems to apply for Lichtenstein (https://bank.codes/iban/structure/liechtenstein/).

You will need these small support functions too. IsOnlyNumbers() will return True if a text is composed only of digit characters “0” to “9”, and IsAlphaNum() will return True if the text is composed either of digits or letters “A” to “Z” in UPPER case only.

Here’s a piece of code to test the function, with a bunch of IBANs I gathered online:

Public domain. Enjoy !

Explaining the real time device detector Access demo database

In this post, we’ll examine and explain how the Access application that demonstrates a possible, real life use case, of the device detection feature of the AxDeviceDetector ActiveX server.

See the downloads section below to download a copy of the database.


We want to allow, let’s say, restaurant waiters or employees, to register their shifts by typing in their password, clicking a (start or end) button, and then plugging a USB key into the computer, to confirm they are who they say to be. This is an instance of a multi factor authentication, or MFA, system.

The DeviceDetectorAuthDemo.accdb demo database is essentially a finite state machine implementation.

Using USB device for multifactor authentication demo

States and transitions

The frmMain form can be in one of these 4 states:

  1. Select employee
  2. Enter password
  3. Wait for action
  4. Wait for device detection

It’s a cycle, when the wait for the device detection ends, we return on the first state (Select employee).

There is a constant definition for each of these states:

When placed in one of these states, the form needs to update its UI, so only the next possible state(s) can be triggered. This is done in the EnableStepControls() method. This method also updates the UI to show in which state number it is, by moving a green square (it’s a label control) under the correct state number label.

State transition triggers

There are two classes of events that provoke the transition between these states:

  • UI elements actions

    • Double-click on the employee listbox
    • Button clicks
  • Timer event

Each event code that provokes a state transition has the responsibility to do so only if the UI holds valid values for every possible event parameters. In this simple demo, cmdOK_Click() is a good example:

To change state, DisableCurrentStep() and ActivateStep() are called in sequence.
DisableCurrentStep() executes whatever code is needed, before the state transition occurs:

ActivateStep() executes whatever code needed, before the new state is reached:

By design, there is a parameter for the transition to state 4 (Wait for device detection): the action code, which is one of these possible values defined as constants:

This way, when the device arrival/removal event sent by the AxDeviceDetector ActiveX server arrives, we know what action to take. The action code is stored in the miAction form scoped variable. Here an example, in the code executed when we click on the cmdRegisterDevice() button:

In the moDetector_OnDeviceArrival() there is a “Select Case miAction” statement that can now execute the correct action and transition back to step (ie state) #1 (select employee).

A class of state transitions is missing

Guessed which one ? – If not, then try to select another employee while you’re in state #3. You can’t.

There are at least two “cancel” actions that are missing:

  • A “cancel” button is missing, along the “OK” button
  • Another “cancel” button is missing if we want to cancel the wait for the device arrival.

Both of the action could for instance simply return us to state #1. I’ll let that for you.

About hashing

To link the USB device to an Employee, we generate a simple hash with the password, the device product id and the device serial number. That is discriminating enough to make it reasonably difficult to reverse engineer:

I’ve used here one of the first MD5 functions for VBA I googled for. You can replace that with a SHA256 to make it stronger, and add your additional parameters to generate the hash.

You should however avoid displaying and/or logging too much information, as I do for the sake of the demo, like the device product id, in a production application.

Avoiding blocking UI while handling ActiveX server events

We should avoid displaying any blocking UI elements like message boxes, input boxes or modal dialog boxes. This would prevent the server from notifying us of further events while those UI elements block the application’s VBA thread. The ActiveX server itself runs on another process/thread, so it will not block or crash. It has room for buffering 50 messages, that’s its event queue default size. When the server event queue becomes full, it’s emptied and the cleared events are lost.

About the Employee table

  • We have to store the device serial number (field AuthDeviceSerial), if we want to avoid registering an already registered device to another user;
  • The EventLog table is just here for the demo. You may want to (re)use your own logging system.
  • You should of course split your database, ie do not keep the data tables in the same database as the application, but rather link them.

That’s it !


All the material including the DeviceDetectorAuthDemo.accdb database, is available in my AxDeviceDetector github project


Credits: Post photo by Kentaro Toma on Unsplash

Explaining how to detect device arrival/removal in an ActiveX server in real time

We’ve seen know how to get some useful information (like vendor ID and serial number) from attached Windows devices, using a C DLL. Interfacing the DLL with Visual Basic, was then just a matter of declaring the DLL functions in a standard module. With some more basic wrapping, we’ve obtained a simple API function and a data structure, to query for device information in Access.

Reacting to real time device arrival/removal notifications in VB/A

Getting notified in real time (well, as near as that as we can get, at least), when a USB device is plugged or unplugged from the system is the next step. This will allow us later to do things like build an Access application that can use a USB device as a multifactor authentication device (MFA).

Receiving notifications about device arrival or removal in windows is achieved by registering for that, via the RegisterDeviceNotification() Win32 API function. By then listening for the WM_DEVICECHANGE windows message, we can react accordingly.


VB/A cannot listen for windows messages on its own. We have to use a “subclassing” technique, where we redirect all the messages Windows sends to a VB Form (which itself is a window), to a custom routine. I use a subclassing technique derived from “Sensei” Bruce McKinney’s “Hardcore VB 5” book (free link), where we end up responding to the Windows messages we choose by implementing an ISubclass interface, in a Visual Basic Form. Here’s the snippet in frmMain that handles what we need:

Building an out-of-process ActiveX exe server

An ActiveX out-of-process server written in (classic) Visual Basic, is an independent 32 bits executable that serves objects running on their own threads, in an external process. In VBA hosts like Access or Excel, the Visual Basic code runs in a single thread of execution, so it cannot handle simultaneously the tasks of continuously responding to Windows messages and running the VBA application. I already explained why it would be a bad idea to use subclassing techniques in a VBA host application, talking about how to detect Windows session lock/unlock in VBA.

Object classes that are exposed by an ActiveX OOP server have the additional advantage of being accessible in 32 and 64 bits VBA hosts, thanks to (automatic) COM inter process communication marshalling.

I’ve used this technique in the three ActiveX servers I’m presenting on this series on my blog:

  • AxSessionLockDetector

    • Provides classes and events to detect and react when a user locks or unlocks his/her Windows session.
  • AxDeviceDetector

    • Provides classes and events to detect then there’s a device (or media) arrival or removal from the system (The one we’re discussing here).
  • AxHttpSrv

    • Provides classes and events to implement an HTTP web server in Microsoft Access.
    • Although this is the last one of this series, it is the oldest (I wrote it back in 2011), and it served as a base template for the two others.

However, when writing the ActiveX server we’re discussing here, I ran into a quite unexpected problem.

While trying to raise events or invoking OLE callbacks to notify the host for events, automation errors where popping up, instead of raising events (or executing OLE callbacks), basically rendering the solution useless. The first actionable information I found about that by googling around is in this KB article, located in a quite interesting github repository (it’s a static repository of Microsoft knowledge base articles).

A nice workaround consists in queuing the Windows messages we receive in the function implementing the ISubclass interface, and notifying them a bit later, using a timer to pop them back from the queue.

So, I quickly added a fixed size queue, stored in a circular memory array, specializing the CQueue class I presented in an earlier article where I was illustrating how a circular queue implementation works.

Strangely, I did not have to use this queue/timer technique for the two other ActiveX EXE server, but, alas, I can’t explain why is the difference between all of them.

Events or OLE callbacks ?

For an ActiveX server (or DLL for that matter), we can use two techniques to notify an object instance owner:

  • Events

    • Technically, they’re disguised OLE callbacks. You first declare the event in a class definition module, then raise the event with the RaiseEvent VB keyword;
  • OLE Callbacks

    • The client process has to give a reference to an (untyped) Object, to the ActiveX server class, that will keep a reference on it.
    • The ActiveX server class then tries a late bound call on a specific method name on the object reference, that’s the late bound call or OLE callback.
    • One key difference with raising events is that you control the callback sequence in the ActiveX server, when you chain multiple OLE callbacks; when you raise events, you can’t control in what sequence the client receives them.

The DeviceDetector class in the AxDeviceDetector ActiveX server allows for both methods to be used. For each of the two methods, you also have the option of passing parameters to the event procedures or OLE callbacks or not. If not, then the client can call methods on the class to get them. This is commented in the class source code:



You should see a schema illustrating a bit more nicely how this ActiveX Server works, as the image of this post.
Head to the github repository to download all the related material.

In the next post, we’ll see a real life possible use case for this ActiveX server.
I’ll explain the Access Demo application that you can see here in the mean time:

Using USB device for multifactor authentication demo


Browse the source code for more specific comments and explanations.

How to get useful Windows device information in VBA

Here are (at least) 3 properties of hardware like hard disks (whether they are HDD, SSD, USB or else), printers or anything else that Windows considers a device. These can be very useful to obtain in applications that manage enterprise assets like data and documents:

  • Vendor name,
  • Vendor ID,
  • and serial number.

These hardware properties allow us to uniquely an reliably identify a physical device.

Unfortunately, they’re not so easy to obtain. Particularly if we develop our applications with the “high level” languages that we use in database management systems like Microsoft Access (but also WinDev or FileMaker to name a few others), or in development environments that do not have the necessary, production ready, components or libraries to interact with the system.

Then there are different technologies we can use to get to these hardware properties, like:

  • Windows Management Instrumentation (or WMI), which has already morphed into MI; it is feature rich and COM/ActiveX friendly, but – boy – what a learning curve and a maintenance nightmare threat that would be, just do such a simple thing;
  • .NET, although it would be like using a cannon to kill a mosquito to fire up the .NET runtime engine just to use a nugget of its capacities to bring this small functionality to applications that are not .NET based.
  • The venerable and almighty Win32 API, that we’ll use here from C, to bridge its functionality to Visual Basic, because the Win32 API is mostly C/C++ friendly; my winner here, attaboy.

So, let’s start at the end (!), because we want to get into action immediately, don’t we ?
Some explanation will follow later in this post.

Just before jumping there, let me point out where I am going with all this stuff next:

  • We’re going to combine this code in an out of process, Visual Basic 5 ActiveX EXE server. That will allow us to detect when a USB (or other device type) is plugged or unplugged into the system, in real time;

  • That’s for a bit later, we have some other things to talk about first, but I can’t resist the urge to spoil it:
    using a similar technique (the ActiveX OOP EXE server), we’ll turn Access into a (local machine/network) Web server (whaaat ?!? – Yes, no apache, nginx, node, or whatever needed!)

Watch the (short) demo video

Get device information from an access database demo


  • binaries

    • deviceinfo.dll (32bits version, MD5 sum: fb86ea314cdc4b2fd42f01b3876230d3)
    • deviceinfo64.dll (64bits version, MD5 sum: c27ce08c1c7d982bc475181d6f1cc613)
  • C DLL source code on my github

  • Access Demo database

    • deviceinfo_dll.accdb (contains the MDeviceInfo.bas module with the necessary declares, and additional wrapping to easily use the DLL with one function call and a supporting Type definition).
      Important note: copy the two DLLs (deviceinfo.dll and deviceinfo64.dll) in the same directory where you put the demo database deviceinfo_dll.accdb. The DLLs have to be in one of your PATH directories, or in the same directory of the application using them.

Some (quick) explanation

We can see the VB/A declarations that import the DLL functions in the declarations section of the MDeviceInfo.bas module:

Get to one API function

Instead of crippling our code with calls to each of the DLL functions, we can create one single function that does it all:

This way, we don’t have to worry about forgetting to call the DICreateDeviceInfo() and DIDestroyDeviceInfo() functions that are necessary for the DLL to do its job without crashes.

We gather the retrieved device information in an easy to access structure, in the following (public) Type definition:

Another way of doing it is using a class module. We’ll do that in the next post, where we set up the ActiveX OOP EXE server that will notify us of the devices arrival/removal in real time.

Sample usage

Take a look at the demo() procedure in the MMain.bas module:

About commercial use

If you’re following me thru this coding journey in my latest posts, you’ve probably noticed that I’m putting out all of the code and other assets under either the MIT or the GPL v2 licenses. While the works under the MIT license are completely free to use in all scenarios (commercial or not), that’s not true for works under the GPL v2 license, where you have to give these assets or your derived ones back to the community or whomever you distribute it.

Talking about the journey, if you’re wondering why I’m starting to making code and posts rain in these days (well, I mean, considering my personal publishing scale), allow me to give you a hint: I’ve started my sole ownership company as an independent software developer, back in may 1995…

So, I’m however going to set up a dual licensing system, so you can reuse the code/assets I publish here (or thru devinfo.net), in your commercial applications without worries. I know one buck has not the same value depending on our situation or where we live. Fear not, I can’t make specific promises for now, but I’ll do my best to be fair and affordable for everyone, leaving open the option to paypal me for a coffee or a beer, or just leave a donation (on which I’ll give back somehow), when it is reasonable.

Anyway, if you made it thru here, thank for your attention, don’t hesitate to share a link to this post where you see fit, open or join the conversation here, or drop me a tweet (I’m @francescofoti), I’ll always appreciate that.

And see you soon, because we’re not done yet đŸ˜‰