Archive for June, 2008

MS Access: checking network paths without freezing your application

Microsoft Access Access programming is inherently single-threaded. That’s usually OK as most operations are sequential anyway and it keeps things simple at the programming level.
There are times though where the lack of ability to run code on another thread is sorely missing: anything that takes a long time to run will just freeze the application, making it unresponsive and appearing to be locked and about to crash to the user.

Checking for the existence of network paths

Checking for the existence of network paths (directories or files) is one of these issues that can freeze an application for 30 seconds or more if the folder is not accessible.

This is a type of problem that benefits greatly from running in a separate thread: it can take such a long time that the best way to check for these remote paths is to launch the verification for their existence outside of Access and somehow get the result back and cache it for the current session so we don’t have to suffer these delays again every time we check for that path’s existence.

One easy way to do achieve that goal is to create plain DOS batch files that execute hidden from view, create a result file when they complete their task and delete themselves automatically when they are finished.

How to use it

Download the sample database below then just add the FileUtilities, HashTable and MD5 modules to your project and you can use the code as such:

Dim status As AsyncDirectoryStatus
status = FileUtilities.PathExistAsync("\\\shared folder")

The status variable will return either of the following values:

  • AsyncDirectoryStatus.OK if the path was found.
  • AsyncDirectoryStatus.NotFound if the path was not found (either because it doesn’t exist or you don’t have the rights to access it).
  • AsyncDirectoryStatus.Checking if the verification is in progress and we haven’t received a definite answer yet.
    It’s up to you to decide how you want to handle that case. You could periodically check it, like I did in the example database, or you could disable the controls until you’re getting a confirmed result (by checking every time the user performs some action, like moving from record to record in a datasheet for instance).

You can call PathExistAsync as often as you want to check the status: it will not slow down your application (read the optional arguments section below though).
The result of the verification is cached, so querying the existence of the path is actually only done once; the result of subsequent queries for the same path is just instantly retrieved from memory.

Optional arguments

If you want to force the actual re-checking of a path without using the cached value, you can simply pass the ForceCheck optional parameter:

Dim status As AsyncDirectoryStatus
status = FileUtilities.PathExistAsync("\\\shared folder", ForceCheck:=true)

The first time you query for a path (or force it to be rechecked) there will be a short 150ms delay to give a chance to the function to return its result straight away (in case the path can be resolved quickly).
This may not be desirable if you’re checking a bunch of directories at a time. For instance, this is what I do when my application launches:

' Check a bunch of paths in parallel
PathExistAsync strPathToQualityDocuments, NoDelay:=true
PathExistAsync strPathToFinancialDocuments, NoDelay:=true
PathExistAsync strPathToShippingDocuments, NoDelay:=true
PathExistAsync strPathToPurchasingDocuments, NoDelay:=true

By querying the existence of all these paths as soon as my application launches, I am starting the verification process without introducing delays in the application itself: each verification will start in its own process, in parallel to the main application.
Later in the application, when I need to actually use these paths, their result is likely to be known.

How it works

The FileUtilities module contains the main code.
In it, the PathExistAsync function works in slightly different ways depending on whether it’s the first time it is being called for a particular path or not.

The first time
The first time the function is called for a given path, we create in the user’s temporary folder the small batch file whose name is simply a MD5 hash (see below) of the path with .bat appended to it.
This batch file simply checks for the existence of the path and will create a small file (whose name is the MD5 hash of the path) with either 0 or 1 in it depending on the result of the verification.
We initially cache the status of the verification for the Path into the AsyncDirectories hashtable (see below) as Checking.

Example of batch file automatically created to verify a path:

IF NOT EXIST "\\\going nowhere" GOTO NOTEXIST
echo 1 > "C:\DOCUME~1\Renaud\LOCALS~1\Temp\463C7367D8329BD6209A65A70A7DA08C"
echo 0 > "C:\DOCUME~1\Renaud\LOCALS~1\Temp\463C7367D8329BD6209A65A70A7DA08C"
DEL %0

The Batch file name is 463C7367D8329BD6209A65A70A7DA08C.bat where the long number is actually the MD5 hash of the path we’re checking \\\going nowhere.

Getting back the result
Whenever the PathExistAsync function is called, we check the currently cached result from the AsyncDirectories hastable.
If it is still Checking then we try to verify if we the result file has been created from the running batch. If not, we just return the same status, if yes, we read the result from the file, save it in the hashtable and delete the result file.

Useful libraries

The code makes use of 2 extremely useful libraries that I end up using quite often:

  • a HashTable implementation.
    It makes it easy to create hashtable objects (otherwise known as Associative Arrays) to store and retrieve key/value pairs quickly.
    Hashtables are often used to cache data and can be thought of arrays where the index is a string value instead of an number.
    Here I use a hashtable to keep track of the paths we’ve checked and their result.

  • a MD5 hash implementation.
    MD5 is a way to get a somewhat unique fixed-length value from a chunk of data.
    It’s a mathematical function that guarantees that a small change in input (say a single bit in the input data) has a large effect on the output value (a totally different number will be generated) and that you can’t reverse the function (you can’t obtain the input just by looking at the output).
    It is often used in applications to transform sensitive data like passwords into unique values that can be (somewhat) safely stored because you can’t easily reverse a md5.
    Well, MD5 are not secure any longer but here we just use their ability to transform our path into a unique number that we can easily use as a filename and a key for our hash to retrieve the current status of the path being checked.

Sample database

DownloadDownload the (67KB) containing the Access 2007 ACCDB database.

DownloadDownload the (121KB) containing the MDB database1 (untested as I only have Access 2007).

Test Database


Please refer to the source code in the database for the exact licensing terms.
Note that the license only refers to code by me. When code from other sources is used, you will have to conform to their own licensing terms.

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 Unported License.


  1. A specific version for Access 2000 now included in the archive (updated 25JUL2008). 

7 comments June 20th, 2008

Linux sysadmin: a short RAID trouble-shooting story

Linux I recently had an issue at a remote location (12000km away) where the old multi-purpose Linux server that had been working for the past 5 years wouldn’t boot again after a nasty power failure.
The server was used as a firewall, a local email store, a file server and a backup server, so its failure is a big deal for the small business that was using it.

RAID explained
RAID configurations explained
You can’t always have complete redundancy, so some amount of bad crash is to be expected over the years. Fortunately, I always construct my servers around a simple software RAID1 array and that leaves some hope for recovery.
In this instance, the server would start and then miserably fail in a fashion that would suggest a hardware failure of some sort. Not being able to be physically present and having no dedicated system admin on location, I directed the most knowledgeable person there to use a spare internet router to recover Internet connectivity and connect one of the disk to another Linux server (their fax server) through a USB external drive.

Doing this, I was able to remotely connect to the working server and access the disk, mount it and access the data.

Salvaging the data

Once one of the RAID1 drives was placed into the USB enclosure and connected to the other available Linux box it was easy to just remount the drives:

fdisk will tell us which partitions are interesting, assuming that /dev/sdc is our usb harddrive:

[root@fax ~]# fdisk -l /dev/sdc

Disk /dev/sdc: 81.9 GB, 81964302336 bytes
16 heads, 63 sectors/track, 158816 cylinders
Units = cylinders of 1008 * 512 = 516096 bytes  

Device      Boot    Start         End      Blocks   Id  System
/dev/sdc1   *           1         207      104296+  fd  Linux raid autodetect
/dev/sdc2             208       20526    10240776   fd  Linux raid autodetect
/dev/sdc3           20527       22615     1052856   fd  Linux raid autodetect
/dev/sdc4           22616      158816    68645304    f  W95 Ext'd (LBA)
/dev/sdc5           22616      158816    68645272+  fd  Linux raid autodetect

We can’t simply mount the partitions, they need to be assembled into a RAID partition first:

[root@fax ~]# mdadm --assemble /dev/md6 /dev/sdc1 --run
mdadm: /dev/md6 has been started with 1 drive (out of 2).

The --run argument forces the RAID partition to be assembled, otherwise, mdadm will complain that there is only a single drive available instead of the 2 -or more- it would expect.

Now simply mount the assembled partition to make it accessible in /mnt for instance:

[root@fax ~]# mount /dev/md6 /mnt

We can now salvage our data by repeating this process for every partition.
Using RAID1 means you have at least 2 disks to choose from, so if one is damaged beyond repair, you may be lucky and the mirror one on the other drive should work.

If the drives are not physically damaged but they won’t boot, you can always use a pair (or more) of USB HDD enclosures and reconstruct the RAID arrays manually from another Linux box.

Planning for disasters

The lesson here is about planning: you can’t foresee every possible event and have contingencies for each one of them, either because of complexity or cost, but you can easily make your life much easier by planning ahead a little bit.

Most small businesses cannot afford dedicated IT staff, so they will usually end-up having the least IT-phobic person become their ‘system administrator’.
It’s your job as a consultant/technical support to ensure that they have the minimum tools at hand to perform emergency recovery, especially if you cannot intervene yourself quickly.

On-Site emergency tools

In every small business spare parts closet there should be at least:

  • Whenever possible, a spare Linux box, even if it’s just using older salvaged components (like a decommissioned PC). Just have a generic Linux install on it and make sure it is configured so it can be plugged in and accessed from the network.
  • a spare US$50 router, preferably pre-configured to be a temporary drop-in replacement for the existing router/firewall. Ideally, configure it to forward port 22 (SSH) to the spare Linux box so you can easily access the spare box from outside.
  • USB external hard-drive enclosure.
  • a spare PC power supply.
  • some network cables, a couple of screwdrivers.

There are many more tools, such as rescue-CDs (like bootable Linux distributions), spare HDD, etc that can be kept but you have to remember that your point of contact need to be able to be your eyes and hands, so the amount of tools you provide should match their technical abilities.
Don’t forget to clearly label confusing things like network ports (LAN, WAN) on routers, cables and PCs.

The point is that if you can’t be on site within a short period of time, then having these cheap tools and accessories already on site mean that your customers can quickly recover just by following your instructions on the phone.
Once everything is plugged-in, you should be able to remotely carry-out most repairs.


5 comments June 7th, 2008

Most Recent Posts



Posts by Month