Posts filed under 'Web Design'

MS Access: Restarting and compacting the database programmatically

Microsoft Access 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:

SET /a counter=0
ping -n 1 -w 100 > nul
SET /a counter+=1
IF "!counter!"=="60" GOTO CLEANUP
"%~f1" "%~f2.%3" /compact
start " " "%~f2.%3"
del %0

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.

DownloadDownload the (48KB) containing both an Access 2007 ACCDB and Access 2000 MDB test databases.

Other implementations

Code Updates

v1.2: 13FEB2009

  • 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.
  • Added license notice at top of source code.
  • Updated the article to reflect the changes.

v1.0: 06MAY2008

  • Original version

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

40 comments May 6th, 2008

MediaWiki: Formating and colouring Code

technology01.pngMediaWiki is the wiki software behind WikiPedia.
The issue, when using it as a software development tool, is formatting code in a pretty way. As we did with WordPress before, here are some details to make dp.SyntaxHighlighter work fairly seamlessly with MediaWiki.

Install the client-side highlighter

Download dp.SyntaxHighlighter. Uncompress its content under a new /skins/common/SyntaxHighlighter folder in your MediaWiki installation (don’t forget to make sure the files can be read by the web server; for instance, on Linux you may use chown apache.apache -R *).

In the skin template you are using for your MediaWiki site, insert the necessary code as required. In my example, I use the default /skins/MonoBook.php template into which I added the following:

Just before the closing </head> tag:
Just before the closing tag:

Note that you must include a reference to each source file corresponding to the type of programming language you want to highlight.
Have a look under the /skins/common/SyntaxHighlighter/Scripts/ folder to see which languages you can highlight; there are a lot more than the few I use on my site.

Install the WikiMedia extension

I’ve created a small extension to WikiMedia to allow us to enclose any source code in a new <codesyntax> tag. Click on the View Plain option below and copy-paste the following code into a new file that you will save under /extensions/syntaxhighlighter.php (again, make sure this is readable by the webserver).

Add the following line to the end of your LocalSettings.php file, right before the closing ?> tag.


To highlight code in your MediaWiki pages, just enclose your source code with the new <codesyntax> tag. This tag takes a lang attribute to specify the options that normally would be listed in the class attribute in the dp.SyntaxHighlighter documentation.

For example:

Will display as:

For more information on using dp.SyntaxHighlighter see:

2 comments February 20th, 2007

Chosing a development platform

technology02.pngLAMP, Zend, .Net, Struts, Ruby on Rails, Catalyst, and a hundred other development platforms all compete for you attention, all pretending to be the only thing you’ll ever need to satisfy your every needs in web or UI development.
Making a decision is really hard: you want the best for your new project and want to make the right decision. In most cases, that means not cursing yourself down the line for a choice that didn’t turn out as expected.

When you start looking into all these platforms you can be blown away by the ease of implementation and elegance of some; your head spins at all the features and claims being made and it becomes hard to achieve a rational decision.

I’ve been faced with that very issue recently and it sucks. Actually, the time spent investigating all these possibilities is really what is being sucked away.
Of course, learning about new technologies is not just fun, it’s also essential: you have to keep on top of what’s new and decide if you want to join in on the fun or just stay comfortably where you are.

I also realized that you need to get away from the coolness factor of each these much touted technologies and actually try to be objective.
During my investigation, I tried to decide what were the really important factors that I should consider to make a rational choice.

I came up with the list of the following 10 questions that I think everyone should ask themselves when choosing a platform for an important project.

1- Is the platform wide enough?

New technologies that can show amazing productivity get all the buzz. After all, that’s what we developer want: enough with the nuts & bolts! Give us a toolbox with all new shinny power tools! The problem is that often these technologies are impressive, but looking beyond the sample projects into what actual users get confronted to, you start noticing discussion threads that should obviously signal that the platform is not wide enough.
Choosing a technology that hasn’t achieved its goals can be a huge problem down the line: you implement 80% of your project in record time, only to realise that the platform you chose doesn’t support proper cross-browser detection, or that its implementation of security is way too lax, or that it only support XML for data storage and now you actually need a real database for performance reasons…
In those cases, you can of course develop those areas yourself and contribute to the project, but doing so supposes that you planed it all along and you factored that in your schedule, both in time and in cost.

2- Is it popular?

In other words: are other professional people actually using that platform in real life? I’m not talking about Joe Developer using it for his local community church website. I’m talking about businesses or large organisations actually using that software platform successfully.
If all you need is a simple 3 page website, then professional popularity amongst the Top 500 business companies is probably not that important. On the other hand, if you plan to build a large project, or you know that your project could grow large in the future, then you probably want to ensure that others have taken it there before you.

3- Is it mature?

Has the platform existed for long enough that it went through enough abuse to withstand almost anything you can throw at it?
A technology that is only a couple year old may be too young to have evolved to the point where it actually can solve most common and not-so-common problems. Technology evolves through iterations: each one is a good long hard look at what didn’t work and tries to fix, improve and extend.
A young technology may be more cutting-edge and exciting, but when you need to commit to it for a big project, its age may quickly come as an issue; its shinny surface may blind you to the black holes waiting below…

4- Are local developers available?

Always think about the future. When you’re gone from the project, who is going to look after it? Getting a web project entirely in Rebol may be cool and fun, but how many people in your city actually use that thing anyway?
Aren’t you creating a liability if you’re dead-set on choosing a platform that cannot be maintained?

5- Is it scalable?

Most projects start small but dream of ending-up big.
It’s usually fine at the beginning when the number of users and visitors is manageable, but what happens when you start to be successful?
After all, that’s why we craft our projects: we want them to succeed, we want the world to see them. When the world starts coming though, will the terrific platform we’ve chosen break at the seams?
How much overhead does the platform create? It is flexible in its database support? Is there a way to implement clustering or persisting sessions accross multiple servers?
What’s the cost in memory and CPU resources? How many simultaneous connections can you get? What are the bottlenecks? What do you need to do to compensate for them? How much would it cost?

6- Do you have control?

Some frameworks work hard for you and can make you really productive. Sometime though, they try too hard and hide too much from you.
When a feature doesn’t exactly work the way you want you have to dig deep into the entrails of the beast to make sense of it and change its behaviour.
A platform that was all nice and pretty can get very dirty and complex inside, making it difficult to adapt to your needs.
A platform should do most of the hard work but still allow you to redefine its default behaviour without much hassle. It should be built with extensibility in mind, making easy thing easy and difficult things possible.
A case in point are 4G development platforms: they usually have a fairly narrow field of expertise and tend to use graphics and nice simple paradigms to quickly build complex tasks but when you are faced with a specific issue that hasn’t been solved by the platform, you often have to resort to ugly hacks to get around their limitations.

7- Does it satisfies the essential constraints for your project?

When being blinded by something cool, we often become skewed in our judgement and guilty of lowering the importance of constraints that are actually critical for our project.
In all honesty, I really wanted to use Ruby on Rail, and I liked the principle and elegance of it so much that I came to seriously consider it, that is, until I came to my senses and looked into something that is critical to my project: support for complex Asian languages.
Ruby is being developed in Japan, so you could think that it should be able to handle complex ideographic characters well.
Turns out that the Japanese are not terribly fond of Unicode and, unless I’m mistaken, prefer to use Code Pages instead. The result is that Ruby doesn’t have great support for Unicode, and that sucks.
That’s why Unicode was created: to attempt to simplify all this patchwork of implementations that are not compatible with each other.
Because I needed to be able to handle English, Mandarin, Cantonese and probably any other language out there, Unicode is the only viable option to abstract the issue of language enough that it becomes manageable.
So because of Ruby’s poor support for Unicode, it would not be rational to use Ruby on Rails for my particular project as it would probably greatly increase the complexity and hacks necessary to manage complex Asian languages in a unified and simple way.

8- Documentation?

All framework have a more or less steep learning curve: you need to think differently to adapt to the particular framework’s frame of mind, so to speak.
Make sure that the framework has a lively community, that it has more documentation than you can swallow and that documentation is well organised.
Make sure also that there are samples, tutorials, webcasts, videos or whatever that cover the principle aspects of it.
A beautiful framework no-one talks about means that no-one will answer when you have a question. A beautiful framework without documentation is useless. A beautiful framework with lots of disorganised docs means that you’re going to waste a lot of time experimenting instead of building your project.

9- Support?

Here I mean support in the wide sense of the word: who is behind the framework. Are they likely to stay in business long after your project has been completed?
Are they commercial or Open Source? In either case there are issues for and against: a commercial venture backed by a small company may falter and disappear overnight. Similarly, an exciting open source project managed by only 2 people can suffer the same fate when they decide to move on. Support also includes what help is available to you when you encounter issues. Is there any guarantee that you could have your important technical questions answered?
If you encounter a large issue, is there someone to help you?
I would contend that the best and most serious projects should have professional help available: you can always fall back on paying someone to help you through, whether it’s the original developers, a paid-for support hotline or a third-party specialist, it’s important that you know you can get your answers when you’re stuck.
Developing for an important project without safety net is dangerous: you can waste a lot of time and effort, endangering the very project you are undertaking, if you stumble on a problem you cannot solve yourself.
Large frameworks like .Net or big Open Source projects usually have enough users and support groups and specialists that it’s likely any question you may have has already been answered somewhere or that you can pay someone to help you out when you need it most.

10- What do you know best?

Every framework is based around a particular data-management model. Every framework is built with a particular programming language in mind. Every framework demands that you learn something new. How comfortable are you with the requirements? how long is it going to take you to start to get really productive?
Like their real-world counterpart, it can take very little time to learn a new language’s words and syntax but it takes years to become proficient in it. A framework adds another layer of abstraction that you will need to get experienced at.
If you need to both learn a new language and a new framework, chances are that you are at best months away from being able to churn code without having to look into the documentation every 5 minutes.
Learning new languages and concepts is necessary, but again, if you chose that route you must be sure that your project has that learning curve built-in, otherwise you’re going to move at the pace of a turtle when in fact you chose that particular framework because you though you would be more productive and faster than a sparrow.
If you chose a new framework, make sure that you already possess most of the knowledge it requires: in my particular instance, I chose .Net and C# because I have already worked in that framework and I know it can tackle the project without me having to waste too much time on learning the platform rather than implementing the project.

We all should seize any opportunity to learn something new. Learning is often more exciting than doing the same old thing over and over again and it’s a necessary part of staying in business. Curiosity is an excellent quality that must be nurtured and indulged.
The issue is when to do it.

Choosing a new development framework is not easy. The best way to tackle the problem is to pose it as a risk analysis study.

The framework you chose should simply be the one that poses the least risk to you succeeding your project.

October 1st, 2006

WordPress: Formating and colouring Code

technology01.pngWordPress is pretty good, but it comes with no code formatting tool, yet colouring facilities. I like the simplicity of dp.SyntaxHighlighter for displaying source code in web pages: it works with major browsers and degrades fairly well.

Its particularity is that is does its painting magic on the client side. This can be a drawback in some instances, where the client browser has JavaScript disabled for instance, but since the code to paint is located within





7 comments June 11th, 2006

WordPress: Fixing PNG transparency issues in IE

technology01.png I’m using WordPress to blog this. I usually prefer to use PNG images over GIF for their ability to have variable alpha-channel transparency that makes them look good over any background. PNGs are a much greater improvement over the old GIF’s single-colour transparency mask.

Continue Reading 24 comments May 30th, 2006

Most Recent Posts



Posts by Month