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:
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: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: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:The Batch file name is 463C7367D8329BD6209A65A70A7DA08C.bat where the long number is actually the MD5 hash of the path we’re checking \\123.56.78.9\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 security 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 that 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.
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.
It’s official, Gold Member is working for Microsoft, apparently doing some evil research in an unnamed Microsoft lair.
Here is the absolute proof:
He calls himself Erik Meijer now and apparently solved his skin issue but you only need to listen to them to know it’s the same person.
Yes, they are both Dutch, which in itself is already proof enough of their evilness and further confirms they are one and the same.
There is some talk that Microsoft has been the refuge of all that is Bad, and some even say that Steve Ballmer may be none other than Dr Evil himself…
That being said, you should check out videos on Channel 9 where Erik Meijer appears. Despite his obvious evilness, he is actually one of the very bright and interesting characters at Microsoft and always has fascinating things to say about programming languages:
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 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.
This project provides a custom and enhanced message box replacement for the default MsgBoxfound in Access. A Test database for Access 2007 is available at the bottom of this post. (Updated Monday 01APR2013.)
What’s wrong with the default MsgBox
The default message box in Access is sometimes useful to warn, inform or ask confirmation from the user.
It has, however, a few drawbacks:
It is bland: the standard message box does not even follow the currently selected Office 2007 scheme.
The amount of text it can display is limited: if you try to display too much text it will be truncated.
You can’t copy or save the content of the message.
Because popup boxes are viewed as intrusive, people tend not to read them and end-up closing message boxes before they realize they may have contained useful information.
They only displays plain text: you cannot format the message to draw attention to the key points.
They are blocking, meaning that nothing can happen in the main application while the box is displayed (it can’t even shut down).
It will only appear on the monitor that has the main Access application window, even though the message box may have been opened from a form on another monitor.
Sometimes you need to display an important message or require users to make take a decision. Message boxes are not to be abused but they serve a useful purpose.
An enhanced message box
Rather than using the bland standard message box you can now have something a bit more customized.
Plain Text version of the enhanced custom message box under the Office Blue Colour Scheme:
RichText version of the enhanced custom message box under the Office Black Colour Scheme:
Here are the features of the enhanced message box:
It is entirely compatible with the standard one: just change MsgBox to Box using find and replace should be enough (see tip below to avoid getting strange errors).
It allows the user to simply click on a button to copy the content of the message to
the clipboard or save it to a text file to a configurable default location.
It looks and feels like it belongs to the main application, following its colour scheme.
It attempts to prevent users from blindly closing the modal box reading the message: buttons will first be inactive for a configurable amount of time. It’s not a perfect solution, but it is quite effective.
There is a RichBox version that can display rich HTML content, not just plain text, so important parts of the message can be formatted in a useful way.
It is able to display large amount of data. While it’s not something you usually want, it may be useful for the message box to display more text in some situations (log or tracing information, legal documentation, etc).
Rather than sprinkling your code with “& vbCrLf & _” uglies, you can embed newlines in the text itself by using C-style “\n” escape sequences that will automatically be transformed into the appropriate newlines. Makes for clearer code and less typing.
Because you get the source, you can easily customise the message box with new icons and colours to better match your overall application’s personality.
It is non-blocking: if your application forces users to log-off after a certain amount of inactivity, the enhanced message box will just close rather than prevent Access from shutting down like the standard MsgBox does. Of course, it’s up to you to decide how to handle that gracefully, if at all.
It properly displays the expected button captions based on the language of the operating system, so it behaves very much like the default MsgBox (for instance, it will properly display “Cancel” on English systems and “Annuler” on French ones).
It also properly plays the system sounds associated with the type of message. You can also enable or disable the sound effect as needed.
As of version 1.4, it will properly display on the correct monitor in a multi-monitor setup.
How to use it
Download the demo below and copy (drag & drop) the following into your application:
the FormDialog form,
the API_GetTextMetrics module,
the Dialog module.
If you rename the FormDialog, make sure you replace any occurrence to it in the code, in particular in the Dialog module.
Since the enhanced message box is just a replacement for the standard one, you just use it like you would use the MsgBox.
As of version 1.4, you can also customise the button labels instead of using the default ones (thanks to Kristjan for the suggestion):
There are a few additional settings that can be used to change the behaviour of the enhanced message boxes. One is that you can adjust the delay before the buttons become activated. Another one is that you can enable or disable whether beeps should be played or not. The last settings is the folder where we should save the content of the message when the user clicks the Save button on the message box. These few settings make the enhanced message box more customizable.
Large text
The standard MsgBox cannot display much text. On the other hand, there is no real limitation to the amount of text the Box and RichBox can display. When the amount of information is too much to fit the maximum allowed size for the message box the text will overflow and can be scrolled up/down as necessary.
Limitations of the RichBox
The RichBox version relies on the normal TextBox control’s ability under Access 2007 to display RichText wich is nothing more than lightweight HTML. Because font size may be varying a lot in the message, it becomes very difficult to accurately predict the size of the box needed to display the whole message. Short of implementing a complete HTML engine, we have to rely on some assumptions to display HTML. The risk is that sometimes the content may not properly fit the TextBox control in some circumstances. If you use the RichBox, thoroughly try displaying your messages and tweak the HTML as necessary to include additional lines or non-breaking spaces to ensure that the result looks good. If you don’t overuse font size and don’t display in multiple fonts the RichBox should do the right thing most of the time. Don’t overuse the RichBox to display colourful messages. There is a fine line between being informative and tasteless. Keep colours and formatting where it is useful. I think that in most cases, the plain text version Box is more than enough.
Replacing MsgBox in existing code
As I said above, replacing the standard MsgBox is easy but you need to make sure your search and replace parameters are configured correctly:
If you’re getting strange compile errors, it may be because you forgot to tick the Find Whole Word Only and some of the strings containing the letter sequence “msgbox” were replaced in the process.
If that’s the case, you can revert the damage by simply doing a search and replace across the whole project on: - VbboxStyle or VbDialog.BoxStyle to be replaced with VbMsgBoxStyle - VbboxResult or VbDialog.BoxResultto be replaced with VbMsgBoxResult
How it works
The code makes extensive use of Win32 API calls. Most of the hard work is done in the FomDialog class form. There is too much there to really go into the details but you are welcome to have a look at the commented code. The code relies also on a utility function from Stephen Lebans used to calculate the size of of text. I have made some minor modification to that code so I would refer you to his original implementation if you are interested in calculating TextBox sizes for forms or reports.
In the code for the FormDialog, I re-implement some of the expected functionalities of the MsgBox: proper arrangement of the buttons, displaying of the appropriate icon, etc. Once this is done, we calculate the size of the textbox needed to display the whole of the message. In the case of RichText, we first use Application.PlainText() to convert the HTML into properly formatted plain text. We then calculate the Textbox size using a slightly larger font than needed as a way to ensure that the content of the RichText message will fit the box in most cases. Once we know the size of the TextBox, we can easily resize the form to properly display the TextBox. If there is too much text, we resize the form to its maximum permissible (70% or screen width and 90% of screen height) and change some of the visual cues to let the user know the text is overflowing.
One thing of note is the way the form is kept modal. Rather than using DoCmd.OpenForm and DoCmd.Close I use the form as a class and create an instance manually (see the code in Dialog.Box and Dialog.Richbox). I keep this instance alive until I got the form’s result back. If you are interested in knowing how the form is made modal, this is the code in FormDialog.ShowModal() what keeps the form open until the user clicks a button: The Sleep() function is a Win32 API that stops the current process for the given number of milliseconds. This in effects hands back the control to the Operating System for a short time. That way the system is still responsive and does not consume resources when it’s just waiting for user input.
Sample database
You can download a sample database containing all the necessary code as well as a number of tests. This version only contains the database in the new Microsoft Access accdb format.
v1.4: 01APR2013 It’s been a while, but at last some improvements and bug fixes!
As per Julie B’s comment, updated code to properly display the dialog on the proper monitor in multi-monitor environments. The dialog box will open in front of the Access window that currently has focus (assumed to be the one that opened the dialog), so if your application has forms on different monitors, the dialog should open on the right one. If we can’t determine the active window, the dialog box will open in the middle of the monitor containing the main Access application window.
Implemented Kristjan’s suggestion regarding the use of custom button labels. See updated description above.
Corrected background colours for the dialog box so they correctly match the MS Office theme.
Corrected a bug in the code that decided of the correct sound to play.
v1.3: 17MAR2009 Thanks to Henry of Access-Pro.de for proposing a correction to the default buttons behaviour.
Updated behaviour for the default buttons. They are now focused in a way that matches that of the standard msgbox.
Reversed the naming of the buttons on the form to make it a bit more consistent with the standard box.
Corrected bug in Form_FormDialog.FilenameSanitize() function that would
fail to remove all invalid characters for a file name.
File name for the saved text message will be truncated to first 32 characters
of message box title in Form_FormDialog.MakeFriendlyFileName().
Changed the use of FollowHyperlink to ShellExecute to avoid security warning
in some instances in Form_FormDialog.btCopyToFile_Click()
Corrected twips to pixel conversion bug in API_GetTextMetrics.fTextWidthOrHeight() that
would result in an improperly sized dialog box when the text message was too
wide.
v1.1: 08AUG2008
Corrected code for DefaultButtonDelay (thanks to Geoffrey) (was referencing
wrong variable, causing self-referencing code).
Corrected code for Box and RichBox to take the DefaultSavedTextFileFolder into account (the path was previously not passed onto the dialog boxes and
the text file would always be created in the application folder instead of
the one specified by DefaultSavedTextFileFolder)
This work is licensed under a Creative Commons Attribution 3.0 Unported License. Free for re-use in any application or tutorial providing clear credit is made about the origin of the code and a link to this site is prominently displayed where end-users can easily access it.
In my previous article about changing the MS Access colour scheme I had the need to allow the user to restart the database after the colour scheme was changed. (Article and Code Updated 13FEB2009.)
Being able to cleanly restart and compact the application is also useful in other instances:
Changes made to the environment
Recovering from errors (for instance after a network disconnection)
Forcing the user to re-log cleanly into the application
Automatically restarting a long-running application (for instance so that it may automatically compact on close and restart afresh with or without user intervention).
The problem is that you cannot -to the best of my knowledge- close and open again the same database from within MS Access itself. Most executables cannot do that and the way to solve the issue is usually to pass the control to another boostrap programme, close the main application and let the bootstrap programme re-open the main application again. I wanted a simple and clean way of using it. One that would not require shipping external dependencies.
How to use it
Download the sample database below, copy the Utilities module or just the Restart sub defined in it into your own application.
To use it, just call the Restart sub and the application will close and re-open. If you supply the optional Compact:=true parameter, the database will also be compacted during the restart process. This will work for normal databases (mdb/accdb) and also compiled (mde/accde) and runtime (accdr) databases as well.
Important note
If you want to use this code do not enable the Compact on Close option in Access for your database as the code doesn’t pick that up yet. Instead, you can either simply call restart Compact:=true on user action (for instance from a menu) or on other triggers, for instance when the database is being open and hasn’t been compacted for more than a week.
How it works
If you’re curious about the technical details, here is how it was put together. The main idea is that the MS Access database application has to be self-sufficient and restart itself by performing these steps:
create a small batch file
run the batch file, passing the path and extension of our database
close the main application
the running batch file would wait for the MS Access lock file to be removed
once the lock file disappears, we open the database after compacting it if required.
The key point here is that the batch file cannot just reopen the database right away: if the application is big or if it’s compacting on close for instance, it may take several seconds to actually close. The only moment we can be pretty sure that the database is effectively closed is when the lock file is deleted by MS Access.
The batch file is hard-wired in the Restart sub that does all the work: When the application runs the batch file, it passes 4 arguments:
the full path to the MSAccess.exe executable (used for compacting the database)
the full path to the database without the extension
the database file extension without the leading “.”
the appropriate database lock file extension (laccdb or ldb).
This allows us to easily construct the path to either the database or the lock file at line 07 and 09. Line 08 is actually only inserted if we need to compact the database: it simply launches MSAccess.exe with the /compact command line switch.
The funny use of PING is actually a simple way to wait for some time before we check if the lock file is still there or not. There is no SLEEP or WAIT function provided by default in Windows so we have to be a bit creative and use the time-out option of the PING command trying to ping an nonexistent, but valid, IP address. Once the lock file has disappeared, we open the database at line 09 and then delete the batch file itself so we leave no leftovers.
The other thing of note is that we now use a counter to keep track of the number of times we checked the existence of the lock file. Once this counter reaches a pre-determined amount (60 by default, ~ 45 seconds) we consider that there is a problem and the database application didn’t close, so we just exit and delete the batch file.
Download the DatabaseRestart.zip (48KB) containing both an Access 2007 ACCDB and Access 2000 MDB test databases.
Added optional parameter to compact the database during restart.
v1.1: 09AUG2008
Now a separate test database (used to be bundled with the Colour Scheme sample).
Added support for older Access versions (an Access2000 MDB is now included).
Corrected wrong lock file extension for accd* files.
Added a time-out feature after which the batch file will delete itself
after a while if the Access lock file wasn’t released
(for instance following a crash).
Added checks to delete the batch file if it has not deleted itself for some
reason (for instance after a reboot).
The batch file now has a unique name based on the name of the database,
allowing multiple databases to be restarted from the same directory.
This is a simple technical weblog where I dump thoughts and experiences from my computer-related world. It is mostly focused on software development but I also have wider interests and dabble in architecture, business and system administration. More About me…
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.
Download the PathExistAsync01.zip (67KB) containing the Access 2007 ACCDB database.
It’s official, Gold Member is working for Microsoft, apparently doing some evil research in an unnamed Microsoft lair.
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.
