Cypris' lookoutThe Internets of Renaud Bompuis, lambda programmer in awe-inspiring Hong Kong!2014-12-25T01:00:24Zhttp://blog.nkadesign.com/feed/atom/WordPressRenaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=9992014-11-07T14:09:03Z2013-10-01T08:27:20Z In this post, we talk about Unicode, about Strings, about how they sometimes become unexpectedly mangled when you pass them around the system and what we can do about it. We’ll see some Chinese ideograms, peek into memory and display some nonsense on screen in the process.

VBA likes Unicode

Internally, VBA handles strings in Unicode encoded as UTF-16. This means that each ‘character’ of the string uses at least a 16 bit WORD (2 bytes) of memory (not entirely true, but let’s keep it simple).

Below is the proof; it shows the memory content of a VBA string variable that contains the following sentence:

Chinese or the Sinitic language(s)
(汉语/漢語, pinyin: Hànyǔ; 中文, pinyin: Zhōngwén)
is the language spoken by the Han Chinese

If you see squares in place of the Chinese ideograms, then you are probably using an old version of Internet Explorer and do not have the Asian fonts installed: simply use the latest versions of Firefox or Chrome if you want to display them correctly (although you can still go through this post without seeing the exact glyphs).

In memory, the layout of this VBA string is:

Each character of our string occupies 2 bytes: our first letter C whose hexadecimal code 0043 is located at address 0EED7974, then, 2 bytes later at address 0EED7976, we have our second character, h whose code is 0068 and so on. Note that Windows is a Little Endian architecture, so the lowest significant byte 68 appears first in memory, followed by the most significant byte 00 at the next address.

On the right side, the map displays strange characters in place of the Chinese ones because the memory map interprets each byte as a simple ASCII character, but you can see for instance that the ideogram 中 is located at address 0EED79EA and that its code is 4E2D .

The character codes we see here are simply the ones that would be returned by the AscW() function in VBA (more or less).

VBA doesn’t always like Unicode

The thing though, is that VBA considers the outside world to be ANSI, where characters take (generally) 1 byte and strings must be interpreted according to a Code Page that translate the character code into a different visual representation depending on the encoding.

This means that if you are Greek, your system’s code page will be 737 and you will see Ω (the capital letter Omega) if a string contains the hexadecimal code 97 whereas on a system set for western languages, including English, you will see ù: different representations of the exact same character code.

This nightmare of ANSI encoding made it very hard to pass around strings if you did not know which code page was associated with them. Even worse, this makes it very hard to display strings in multiple languages within the same application.

The Office VBE IDE editor is one such old beast: it only support the input and display of characters in your current system’s Code Page and it will simply display garbage or ? placeholders for any characters it can’t understand.

Fortunately, we’re not really living in an ANSI world any more. Since the days of Windows NT and Windows XP SP2, we’ve been able to use Unicode where each possible character, glyph, symbol has its own code point (things can be quite complicated in Unicode world as well, but that’s the topic for another post).

Unfortunately, VBA still inherits some old habits that just refuse to die: there are times when it will convert your nice Unicode strings to ANSI without telling you.

To make the situation worse, most of the VBA code on the Internet was written by English speakers at a time when Unicode was just being implemented (around 2000). The result is that even Microsoft’s own examples and Knowledge Base articles still get things wrong and copying these examples blindly will probably make you, your customers and users very unhappy at some point.

To understand this unwanted legacy, and the solutions to avoid these problems, we’ll go through a concrete example.

Message in a box

Let’s study a simple case: display our previous message using the MessageBox Windows API. The result we want to achieve is this:

Note that in order to display Chinese ideograms, your system must have the proper fonts installed. If not, you’ll end up with little boxes where the glyphs should be displayed.

What we are talking about here is not limited to Asian languages though: it concerns all languages, including English if you ever include any symbols that’s outside of extended ASCII, like the Euro €, mathematical symbols, words in other alphabets like Greek, phonetics, accents, even Emoji (emoticon) symbols.

The GetUnicodeString() function simply returns a string containing the text we want to display. It uses a function UnEscStr() from a previous article to convert escaped Unicode sequences into actual characters.

What we obtain is not what we expected:

All Chinese characters have been replaced by ? placeholders.

Ok, so, if we look again at the signature of the function, we’re calling MessageBoxA. What does this A tacked at the end means? The Windows API has generally 2 version of functions that handle strings: the old ANSI version, ending with a A, and the Unicode version, ending with a W, for Wide-Characters.

Our first tip is: whenever you see a WinAPI declaration for a function ending in A, you should consider the W version instead. Avoid A WinAPI functions like the plague!

Get it right: MessageBoxW

So, now we know that we must use MessageBoxW for Unicode, the API documentation says so as well, so we must be on the right track.

The only change is the W in the function name and we’ll call the Unicode version. Let’s try to open that box again:

Dim s As String
s = GetUnicodeString()
MessageBoxW 0, s, "test", 0

And this displays:

Oh dear. Looks like Chinese, but it certainly isn’t what we expected. Here we’ve just got Mojibake, garbage due to incorrect processing of Unicode.

What could be wrong? We did pass a proper Unicode string, so what happened?

Well, VBA happened: whenever you pass a String as a parameter in a Declare statement, VBA will convert it to ANSI automatically.

Let me repeat this: when you see a Declare statement that uses As String parameters, VBA will try to convert the string to ANSI and will likely irremediably damage the content of your string.

I’ll show you what happens by going back to our memory map. This is the content of memory before we call MessageBoxW:

This is the memory of the same string after the call to MessageBoxW:

The string has been converted to ANSI, then converted back to Unicode to fit into 2-byte per character but in the process all high Unicode code points have been replaced by ? (whose code is 003F).

Note that the memory location of the string before and after the call are different. This is normal since the string was converted, so another one was allocated (sometimes they end-up occupying the same memory though it’s probably an optimisation of the interpreter).

So our string was actually returned modified to us; we can’t even rely on VBA to keep our original string intact…

Get it right, use pointers!

So, it’s clear now, whenever VBA sees that you are passing a String in a Declare statement, it will silently convert that String to ANSI and mangle any characters that doesn’t work in the current Code Page set for the system.

To avoid that unwanted conversion, instead of declaring the parameters as String, we will declare pointers instead so that our final declaration will be:

What we did here is simply define MessageBoxU as an alias for MessageBoxW which we know is the right API function. Then we replaced the declaration for ByVal lpText As String and ByVal lpCaption As String, by pointers values: ByVal lpText As LongPtr and ByVal lpCaption As LongPtr.

From the point of view of the MessageBoxW function, in both instances it will receive a pointer, except that instead of a String, itwill receive the value of a LongPtr, which is functionally identical.

Now, when using our new MessageBoxU function, we also need to pass the value for the pointers to the strings explicitly:

The StrPtr() function has been part of VBA for a while now but it is still not documented. It simply return the memory address of the first character of a string as a LongPtr (or Long versions of Office older than 2010).

There are other functions to get the address of a variable: VarPtr() returns the memory address of the given variable while ObjPtr() returns the memory address of an object instance. We’ll have to talk more about those in another post.

So, now, the result:

Hurray! We did it! No unexpected conversion! If you looked at the memory map, there would be nothing really interesting to see: the string would have stayed the same throughout the call and would be untouched: no conversion, no copy.

Not only did we manage to make call to API functions work, they are also faster because VBA doesn’t have the overhead of these silly Unicode-ANSI-Unicode conversions every time we use the API function.

Parting words

The point of all this is simply to remember the following when involving Strings in Win API calls:

Do not use the A version of API calls, always use the W version instead.

This is also relevant for some of the built-in VBA functions: use ChrW$() over Chr() and AscW() over Asc(), both W version handle Unicode characters and are about 30% faster in my tests (see KB145745 for more information).

VBA will mess-up and convert to ANSI all strings parameters in a Declare statement.

To avoid this mangling, use pointers instead of Strings in your declarations: never declare As String in an API Declare statement. Always use ByVal As LongPtr instead and then use StrPtr() in your calls to pass the pointer to the actual string data.

Similarly, when passing a User-Defined-Type structure that contains Strings to a Declare statement parameter, declare the parameter as ByVal As LongPtr and pass the UDT pointer with VarPtr().

Remember that the VBE IDE cannot process and display Unicode, so don’t expect your debug.print to display anything other than ? for high code point characters.

]]>6Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=8592014-08-22T13:03:19Z2013-09-12T03:03:54ZWell, maybe the profit part is a bit of an exaggeration, but it will be fun, I promise, unless you’re not a geek in which case I really wonder why you are reading this blog.

I’m not going to shock you if I say that strings manipulations and formatting are really ugly in VBA. Who has time to read and debug stuff like this?

Well, the dragon will probably not be visible, even in your browser it’s probably not well supported by your installed fonts, but at least you should see the Euro symbol.

.Net strings

It’s no secret that strings in .Net have a lot more capabilities when it comes to building and formatting. You can do everything in VBA as well, but things tend to be more difficult to read and compose. In .Net, strings can be formatted in lots of different ways, including creating columns of fixed-length text, padding, aligning text to the left or the right, specifying precision for numbers, formatting dates, etc.

Strings can also contain special character literals to insert newlines or tabs for instance and special Unicode escape sequences meant to represent characters that would otherwise be impossible to include in a string by hand.

Character literals and Unicode escape sequences

Before we go deeper into how things works, let’s go through some of the helper functions you will find in the attached code.

The StringUtils.bas module contains the following function that takes a string that may contain any .Net special character literals and Unicode escape sequences and returns a string that will contain the un-escaped version:

UnEscStr(str As Variant) As String

For instance, to include new lines in our strings, just include the \n literal. Adding a tab is simply a matter of using \t. Note that if you want a backslash \ in your string, you will have to escape it to \\ otherwise the function will interpret the following characters in a way you may not expect.

You can also encode any Unicode character using its Unicode code-point expressed in hexadecimal (for instance, the code-point U+0041 represents the character A).

VBA uses UTF-16 for its string encoding, meaning that each character in a string is encoded using 16 bits and should therefore be capable of containing any possible glyph in the world.

However, to include Unicode values in strings is difficult in VBA:

You can’t just type the character in the string: the IDE is a remnant of the past and uses Extended ASCII only. Anything that cannot be displayed in the IDE will come out as ?.

You can use ChrW$() to include Unicode in your strings, but it is quite verbose and ends-up an ugly mess as you have to cut your string in bits to insert Unicode characters:

As a more elegant solution, you can use Unicode escape sequences directly in the strings and they will be converted through the UnEscStr() function.

To properly include a Unicode code-point in your string, you must follow some rules:

If the code-point is between U+0 and U+FFFF, use the \uXXXX sequence (lower case u followed by 4 hex digits). For instance to encode the € sign whose code-point is U+20AC, just include \u20ac in your string.

If the code-point is above 0+FFFF, you must use the \UXXXXXXXX (upper case U followed by 8 hex digits). For instance, to encode the dragon symbol whose code-point is U+1F409, you will have to use \U0001F409 in your string.

There is a third way to include a code-point sequence by using the \x notation. This is similar to \u but you do not have to use exactly 4 hex digits, you can use 1, 2, 3 or 4. For instance, instead of encoding the letter A as \u0041, you can simply use \x41. However, since this notation accepts a variable number of digits, you have to be careful: the string \x27Effect\x27 will not print 'Effect', because E and f are considered hex digits, you will end up with something that will look like )fect' instead. Better use the \u encoding, it’s what you’ll need in 99% of the cases anyway.

String formatting

The StringUtils.bas module also contains an implementation of the .Net String.Format() method:

sFormat(frmt As String, ParamArray args() As Variant) As String

In .Net, string formatting uses {x} place-holders inside the string itself. The place-holder {0} refers to the value of the first parameter, {1} to the value of the second, and so on. Place-holders can also be used to define how the value should be formatted:

Having the most common method as a default makes things a bit cleaner, I think.

The thing to remember, is that in our example, s is an instance of the class, it is not a string, it’s an object, so once you have completed your string composition, you must call ToString() to get the actual VBA string back.

The class is fairly flexible, allowing you to insert values in each formatted string or, as shown above, by adding the parameters in the call to ToString():

You can find out the current length of the string with the Length property.

Be careful that Replace() is case sensitive since all .Net operations are case sensitive. So Replace("line", "Row") is not the same as Replace("Line", "Row").

Using .Net strings in VBA

Using .Net from VBA can be a challenge depending on what part of .Net you want to use and how you want to use it from VBA.

The .Net String object itself is not accessible from VBA. You can’t create an instance of it directly from VBA and when you use an existing instance of a .Net string, it gets converted to a VBA string immediately.

This .Net class is the workhorse behind the implementation of the StringBuilder class and the sFormat function in VBA. Thanks to this easily instantiable object, we can use some of the power of .Net in our VBA string at no cost.

Some performance information

Of course, doing all that processing has a cost, although in the vast majority of cases, it should not be noticeable.

I timed the use of UnEscStr above in the string that contains Chinese Unicode characters. The call takes 0.010ms on my machine (a 4 year old i7)

A call to sFormat similar to the example given in this article took about 0.10ms.

This timing depends a lot of course on the complexity of the string, the type of formatting, the amount of literal characters an Unicode sequences to convert, etc.

So if you are formating 10,000s of strings, or un-escaping 100,000s or strings the processing time will probably add up to be noticeable but in most use cases, even if you have a few 1,000s of strings, processing will not impact performance in any noticeable way.

Code Updates

v1.0: 12SEP2013 Original release.

Code download

The 2 files contained in the archive below are all you need. Actually, if you are only interested in the sFormat() or UnEscStr() function you only need to import the StringUtils.bas module.

However, if you want to use the StringBuilder class, you must import both files.

To import the files, just open your Access application, open the IDE then, from the menu, File > Import File, and select the StringUtils.bas, then repeat for StringBuilder.cls. That’s all there is to it.

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.

]]>1Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=8302014-08-22T13:03:19Z2013-07-07T04:25:55Z I’ve put off writing about x64 MS Office because I was a bit lazy and hadn’t build the necessary environment to test it properly.

In my day-to-day job -as far as Access is concerned-, I have so far targeted the 32 bit (x86) version of Office 2010 but the 64 bit version is bound to become more popular with time and some people already have to use it, even if sometimes their reasons are misguided.

When should I care?

If your code has any chance of being run on Office 2010 and later, and you want to avoid issues when one of your users or customers will have Office x64 (or just one of its components) installed, then you should make sure to use your code uses the new VBA7 constructs.

The changes to be performed only affect the parts of your code that use Win API calls or manipulate Windows control handles and memory pointers.

For a small fraction of applications, actually using Access/Excel x64 could make sense as it allows you to deal with 64 bit integers (as opposed to the standard 32 bit long). While this could be a useful feature, code that use these 64 bit integers will not compile on a 32 bit version of Office.

If you use external libraries and controls

If you are using external libraries and ActiveX controls, it’s unlikely that they will only work in Office x64. The reason is that most libraries (especially older ones) are only available in 32 bits.

For these to work under Office x64, they would have to be compiled especially for 64 bit as well.

completely control the user environment an ensure it will always run Office x86 (32 bit).

you can compile or get 64 bit versions of these libraries and ActiveX components so you can build a version of your application that will work for all your users.

If your code targets Access 2010 and later

There are 2 changes that may affect your code: external library declarations and usage of the new pointer and very large integer types.

Library declarations:

Any declaration for external libraries (such as Win API or code that resides in DLLs), the declaration statement has changed and must use the new PtrSafe keyword to tell the compiler that the declaration should be interpreted in a 64 bit context when relevant. If you omit the keyword, your declarations will always be interpreted as being in a 32 bit context and the results may be random.

For instance, the old declaration for the SetFocus Win API function was:

You will need to replace declarations that use long with the more appropriate type:

LongPtr Use the new LongPtr wherever you deal with declarations that relate to Windows control handles or memory locations.

LongLong Defines a 64 bit integer. The reason for LongLong is mainly to access some 64 bit versions of the Windows API, in particular, for math functions. This type only exists if you are running under a 64 bit version of Access. On 32 bit systems, it will cause a compile error. To avoid that problem where your code needs to run on both 32/64 bit platforms, you can use the new conditional constant Win64:

The compiler will only see and compile one code path and will ignore the other, so doing this does not affect the speed of your application.

If your code must also work in Access 2007 and earlier

Older versions of Access will not be able to compile the new PtrSafe, LongPtr and LongLong keywords introduced by VBA7. If your code must also work in older versions of Access, you must either create 2 versions of your application (not very convenient), or simply use the new conditional constant VBA7 to separate blocks of code that will run on Office 2010 and later from blocks of code that will run on Office 2007 and ealier.

The new VBA7 conditional constant will evaluate to True if it is running under Office 2010 and later.

So now you can create variations of your code that will work everywhere:

Adapting old code

When going through old code, you can’t just add PtrSafe to all API declarations and do a search to replace Long into LongPtr. That would be the surest way to get everything wrong.

Instead, you must be very careful about the intent of the code and understand what your variables are manipulating: is that long really just a number or is it mean to be a memory location or a control handle ?

To help you, at least as far as the most common Win API declarations are concerned, Microsoft has made available a text file with hundreds of such declarations compatible for Office x64: Win32API_PtrSafe with 64-bit Support

If you need to use a WinAPI call that isn’t in the list, you will need to have a look at its actual definition and, based on the type of data being passed around, decide if you need to use a simple Long or a LongPtr.

For instance, I had to use the MonitorFromWindow API function. Its C++ definition is as follow:

HMONITOR MonitorFromWindow(
_In_ HWND hwnd,
_In_ DWORD dwFlags
);

The definition tells me:

That the function is taking a Handle (basically a pointer) to a Window and some flags

and that the function returns a Handle to the display monitor (basically a pointer).

So the API definition that will work on all versions of Access is:

#If VBA7 Then
' Works in 32/64 bits of Office 2010 and later
Declare PtrSafe Function MonitorFromWindow Lib "user32" _
(ByVal hwnd As LongPtr, ByVal dwFlags As Long) As LongPtr
#Else
' Works in Office 2007 and earlier
Declare Function MonitorFromWindow Lib "user32" _
(ByVal hwnd As Long, ByVal dwFlags As Long) As Long
#End If

When using the external function, you also need to be careful in the code and use the VBA7 conditional to separate the parts where you access structure pointers:

Make sure you save, re-compile and test your project as you go along. Also make frequent backups of your application as a wrong declaration could crash and corrupt it. It may even be possible to crash the whole machine, although I have not experienced that so far.

Conclusion

Updating old code to make it work properly on all versions of Access is not difficult. It is usually fairly easy, although it can be a bit cumbersome at times.

The trick is to know what your code really needs to target:

Office 2010 and later, the present and the future, in which case everything is straightforward and you use the new VBA7 keywords PtrSafe, LongPtr and LongLong where appropriate.

All versions of Access, in which case you will have sometimes to separate and maintain 2 versions of your code using the VBA7 conditional.

]]>4Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=7192014-08-22T13:03:19Z2011-09-07T03:37:34ZAs a developer it’s often hard to stay current with your field. New technologies and new ways of doing programming pop up every day, it’s difficult to know if you should invest any time in learning them or if they are just passing fads.

Podcasts are a great way to get exposed to these new things: if you commute, jog, travel, you can easily use that time away from the computer to get better informed and reflect about our field.

I have a 20 minute walk between home and my office. That’s more than 3h of free time per week that I can spend thinking, listening to music or getting entertained and educated through relevant podcasts.

These are my preferred shows so far:

Hanselminutes A 30 minute or so podcast about technologies, usually closely or distantly related to Microsoft development. Scott is an excellent interviewer and his blog is a must read too.

.Net Rocks The unavoidable 60 minute grand-father or all .Net podcasts. Funny and always informative. Also check out the related DnrTV for on-topic screencasts, excellent for getting started on a new technology.

The Polymorphic podcast A nice podcast, the host, Craig Shoemaker, is a great inteviewer. Only drawback is that shows are not regularly produced.

The StackExchange podcast Sometimes random, mildly irritating at times (guests sound quality, audio sync between guest, ramblings about Stackoverflow’s principles) but usually both enlightenning and funny. Topics revolve around Stackoverflow and the various StackExchange sites. It’s always worth listenting to Joel Spolsky, he tends to see clearly in an industry that often borders on the BS.

This Developer’s Life Last but not least, a beautifully produced podcast that covers more philosophical topics through interviews and real-life experiences. It’s not about technology, it’s about the human aspect of being a coder, and what it means to be one.

Whichever you choose, any of these podcasts are bound to expand your knowledge, educate and entertain you!

]]>1Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=7062014-08-22T13:03:19Z2011-07-13T11:22:25Z (Updated Saturday 10DEC2011.) On 28th of June 2011, Microsoft Office 2010 Service Pack 1 and the Access 2010 Runtime Service Pack 1 were issued.

After upgrading my development machine (Win7 x64) and a few clients (Windows 2008R2 x64) to SP1 (x86), I started to get strange issues:

I use .Net libraries from my Access application and suddenly, even when not instantiating any .Net objects, Access would crash, usually on startup, but sometimes when opening the VBE. Decompiling and re-compacting the database would be OK, usually once, but the problem would reappear the next time I would restart the application.

In the Runtime, I would get strange errors, such as The setting your entered isn’t valid for this property, or Action Failed Error Number: 2950, or Runtime Error 3270 Property not found. The strange thing about these errors is that they would occur in places that had not been modified for many releases of our application, parts that have been running without problem until now.

Another weird issue was the systematic reset of our custom ribbon to its first tab. this could happen randomly, but most it could also be reproduced by simply opening a report as a tab page (that fill-in the whole MDI window). When closing that form, the first tab of the ribbon would select itself automatically. This wasn’t happening when closing pop-up windows.

After removing the Office and Runtime Service Pack 1, everything went back to normal.

A fix, finally!

]]>12Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=6652014-08-22T13:03:19Z2010-10-03T04:34:34ZIn the previous article, I showed how to improve the performance of an existing file server by tweaking ext3 and mount settings. We can also take advantage of the availability of the now stable ext4 file system to further improve our file server performance.

Some distribution, in particular RedHat/CentOS 5, do not allow us to select ext4 as a formatting option during setup of the machine, so you will initially have to use ext3 as file system (on top of LVM preferably for easy extensibility).

A small digression on partitioning

Remember to create separate partitions for your file data: do not mix OS files with data files, they should live on different partitions. In an enterprise environment, a minimal partition configuration for a file server could look like:

Hardware:

2x 160GB HDD for the OS

4x 2TB HDD for the data

The 160GB drives could be used as such:

200MB RAID1 partition over the 2 drives for /boot

2GB RAID1 partition over the 2 drives for swap

all remaining space as a RAID1 partition over the 2 drives for / Note though that it is generally recommended to create additional partitions to further contain /tmp and /var.

The 2TB drives could be used like this:

all space as RAID6 over all drives (gives us 4TB of usable space) for /data

alternatively, all space as RAID5 over all drives (gives us 6TB of usable space) The point of using RAID6 is that it gives better redundancy than RAID5, so you can safely add more drives later without increasing the risk of failure of the whole array (which is not true of RAID5).

Moving to ext4

If you are upgrading an existing system, backup first!

Let’s say that your /data partition is an LVM volume under /dev/VolGroup01/LogVol00. First, make sure we have the ext4 tools installed on our machine, then unmount the partition to upgrade:

Important notes

The mounting options we use are somewhat a bit risky if your system is not adequately protected by a UPS. If your system crashes due to a power failure, you are more likely to lose data using these options than using the safer defaults. At any rate, you must have a proper backup strategy in place to safeguard data, regardless of what could damage them (hardware failure or user error).

References

]]>1Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=6342014-08-22T13:03:19Z2010-07-11T10:36:47Z Using a Linux for an office file server is a no-brainer: it’s cheap, you don’t have to worry about unmanageable license costs and it just works.

Default settings of most Linux distributions are however not optimal: they are meant to be as standard compliant and as general as possible so that everything works well enough regardless of what you do.

For a file server hosting large numbers of files, these default settings can become a liability: everything slows down as the number of files creeps up and it makes your once-snappy fileserver as fas as a sleepy sloth.

There are a few things that we can do to ensure we get the most of our server.

Checking our configuration

First, a couple of commands that will help us investigate the current state of our configuration.

tune2fs will help us configure the options for each ext3 partition. If we want to check what is the current configuration of a given partition, says we want to know the current options for our /data mount:

The interesting options are listed under Filesystem features and Default mount options. For instance, here we know that the partition is using a journal and that it is using the dir_index capability, already a performance booster.

The data=ordered mount parameter tells us of the journaling configuration for the partition.

Journaling

So what is journaling? It’s one of the great improvements of ext3: a journal is a special log on the disk that keeps track of changes about to be made. It ensures that, in case of failure, the filesystem can quickly recover without loss of information.

There are 3 settings for the journalling feature:

data=journal the most secure but also slowest option since all data and metadata is written to disk: the whole operation needs to be completed before any other operation can be completed. It’s sort of going to the bank for a deposit, filling the paperwork and making sure the teller puts the money in the vault before you leave.

data=ordered is usually the default compromise: you fill-in the paperwork and remind the teller to put the money in the vault asap.

data=writeback is the fastest but you can’t be absolutely sure that things will be done in time to prevent any loss if a problem occurs soon after you’ve asked for the data to be written.

In normal circumstances all 3 end-up the same way: data is eventually written to disk and everything is fine. Now if there is a crash just as the data was written only option journal would guarantee that everything is safe. Option ordered is fairly safe too because the money should be in the vault soon after you left; most systems use this option by default.

If you want to boost your performance and use writeback you should make sure that:

you have a good power-supply backup to minimise the risk of power failure

you have a good data backup strategy

you’re ok with the risk of losing the data that was written right before the crash.

To change the journaling option you simply use tune2fs with the appropriate option:

# tune2fs -o journal_data_writeback /dev/md5

Mount options

Now that we’ve changed the available options for our partition, we need to tell the system to use them. Edit /etc/fstab and add data=writeback to the option columns:

/dev/md5 /data ext3 defaults,data=writeback 1 2

Next time our partition is mounted, it will use the new option. For that we can either reboot or remount the partition:

# mount - o remount /data

noatime option

There is another option that can have a very dramatic effect on performance, probably even more than the journaling options above.

By default, whenever you read a file the kernel will update its last access time, meaning that we end up with a write operation for every read! Since this is required for POSIX compliance, almost all Linux distributions leave this setting alone by default. For a file server, this can have such drastic consequence on performance.

To disable this time-consuming and not useful feature (for a file server), simply add the noatime option to the fstab mount options:

/dev/md5 /data ext3 defaults,noatime,data=writeback 1 2

Note that updating access times is sometimes required by some software, such as mail software (such as mutt). If you properly keep your company data in a dedicated partition, you can enable the mount options only for that partition and keep other options for the root filesystem.

dealing with errors in fstab

After doing the above on one of the servers, I realized that I made a typo when editing /etc/fstab. This resulted in the root filesystem being mounted read-only, making fstab impossible to edit…

To make matters worse, this machine was a few thousand miles away and could not be accessed physically….

Remounting the root filesystem resulted in errors:

# mount -o remount,rw /
mount: / not mounted already, or bad option

After much trial and rebooting, this worked (you need to specify all mounting options, to avoid the wrong defaults from being read from etc/mtab`):

# mount -o rw,remount,defaults /dev/md2 /

After that, I could edit /etc/fstab and correct the typo…

Conclusions

How much these options will improve performance really depends on how your data is used: the improvements should be perceptible if your directories are filled with large amounts of small files. Deletion should also be faster.

]]>1Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=6182014-12-25T01:00:24Z2009-09-09T05:54:00Z I often have to test String, Variant or Object variables that have no content and could be considered ‘blank’.

For a String type, the string can have no content at all: "", vbNullString.

For a Variant type, the string can have any of the following attributes or values:

it can be Missing if the variable is an unused optional parameter,

it can be Empty if it was never assigned,

it can be Null if, for instance it’s bound to a nullable field or unbound with no value,

it can be an empty string "", vbNullString.

When having to check these variables in code, it can be tiresome to have to go through testing some of these possibilities just to find out that your variable does or not not contains something useful, regardless of the type of variable you are using.

To avoid having to do all these tests, make the code a bit more tidy and allow me to move on to more important things, I use this small utility function quite often:

So now I don’t have to worry so much about the type of the variable I’m testing when I want to know if it contains useful data:

...
' Here assume that CustomerReference is a control on a form.
' By using IsBlank() we avoid having to test both for Null and empty string.
If IsBlank(CustomerReference) Then
MsgBox "Customer Reference cannot be left blank."
End If
...

Obviously, IsBlank() doesn’t replace the other tests but I found it to be more straightforward to use in most cases.

]]>6Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=6132014-08-22T13:03:19Z2009-08-26T02:54:42ZRecently I found myself constrained by the puny 200GB of my Mac Book Pro and I bought a 500GB Seagate drive to replace it (a fast 7200 rpm one). The Macbook Pro has no easy access for the drive so you have to resort to dismantling the case to access it. This put me off replacing the drive because I would probably be voiding the warranty and was running the risk of damaging this expensive piece of equipment.

I’ve been filling the drive with pictures from my recent camera purchase and I couldn’t put it off any longer, so I bought the new drive and went online to find some good tutorial on how to crack open the Macbook Pro case.

After a few searches, I noticed that many people were referring to the iFixit.com website. It was very easy to find the tutorial I was looking for, I didn’t have to register, and each step was made very clear and simple. It took no time to open the case and replace the drive. I was very happy with that find.

Now, that’s not the end of the story.

A couple of days before I replaced the drive the left fan of the laptop suddenly became noisy. This would happen a few times a day, at random, and would last 10-20 minutes. My only solution to get this repaired was to get to the local Apple service shop. Even though I knew exactly which part number was to be replaced, they still wanted me to:

go across town to visit them so they could see for themselves what the problem was: annoying because the problem was intermittent so I may have to go for nothing.

That took me all of 10 minutes; I placed my order on Thursday and the next Monday I received the part … halfway across the globe!

I also got a survey request from iFixit and left some comments, from which I got back two nice detailed email follow-ups, one from the CEO saying they were implementing my remarks as part of their site improvement efforts.

Well, I thought I would share this story. It’s not that often that you get excited by an online vendor that not only does its job well but goes beyond expectations.

]]>0Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=6062014-08-22T13:03:19Z2009-06-11T08:26:06Z I have been spending a lot of time trying to find out why some of the code used to insert new records into a linked SQL Server table would systematically fail with an error:

Run-time Error '3155' ODBC--insert on a linked table failed

It was driving me mad. I could insert a simple record using SQL Server Management Studio, I could add new records to the table in datasheet mode within Access, but as soon as I tried to insert a record from code, whether using DAO recordset or executing the same SQL INSERT, it would miserably fail.

After a fair bit of investigation and tests, of which you can read the full account on the question I asked on StackOverflow, it turns out that this is a long-standing bug in the ODBC Driver (or Access).

Memo fields in Access are usually translated into nvarchar(MAX) in SQL Server by tools like SSMA. Unfortunately, when you link tables having these fields using the SQL Server Client driver, these fields get incorrectly interpreted as string, even though they appear ok from the table design view. It’s only if you try to insert something into the field, either text larger than 255 chars or NULL, that you get the error message.

So, the solution, at least in this case, is to revert to the older SQL Server ODBC driver instead, or use varchar() instead of nvarchar(), but if you’re dealing with Unicode, you have to stick with nvarchar().

References

]]>3Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=5272014-08-22T13:03:19Z2009-04-13T04:00:53Z In the perspective of upsizing my current Access 2007 application, I have been trying to understand a bit more about the possible performance impact of various choices of Primary Keys.

My problem is that currently, the Access application uses autoincrement numbers as surrogate Primary Keys (PK). Since I will need to synchronise the data over multiple remote sites, including occasionally disconnected clients, I can’t use the current autoincrement PK and will need to change to GUID.

To see for myself what could be the impact, I made a series of benchmarks. This first part is fairly simple:

For the table using a GUID, we use the NewSequentialID() instead of NewID() to create new keys. This is supposed to offer much better performance as the generated GUIDs are guaranteed to be sequential rather than random, resulting in better index performance on insertion.

For the Access version of the tables, we basically use the same definition, except that we used 4 tables:

Basically, we perform 1000 transactions each inserting 1000 records into the table ProductGUID or ProductINT.

Access 2007 Test code

To duplicate the same conditions, the following VBA code will perform 1000 transactions each inserting 1000 records. Note that the recordset is opened in Append mode only. The importance of this will be discussed in another article.

' Run this to inset 1,000,000 products in batches of 1000
' In the given table
Public Sub Benchmark(TableName as String, InsertSeqGUID as Boolean)
Dim i As Integer
For i = 1 To 1000
Insert1000Products TableName, InsertSeqGUID
Next i
End Sub
' Insert 1000 products in a table
Public Sub Insert1000Products(TableName as String, InsertSeqGUID as boolean)
Dim i As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ws As DAO.Workspace
Dim starttime As Long
Dim timespan As Long
Set ws = DBEngine.Workspaces(0)
DoEvents
starttime = GetClock ' Get the current time in ms
ws.BeginTrans
Set db = CurrentDb
Set rs = db.OpenRecordset(TableName, dbOpenDynaset, dbAppendOnly)
With rs
For i = 1 To 1000
.AddNew
If InsertSeqGUID Then !ID = "{guid {" & CreateStringUUIDSeq() & "}"
!SKU = "PROD" & i
!Description = "Product number " & i
.Update
Next i
End With
ws.CommitTrans
rs.Close
timespan = GetClock() - CDbl(starttime)
Set rs = Nothing
Set db = Nothing
' Print Elapsed time in milliseconds
Debug.Print timespan
DoEvents
End Sub

ProductGUIDRandom table: we let Access create the Random GUID for the primary key.

ProductGUIDSequential: we use the Windows API to create a sequential ID that we insert ourselves.

Test results

Without further ado, here are the raw results, showing the number of inserted record per second that we achieve for each test over the growing size of the database (here are only shown tests comapring Sequantial GUID and Autoincrement on SQL Server and Access, see next sections for the other results):

What we clearly see here is that performance when using autoincrement and Sequential GUID stays pretty much constant over the whole test. That’s good new as it means that using Sequential GUIDs do not degrade performance over time.

As a side note, in this particular test, Access offers much better raw performance than SQL Server. In more complex scenarios it’s very likely that Access’ performance would degrade more than SQL Server, but it’s nice to see that Access isn’t a sloth.

Using Sequential GUID vs Autoincrement in Access

The results show that we do take a performance hit of about 30% when inserting Sequential GUID vs just using autonumbers. We’re still getting good results, but that’s something to keep in mind.

In terms of CPU consumption, here is what we get:

Random PK, whether they are simple integer or GUID do consume substantially more CPU resources.

Using Sequential GUID vs Identity in SQL Server

Out-of-the box, SQL Server performs quite well and there is not much difference whether you’re using Sequential GUIDs or autoincrement PK.

There is however a surprising result: using Sequential GUIDs is actually slightly faster than using autoincrement!

There is obviously an explanation for this but I’m not sure what it is so please enlighten me :-)

CPU Consumption:

Using Random GUID vs Sequential GUID vs Random Autonumber in Access

So, what is the impact of choosing a Sequential GUID as opposed to letting Access create its own random GUIDs?

It’s clear that random GUIDs have a substantial performance impact: their randomness basically messes up indexing, resulting in the database engine having to do a lot more work to re-index the data on each insertion. The good thing is that this degradation is pretty logarithmic so while it degrades over time, the overall performance remains pretty decent. While GUIDs are larger than Random Integers (16 bytes vs 4 bytes) the actual performance of inserting records whose PK is a random integrer is actually worse than random GUID…

Provisional conclusions

Here we’ve check the baseline for our performance tests. In the next article, we’ll look exclusively at the performance of inserting data from a remote Access 2007 front end using our VBA code.

Having this baseline will allow us to check the performance overhead of using ODBC and letting Jet/ACE manage the dialogue with the SQL Server backend.

Feel free to leave your comments below, especially if you have any resources or information that would be useful.

Updates

16APR2009: added test of random autonumber as PK in Access.

13APR2009: Original Article

]]>15Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=5502014-08-22T13:03:19Z2009-04-12T11:25:56Z I’ve just lost 2 days going completely bananas over a performance issue that I could not explain.

I’ve got this Dell R300 rack server that runs Windows Server 2008 that I dedicate to running IIS and SQL Server 2008, mostly for development purposes.

In my previous blog entry, I was trying some benchmark to compare the performance of Access and SQL Server using INT and GUID and getting some strange results.

Here are the results I was getting from inserting large amounts of data in SQL Server:

Machine

Operating System

Test without Transaction

Test with Transaction

MacbookPro

Windows Server 2008 x64

324 ms

22 ms

Desktop

Windows XP

172 ms

47 ms

Server

Windows Server 2008 x64

8635 ms!!

27 ms

On the server, not using transactions makes the query run more than 8 seconds, at least an order of magnitude slower than it should!

I initially thought there was something wrong with my server setup but since I couldn’t find anything, I just spend the day re-installing the OS and SQL server, applying all patches and updates so the server is basically brand new, nothing else on the box, no other services, basically all the power is left for SQL Server…

Despair

When I saw the results for the first time after spending my Easter Sunday rebuilding the machine I felt dread and despair. The gods were being unfair, it had to be a hardware issue and it had to be related to either memory or hard disk, although I couldn’t understand really why but these were the only things that I could see have such an impact on performance.

I started to look in the hardware settings:

And then I noticed this in the Policies tab of the Disk Device Properties :

Moral of the story

If you are getting strange and inconsistent performance results from SQL Server, make sure you check that Enable advanced performance option. Even if you’re not getting strange results, you may not be aware of the issue, only that some operations may be much slower than they should.

Before taking your machine apart and re-installing everything on it, check your hardware settings, there may be options made available by the manufacturer or the OS that you’re not aware of…

Lesson learnt.

]]>0Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=4332013-09-12T03:22:02Z2009-04-01T03:39:45Z When you start building an Access application, it’s tempting to just think about today’s problem and not worry at all about the future. If your application is successful, people will want more out of it and, over time, you’ll be faced with the task of moving the back-end database to a more robust system like SQL Server.

Naming conventions

Access is pretty liberal about naming conventions and it will let you freely name your tables, columns indexes and queries. When these get moved to another database you’ll most probably be faced with having to rename them. In some cases, you could actually create subtle bugs because something that used to work fine in Access may be tolerated in the new database but be interpreted differently.

Do not use spaces or special characters in your data object names. Stick to characters in the range A through Z, 0 to 9 with maybe underscores _ somewhere in between (but not at the start or the end). Also try to respect casing wherever you reference this name (especially for databases like MySQL which are case-sensitive if the hosted on a Linux platform for instance). eg:Customer Order Lines (archive) should be CustomerOrderLines_Archive.Query for last Year's Turnover should be QueryLastYearTurnover. Index ID+OrderDate should become instead ID_OrderDate.

Do not use keywords that are reserved or might mean something else whether they are SQL keywords or functions names: A column called Date could be renamed PurchaseDate for instance. Similarly, OrderBy could be renamed SortBy or PurchaseBy instead, depending on the context of Order. Failing to do so may not generate errors but could result in weird and difficult to debug behaviour.

Do not prefix tables with Sys, USys, MSys or a tilde ~. Access has its own internal system tables starting with these prefixes and it’s best to stay away from these. When a table is deleted, Access will often keep it around temporarily and it will have a tilde as its prefix.

Do not prefix Queries with a tilde ~. Access use the tilde to prefix the hidden queries kept internally as recordsource for controls and forms.

Database design

Always use Primary keys. Always have a non-null primary key column in every table. All my tables have an autonumber column called ID. Using an automatically generated column ID guarantees that each record in a table can be uniquely identified. It’s a painless way to ensure a minimum level of data integrity.

Do not use complex multivalue columns. Access 2007 introduced complex columns that can record multiple values. They are in fact fields that return whole recordset objects instead of simple scalar values. Of course, this being an Access 2007 only feature, it’s not compatible with any other database. Just don’t use it, however tempting and convenient it might be. Instead use a table to record Many-To-Many relationships between 2 tables or use a simple lookup to record lists of choices in a text field itself if you’re only dealing with a very limited range of multivalues that do not change.

Do not use the Hyperlink data type. Another Access exclusive that isn’t available in other databases.

Be careful about field lookups. When you create Table columns, Access allows you to define lookup values from other tables or lists of values. If you manually input a list of values to be presented to the user, these won’t get transferred when upsizing to SQL Server. To avoid having to maintain these lookup lists all over your app, you could create small tables for them and use them as lookup instead; that way you only need to maintain a single list of lookup values.

Be careful about your dates. Access date range is much larger than SQL Server. This has 2 side-effects: 1) if your software has to deal with dates outside the range, you’ll end-up with errors. 2) if your users are entering dates manually, they could have made mistakes when entering the year (like 09 instead of 2009). Ensure that user-entered dates are valid for your application.

VBA

While most of your code will work fine, there are a few traps that will bomb your application or result in weird errors:

Always explicitly specify options when opening recordsets or executing SQL. With SQL Server, the dbSeeChange is mandatory whenever you open a recordset for update. I recommend using dbFailOnError as well as it will ensure that the changes are rolled back if an error occurs.

Get the new autonumbered ID after updating the record. In Access, autonumbered fields are set as soon as the record is added even if it hasn’t been saved yet. That doesn’t work for SQL Server as autonumbered IDs are only visible after the records have been saved.

' Works for Access tables only
' We can get the new autonumber ID as soon as the record is inserted
rs.AddNew
mynewid = rs!ID
...
rs.Update
' Works for ODBC and Access tables alike
' We get the new autonumber ID after the record has been updated
rs.AddNew
...
rs.Update
rs.Move 0, rs.LastModified
mynewid = rs!ID

Never rely on the type of your primary key. This is more of a recommendation but if you use an autonumbered ID as your primary key, don’t rely in your code or you queries on the fact that it is a long integer. This can become important if you ever need to upsize to a replicated database and need to transform your number IDs into GUID. Just use a Variant instead.

Parting thoughts

These simple rules will not solve all your problems but they will certainly reduce the number of issues you’ll be faced with when upsizing you Access application. Using a tool like SSMA to upsize will then be fairly painless.

If you have other recommendations, please don’t hesitate to leave them in the comments, I’ll regularly update this article to included them.

]]>1Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=2852013-09-12T03:27:56Z2009-03-17T07:00:36Z I’m currently researching ways to move my main MS Access application from a simple local network client/backend setup to a global, multiple remote sites configuration using SQL Server.

One of the challenges is to upsize the current MS Access accdb backend database to SQL Server 2008. If you try it from Access itself using the Upsizing Wizard, you may end up getting this error message:

The Upsizing Wizard only works with Microsoft SQL Server (Versions 6.50 SP5 or higher). Please log in to a SQL Server data source.

After spending some time fiddling around with SQL Server settings I couldn’t understand why I was still getting this error. Turns out that the upsizing wizard is apparently sensitive to the version of SQL Server you’re using and it doesn’t consider SQL Server v10 (2008) as being later than v6.50…

This issue is in fact a blessing. Microsoft provides a migration tool for upsizing MS Access database to SQL Server 2008 that’s orders of magnitude better than anything the basic wizard can do: the SQL Migration Assistant for Access, or SSMA.

SSMA lets you take a bunch of Access databases and move the tables and queries you choose to SQL Server, automatically linking them in your original database if you want. It’s not just a one-off thing either: SSMA keeps track of the objects that where transferred and allows you to synchronise both schema and data as often as you need.

So here you are: do not use the basic MS Access Upsizing Wizard, download and use SSMA instead.

Strange COM Error

While SSMA works perfectly fine on my Windows 2008 x64 laptop, on my main Windows XP desktop it throws an exception when trying to load an Access database:

It was a COM error saying that the library for DAO couldn’t be loaded.

I couldn’t find any relevant information on the web. After a while, I had a look at the DAO driver inC:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll and I noticed that the filename was in blue: that reminded me that I had set compression on the filesystem.

I disabled compression for the file and, magically, SSMA worked again…

Moral of the story: be careful about compressing your filesystem, some registered libraries and system files may work in unpredictable ways…

Updates

23FEB2012: Added link to SSMA Team blog and updated download link to current version (5.2)

17MAR2009: Added section on the strange COM error.

14MAR2009: Added links to Tony’s Access MVP website.

05JAN2009: Original publication.

]]>29Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=4352010-02-12T00:04:16Z2009-03-11T03:47:43ZI like buying technical books. Unfortunately, here in Hong Kong, we have less choice: there are not that many technical bookstores that cater for English-language books and the selection is rather limited.

So whenever a book isn’t available here, I buy it online as a PDF. It’s cheaper, saves postage and I can keep them handy on my desktop computer at work and my laptop.

I love Safari and being able to access such a large library of books online in such a flexible way is great, but if you’re not in the US, the experience is not always that pleasant, with the site sometimes becoming a bit too slow for comfort.

The publishers I regularly buy ebooks from are O’Reilly, when they make the PDF available, and Apress.

O’Reilly’s PDF may have your subscribtion details embedded at the bottom of each page. It’s perfectly reasonable and doesn’t ruin the experience: I can still search, copy snippets to the clipboard and print the book if I need to keep an annotated copy.

Apress encrypt the PDF with your account email. Again, that’s fine by me, they don’t prevent me from using the book and it’s not a great annoyance to have to type the password to unlock the book.

Now comes Wrox (Wiley): they publish fine books and even have some available as ebooks. The biggest issue I have though is that they assume that people who buy their books are all potential criminals:

So the net advantage of buying an eBook from Wrox (Wiley) is: your guess is as good as mine.

Yeah, you can buy each chapter as a PDF: great value, at US$4.99 per chapter, the book is costing you nearly US$100. You can get the fully printed version for half the cost… Still, I’ll concede that being able to download a particular chapter can be useful.

The bottom line is: if your book gets printed, it’s going to be pirated and distributed for free in ebook format within a couple of weeks of being published. While thinking they are protecting their copyright, Wiley is in fact punishing people who buy their books.

I’ll stick with Apress and O’Reilly I think. At least I don’t feel treated like a thief.

12FEB2010 Update: Wrox is now trying DRM-free PDF ebooks. We can only applaud this decision. While the offering is still limited at the moment, most or all of their books will eventually be available DRM-free.

]]>3Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=3362013-09-12T03:44:51Z2009-02-04T07:22:24Z After suffering broadband trouble for the past 9 months, including interruptions that lasted a few days, I decided to get an additional line installed by a different ISP. I could have bought one of these multi-WAN devices but decided against it for a couple of reasons: I like a challenge and I wanted to achieve a particular setup that I wasn’t sure could be answered by off-the-shelf products (for a reasonable price that is).

This long article is fairly detailed but if your setup is similar it should be enough to get you going quickly.

The basic setup

Notable things

CYBR, a standard DSL line with a fixed IP 111.99.88.77 allocated through PPPoE.

HKBN, a standard 100Mbps line with a fixed IP 30.40.50.62.

The network is split into different zones:

the Internet zone, connected to our Firewall through interfaces eth0 (ppp0) and eth1.

a Firewall zone, delimited by the firewall system itself

a DMZ zone connected through interface eth2 for the servers we want to make visible from the Internet. The DMZ has its own private subnet delimited by 192.168.254.0/255.255.255.0.

a LAN zone connected through interface eth3 so local computers can access the Internet and be protected from it. The DMZ has its own private subnet delimited by 192.168.0.0/255.255.255.0.

Objectives

What we want from our setup:

our firewall protects our DMZ and LAN from unwanted access.

our win server can host websites or other services.

our linux server can handle receiving and sending email or other services.

our firewall can handle incoming traffic from either ISP.

our firewall can load-balance local outgoing traffic across both ISP.

If one line fails, incoming traffic switches to the other line.

If one line fails, outgoing traffic switches to the other line.

Eventually, we want both the linux and win servers to be able to host different websites and we want the firewall to send incoming requests to the right server.

In this first article, I’ll present the setup for items 1-5. The remaining topics will be the subject of subsequent articles of their own.

Technologies

The firewall is our primary subject. What is being discussed here is pretty much distribution-independent and should work on all flavours of Linux.

OS on the firewall system

I chose CentOS on the firewall. Being an almost byte-for-byte identical copy of RedHat Enterprise Linux, all configuration will be identical on RedHat and its derivatives such as Fedora.

Firewall software, first try

When my firewall needs are simpler, I use the Stronger IP Firewall Ruleset from the Linux IP Masquerade HOWTO. I started to modify the script to adapt it to my new Multi-ISP setup but things got complicated once I needed to debug routing tables. I got it 80% of the way but tracing network connections and packet routing is complicated and time-consuming. After a couple of days of peering into log files and wireshark capture screens, I gave up manual configuration and decided to go with something else.

Firewall software, final

The product I chose in the end is shorewall: it’s a very flexible firewall system that create the necessary iptable rules and configure most of the routing needs to properly handle complex network setup. Shorewall is Open Source, very stable, has been out for a long time, is actively maintained and has lots of excellent documentation and examples.

Things to know

Before we get into the meat of the article, you should brush up on the following topics:

You have some knowledge of Linux system administration, know how to configure network connections, know how to enable/disable/stop/start services, able to edit config files.

Networking: you should know what a netmask is, what a gateway is, what a subnet is and have a passing understanding of IP classes, IP notation, what ports are for, what’s the difference between the tcp, udp, icmp protocols, what Dynamic Port Forwarding (DNAT) is, what Network Address Translation (NAT) is, what masquerading means.

Some basic understanding of DNS and local host name resolving (using host.conf and resolv.conf)

Some basic knowledge of what routing is for and how it works.

Some knowledge of how the linux kernel handles network packets (NetFilter, basics of iptables).

You don’t need to be a specialist in any of these areas but any knowledge helps. I’m far from being well versed into Netfilter and routing, it’s not often that I have to deal directly with these topics, but brushing up on these topics helped.

Things to read

Shorewall has very extensive documentation. So much so that it can be a bit daunting, not knowing where to start. I found the following documents helpful to get me started:

Disable firewall

Disable the current firewall, for instance using the system-config-securitylevel helper tool. Be careful if you’re directly connected to the Internet, you will be left without protection! You can actually wait until shorewall is properly configured to disable the firewall.

Shorewall configuration

Shorewall uses a set of simple configuration files, all located under /etc/shorewall/. For exact detail of each configuration files, have a look at the list of man pages.

Zones

zones are probably the simplest configuration file. Details in the zones man page. Here we just name the various zones we want our firewall to handle:

Note that for our net zone, we list the 2 interfaces connected to our ISPs. If you’re using PPPoE to connect, don;t use the interface name eth0 but use ppp0 instead.

Policy

The policy file tells shorewall which default actions should be taken when traffic is moving from one zone to another. These default actions are taken if no other special action was specified in other configuration files. View the policy file as a list of default actions for the firewall. Details about this configuration file as in its man page.

Traffic from one zone to another needs to be explicitely ACCEPTed, REJECTed or DROPped. For instance, loc net ACCEPT means that we allow all traffic from our local LAN to the Internet, while net all DROP means we don’t allow incoming traffic from the internet to anyone (remember this is the default action, in most cases we will override this for specific types of traffic in the rules file). When we set the default action to DROP, we can tell shorewall to keep a trace of the details in the /var/log/messages log.

Providers

The providers file is generally only used in a multi-ISP environment. Here we define how we want to mark packets originating from one ISP with a unique ID so we can tell the kernel to route these packets to the right interface. Not doing this would get packets received from one interface to be routed to the default gateway instead. The details of this configuration file are explained in the providers man page for it.

Note that the DUPLICATE columns tells shorewall that it should make a copy of the main default routing table for this particular routing table (called CYBR or HKBN depending on which ISP we refer to). Packets are marked with number 0x1 or 0x2 so we can distinguish them during their travel through the system. For PPPoE connections, don’t specify a GATEWAY since it’s most likely that your ISP didn’t give you one.

The most interesting part of this file are the OPTIONS: track means that we want the packets to be tracked as they travel through the system; balance tells the kernel that we want traffic coming out to be spread over both interfaces. Additionally, we want HKBN to receive more or less 5 times more traffic than CYBR (note that this has no effect on reply packets).

The COPY columns will ensure that the routing tables created for CYBR and HKBN are copied for each internal interface, so our eth2 and eth3 interfaces know how to route packets to the right ISP.

Route Rules

For our purpsose, the route_rules file only describes how traffic should be routed through one or the other ISP we set up in /etc/shorewall/providers. Details are in the route_rules file man page.

Here we simply say that all traffic through the CYBR table should be sent to ppp0. The PRIORITY is an ordering number that tell shorewall to consider this routing rule before it marks the packets. Since we know the packets originated from ppp0 or eth1 we don’t really need to mark them.

Masq

The masq file will contain the masquerading rules for our private interfaces: in essence, we want traffic from the local LAN and DMZ to be hidden behind our limited number of external IPs. See the masq manpage for all the details.

The first part ensures that the traffic coming out of our public interfaces but originating from the other is actually rewritten as originating from the right IP for the interface. This ensures that packets leaving eth1 for instance don’t come out with the wrong source address of the other interface. The second part of the ensures that packets from our LAN or DMZ leaving either public interfaces are doing so with the right IP address, so traffic from my desktop going through ppp0 for instance, will have its source address as 100.90.80.70.

Rules

This is the main file where we tell shorewall our basic configuration and how we want packets to be handled in the general case. The /etc/shorewall/rules file contains the specific instructions on where to direct traffic that will override the default actions defined in the /etc/shorewall/policy file.

#####################################################################
#ACTION SOURCE DEST PROTO
#
SECTION NEW
# Drop and log packets that come from the outside but pretend
# to have a local address
DROP:info net:192.168.0.0/24 all
DROP:info net:192.168.254.0/24 all
# Redirect incoming traffic to the correct server for WWW and email
DNAT all dmz:192.168.254.20 tcp www
DNAT all dmz:192.168.254.10 tcp 110
DNAT all dmz:192.168.254.10 tcp 143
DNAT all dmz:192.168.254.10 tcp 25

In its most basic form, what we’ve just defined here is that we want all traffic from anywhere destined for port 80 (www) to be sent to our win server. All mail traffic, POP3 (port 110), IMAP (port 143) and SMTP (port 25) is to be redirected to our linux server in the DMZ.

There are a few more useful rules that we can include, for instance, I want to be able to access my servers through either ISPs from home (IP 123.45.67.89) and disallow everyone else from accessing it.

When I SSH to 30.40.50.62 or 100.90.80.70, on the normal port 22, I will access the firewall. Now if I SSH to the non-standard port 2222, I will instead access the linux server. Ports 5901 are for remoting through VNC on the linux machine, and port 3389 will be used for Remote Desktop connections to the win server.

To make sure my machines are up and running, I like to be able to ping them:

Note that ping will only work between the LAN and the DMZ and pinging my firewall from the Internet will result in the requests being silently dropped. I usually prefer that configuration as it makes discovering the servers by random bots slightly less likely.

There are lots of other cool things we can do with forwarding but that will do for now.

shorewall.conf

The last file we’re going to look at is the main configuration file for shorewall. See details about each option from the man page for shorewall.conf.

Most options are OK by default. The only ones that I have had to change are:

The first option tells shorewall that we want it to start automatically when the system boots. That’s not enough though, so make sure that the service will be started:

# chkconfig shorewall --levels 235 on

Installing our firewall rules

Shorewall configuration files need to be compiled without error before the firewall is actually loaded by shorewall. The command:

# shorewall restart

will stop and recompile the current configuration. If there are any errors, the current firewall rules will be unchanged. There are lots of other commands that can be issued. Check the man page for a complete list.

If you use PPPoE, you will want the firewall to be restarted every time the line reconnects. The simplest way is to create a file /etc/ppp/if-up.local with only a single line:

shorewall restart

DNS

There is one remaining issue with our firewall: if a user on the LAN attempts to access the web server by its name the request will probably fail. Same for accessing our mail server: we can configure our desktop to connect to 192.168.254.10 to get and send emails, but on the laptop we would usually use something like pop.acme.com instead so we can read our emails from outside the office.

Similarly, trying to access www.acme.com hosted on the win server from the linux server will fail.

One solution is to route traffic through the firewall but that’s actually fairly complicated to setup properly. The shorewall FAQ 2 discourages this and instead recommends the use of split-DNS: it’s very easy to setup and it works like a charm.

dnsmasq

Just install dnsmasq on the firewall. There are ready-made packages available for it and a simple yum install dsnmasq should suffice.

Dnsmasq provides a simple DNS forwarding and DHCP service. I had already configured dhcpd -which is already fairly simple to configure- on my firewall so I won’t need DHCP from dnsmasq but you can easily set it up if you want.

On the DNS side, dnsmasq can be told to first try to resolve hostnames by looking at the standard /etc/hosts file and then query the DNS servers defined in /etc/resolv.conf if necessary.

This simple trick means that we can:

Keep our normal DNS service pointing to say 100.90.80.70 for www.acme.com so that people on the Internet will properly resolve their web requests to our win server.

Add an entry in the firewall’s hosts file to point local clients to 192.168.254.20 instead.

DNS resolution

There may be one last issue with DNS: in your /etc/resolv.conf you will have listed the DNS servers of one or both of your ISPs. The problem is that some ISPs don’t allow access to their name servers from a network different than theirs.

The result is that each time any of the systems issues a DNS request it may fail and need to be sent to the next server instead, which may also fail and introduce delays in accessing named resources on the Internet.

One easy way out is to not use the ISPs DNS servers but instead only list the free OpenDNS name servers in your resolv.conf:

search acme.com
nameserver 208.67.222.222
nameserver 208.67.220.220

Then make sure that you disable DNS in your /etc/sysconfig/network-config/ifcfg-XXX configuration file for your PPPoE connection:

PEERDNS=no

Failure to do so will result in your /etc/resolv.conf file being rewritten with the DNS servers of one of your ISP every time you reconnect to them.

DHCP configuration

If you use dhcpd for local users, then you will need to make sure that its DNS server is set to the firewall’s:

On your local machines that use DHCP, make sure to renew your IP. All other machines should be configured to use 192.168.0.1 as their unique DNS server and the machines in the DMZ should have their DNS set to 192.168.254.1.

Unless you reboot, don’t forget and flush the local DNS cache of each machine: On Windows, from the command line:

C:\> ipconfig /flushdns

On Mac, from the terminal:

bash-x.xxx$ dnscacheutil -flushcache

Initial conclusions

I believe this type of firewall setup is fairly common and I hope that the -rather long- article helped you get your own setup in place. In the -much shorter- follow-up articles, we’ll make our system as redundant as possible so our web and email services stay online even when one of the broadband connections fails.

In the meantime, don’t hesitate to leave your comments and corrections below.

]]>20Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=3112009-01-20T14:45:51Z2009-01-19T14:54:09Z Over the years I’ve struggled to keep my folder data synchronised between my various desktop and laptops.

Here I present the tools I’ve tried and what I’ve finally settled on as possibly the ultimate answer to the problem of synchronising files and folders across multiple computers:

rsync

I’ve tried rsync, which is a great Open Source tool to securely synchronise data either one-way or both-ways. It’s very efficient with bandwidth as it only transfer blocks of data that have actually changed in a file instead of the whole file. It can tunnel traffic across SSH and I’ve got a few cronjobs set up between various servers to back-up files daily.

It’s only weaknesses are that:

Every time it runs, it needs to inspect all files on both sides to determine the changes, which is quite an expensive operation.

Setting up synchronisation between multiple copies of the data can be tricky: you need to sync your computers in pairs multiple times, which quickly becomes expensive and risky if you have the same copy across multiple computers.

It doesn’t necessarily detect that files are in use at the time of the sync, which could corrupt them.

unison

It a folder synchronisation tool whose specific purpose is to address some of the shortcomings of rsync when synchronising folders between computers. It’s also a cross-platform Open Source tool that works on Linux, OS/X, Windows, etc.

Unison uses the efficient file transfer capabilities of rsync but it is better at detecting conflicts and it will give you a chance to decide which copy you want when a conflict is detected.

The issue though is that, like rsync, it needs to inspect all files to detect changes which prevents it from detecting and propagating updates as they happen.

The biggest issue with these synchronisation tools is that they tend to increase the risk of conflict because changes are only detected infrequently.

WinSCP

WinSCP Is an Open Source Windows GUI FTP utility that also allows you to synchonise folders between a local copy and a remote one on the FTP server.

It has conflict resolution and allows you to decide which copy to keep.

It’s great for what it does and allows you to keep a repository of your data in sync with your local copies but here again, WinSCP needs to go through each file to detect the differences and you need to sync manually each computer against the server, which is cumbersome and time consuming.

General Backup tools

There are lot more tools that fall into that category of backup utilities: they all keep a copy of your current data in an archive, on a separate disk or online. Some are great in that they allow you to access that data on the web (I use the excellent JungleDisk myself) but file synchronisation is not their purpose.

Now for some Captain Obvious recommendation: remember that file synchronisation is not a backup plan: you must have a separate process to keep read-only copies of your important data. File synchronisation will update and delete files you modify across all your machines, clearly not what you want if you need to be able to recover them!

Revision Control Systems

Revision control software like cvs, subversion, git, etc are generally used to keep track of changes of source code files; however, they have also been used successfully to keep multiple copies of the same data in sync. It’s actually exactly what I use for all my source code and associated files: I have a subversion server and I check-out copies of my software project folders on various computers.

After making changes on one computer, I commit the changes back to the server and update these changes on all other computers manually.

While great at keeping track of each version of your files and ideally suited to pure text documents like source code, using revision control systems have drawbacks that make them cumbersome for general data synchronisation:

you need to manually commit and update your local copies against the server.

not all of them are well suited to deal with binary files

when they work with binary files, they just copy the whole file when it changed, which is wasteful and inefficient.

Revision Control System are great for synchronising source code and configuration files but using them beyond that is rather cumbersome.

Complex setup

All of the above solutions also have a major drawback: getting them to work across the Internet requires complex setup involving firewall configurations, security logins, exchange of public encryption keys in some cases, etc.

All these are workable but don’t make for friendly and piece-of-mind setup.

What we want from data synchronisation

I don’t know about you but what I’m looking for in a synchronisation tool is pretty straightforward:

Being able to point to a folder on one computer and make it synchronise across one or multiple computers.

Detect and update the changed files transparently in the background without my intervention, as the changes happen.

Be smart about conflict detection and only ask me to make a decision if the case isn’t obvious to resolve.

Live Mesh folders

Enters Microsoft Live Mesh Folders, now in beta and available to the public. Live Mesh is meant to be Microsoft answer’s to synchronising information (note, I’m not saying data here) across computers, devices and the Internet. While Live Mesh wants to be something a lot bigger than just synchronising folders, let’s just concentrate on that aspect of it.

Installing Live Mesh is pretty easy: you will need a Windows Live account to log-in but once this is done, it’s a small download and a short installation.

Once you’ve added your computer to your “Mesh” and are logged in you are ready to use Live Mesh:

You decide how the data is synchronised for each computer participating in your Mesh: you’re in charge of what gets copied where, so it’s easy to make large folders pair between say your laptop and work desktop and not your online Live Desktop (which has a 5GB limit) or your computer at home. You’re in control.

Files are automatically synchronised as they change across all computers that share the particular folder you’re working in. If the file is currently used, it won’t be synced before it is closed.

If the other computers are not available, the sync will automatically happen as they are up again.

There is no firewall setup: each computer knows how to contact the others and automatically -and uses- the appropriate network: transfers are local if the computers are on the same LAN or done across the Internet otherwise. All that without user intervention at all.

Whenever possible, data is exchanged in a P2P fashion where each device gets data from all the other devices it can see, making transfers quite efficient.

File transfers are encrypted so they should be pretty safe even when using unsafe public connections.

If you don’t want to allow sync, say you’re on a low-bandwidth dialup, you can work offline.

The Mesh Operating Environment (MOE) is pretty efficient at detecting changes to files. Unlike other systems, in most cases it doesn’t need to scan all files to find out which ones have been updated or deleted.

Some drawbacks

It’s not a final product, so there are some quirks and not all expected functionalities are there yet.

The Mesh Operating Environment (MOE) services can be pretty resource hungry, although, in fairness, it’s not too bad except that it slows down your computer’s responsiveness while it loads at boot time.

You can’t define patterns of files to exclude in your folder hierarchy. That can be a bit annoying if the software you use is often creating large backup files automatically (like CorelDraw does) or if there are sub folders you don’t need to take everywhere.

The initial sync process can take a long time if you have lots of files. A solution if you have large folders to sync is to copy them first manually on each computer and then force Live Mesh to use these specific folders: the folders will be merged together and the initial sync process will be a lot faster as very little data needs to be exchanged between computers.

Bear in mind that Live Mesh is currently early beta and that most of these drawback will surely be addressed in the next months.

Conclusion

I currently have more than 18GB representing about 20,000 files synchronised between 3 computers (work desktop, laptop and home desktop) using Live Mesh.

While not 100% there, Live Mesh Folder synchronisation is really close to the real thing: it’s transparent, efficient, easy to use and it just works as you would expect.

Now that Microsoft has released the Sync Framework to developers, I’m sure that other products will come on the market to further enhance data synchronisation in a more capable way. In the meantime, Live Mesh has answered my needs so far.

]]>3Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=3062009-02-03T02:29:14Z2009-01-08T02:57:08ZI made a mistake the other day: I wanted to delete the partition on an external drive and in my haste ended up deleting the partition of a local hard drive instead…

The good thing is when you delete a partition using the Windows Disk Management console it doesn’t actually delete your files, only the partition header.

With NTFS files systems, there is a backup at the end of the partition. The problem is how do you recover it?

I first looked at the instructions from Microsoft knowledge base article kb245725, downloaded the low-level sector editor Dskprobe but was getting no-where with it.

Searching google brings you to the usual list of recovery software that you can’t be sure will actually do the job until you fork $$ for them. I’ve got nothing against paying for software but I’ve been bitten by false promises before.

My search ended up with TestDisk an OpenSource utility to manipulate and recover partitions that works on almost all platforms. The user interface is DOS only, so it’s not pretty, not point-and-click user friendly but it has a fair amount of options and after fiddling around with it for 10 minutes, I was able to simply recover the backup boot sector and tada! all my files were back!

So, some recommendations when recovering lost partitions:

Don’t panic! If you only deleted the partition (whichever type), chances are you’re likely to recover it or at least salvage the files.

Obviously, be careful not to write anything over them, like recreating partitions and a file system.

If you use a utility like TestDisk, don’t blindly follow the on-screen instructions. At first, it was telling me that I had 2 Linux partitions on the device (which used to be true) but it did not see the NTFS one. Then it thought I had a FAT partition only until I switched to the advanced options and inspected the boot partition. Just know enough about file systems to know what you’re looking for.

Low-level tools are not for everyone, so if you’re not comfortable using them, don’t tempt your luck and try a paid-for recovery tool with an easier interface.

If you use TestDisk and you manage to recover your files, don’t forget to donate to encourage Christophe GRENIER, the author.

]]>3Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=2452013-09-12T03:49:44Z2008-11-05T20:06:27Z(Updated Wednesday 30JAN2009.) Developing applications that manipulate OpenOffice documents has always been rather tricky; not very difficult, but just tricky to get right.

With OpenOffice 3, things got trickier and applications that used to work will now fail. I’ve just spend nearly a day trying to get a mail-merge application I built to work again with the new version of OO.

Changes and limitations

Getting the CLI assemblies in your project

OpenOffice used to ship with a bunch of CLI DLLs for manipulating Open Documents from .Net. With OpenOffice version 3, these DLLs are directly installed in the GAC and not available in the Program installation folder any longer, making them unavailable when you need to add them as references to your Visual Studio project.

The trick is to extract the DLLs from the installation CAB, then reference those and make sure you exclude them in your setup file so only the latest version installed on the user’s computer’s GAC will get used.

Open the installation folder where the installation files are extracted (usually left on your desktop under a folder like OpenOffice.org 3.0 (en-US) Installation Files during the OO installation process.

Open the openofficeorg1.cab file (using a utility like 7zip if necessary) and extract the files matching cli_*.dl.

Add the ones you need to your VS Project’s references and make sure the properties of these references have their Copy Local and Specific Version properties set to false.

If you have a setup project that added these references to the list of files, right-click each of them and select Exclude to make sure they won’t be packaged.

The reason for not deploying the DLLs is that they are very specific to a given OpenOffice version and the latest assemblies relevant to the user’s OpenOffice installation will already be deployed to the target machine’s GAC. When .Net loads assemblies, if they are not included in the path of the application being launched, they will be loaded from the GAC if they can be found there.

Path and environment setup

Before being able to call OpenOffice from your application you now must set your application’s environment variables to the correct paths so the assemblies can find the actual OpenOffice library and program files.

Basically, you need to add to your PATH the path to the folder where the UNO java libraries reside. You also need to add a UNO_PATH environment variable that points to the program folder of OpenOffice. Basically, before any call to OpenOffice functions you must:

Create a UNO_PATH variable set to C:\Program Files\OpenOffice.org 3\program.

Because there is no guarantee that these paths will not change or are valid for all systems you must get them from specific keys located in the Registry:

PATH is appended with the vaue of HKLM\SOFTWARE\OpenOffice.org\Layers\URE\1\UREINSTALLLOCATION to which you must append the bin directory.

UNO_PATH is set to the content of the HKLM\SOFTWARE\OpenOffice.org\UNO\InstallPath key.

See the C# and VB.Net code below for working examples.

Special Considerations for x64 systems

My development machine runs Windows Server 2008 x64 and I’ve ran into some specific issues that you’re likely to encounter when deploying to a 64 bits OS.

OpenOffice is 32 bits only That means that your .Net project must be set to target x86 systems only: Open your Solution’s Configuration Manager and under Active solution platform click New… then:

Make sure you repeat this for both the Debug and Release configurations.

Registry keys are elsewhere 32 bit applications see their registry keys normally expected under:HKEY_LOCAL_MACHINE\Software moved to:HKEY_LOCAL_MACHINE\Software\Wow6432Node instead. This of course creates issues when you’re trying to read a registry key that’s not where it should be…

The Code

The code below will allow you to correctly connect to OpenOffice 3 under 32 or 64 bit systems. It reads the registry to find the proper paths and appends the PATH and creates the UNO_PATH environment variables expected by the the bootstrapper to find the OpenOffice program and libraries.

The code is built upon information and a test program made available by Marten Feldtmann on his blog (more information, in English, is available on OOoForum ).

Please let me know if this works for you or if you have any corrections.

]]>31Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=2362011-12-10T04:42:47Z2008-10-28T13:31:53ZThere are times when the coexistence of 64 and 32 bit code on the same machine can cause all sorts of seemingly strange issues. One of them just occurred to me while trying to run the ASPx demos from Developer Express, my main provider of .Net components (the best supplier I’ve ever been able to find). I was getting the following error:

The ‘Microsoft.Jet.OLEDB.4.0’ provider is not registered on the local machine:

It may look otherwise, but this error is generally due to either of two thing:

you don’t have Office 2007/2010 Jet drivers installed

or you are running a 32 bit application in a default x64 environment.

The first issue is easy to solve, just download the Access 2010 Database Engine from Microsoft (works with Access 2007 databases as well).

References

Updates

10DEC2011: Updated driver link to use the Access 2010 engine.

03APR2010: Added instructions for Windows 7

12FEB2009: Added reference to Scott’s article.

28OCT2008: Original version

]]>96Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=2072008-10-18T19:21:18Z2008-10-18T19:00:29ZI’ve been using the MacBook Pro I introduced in my previous blog entry for a few weeks now. Between love and frustration I hang… Here is a review of our relationship so far.

The Great

Hardware delight

Whether running OS/X or Windows 2008 I’ve got no major complaint about the performance of the machine. It’s fast, stable (except sometimes it’s not waking up from sleep or it does but the screen remains black). The screen is nice and vibrant, I just love the magnetic power connector and the small size of the power adapter. I have a few complaints though, see below.

OSX battery Power usage

For such a large and powerful laptop I’m pleasantly surprised with the duration of the battery under OSX: I’ve been able to watch videos for 3h, full screen, without trouble and overheating (although I would lower the screen brightness to reduce consumption). I haven’t had such luck under Windows 2008 where I’ve been struggling to find the right power settings balance, but remember that’s a server OS and it’s not really meant to be run on a laptop.

The Ugly

The mouse

You wonder why Apple, in all its hardware expertise could design the mighty-mouse with a single big button that can still do right-clicking but can’t give us the same thing with the enormous single-button of the trackpad. Now the new models -just released this week- have done away with the button entirely, which may be just as well although I’m curious about how well the drivers will work under Windows.

Mouse acceleration in OSX is pretty frustrating to me. When you’ve got a large screen, you’re endlessly shuffling the mouse to get that pointer in the right place. It feels slow, inaccurate and is extremely irritating after a while. The problem is even worse when you’re working in OSX under VMware Fusion: while it might still be usable under OSX, the difference is really severe and unnatural in Windows. This does not happen under bootcamp though where mouse acceleration behaves as you would expect (for windows). I’ve tried a number of utilities (iMouse , SteerMouse and others) but none gave me what I needed.

The keyboard

The keyboard feels great to type on, with a nice spring and softness. There are a few issues though: Why is the Return key so small compared to the right Shift? The rule is that the more a key is used, the bigger it is, yet the Enter is rather small, it’s the same size as the caps lock which serves almost no useful purpose in comparison. The arrow keys are also minuscule, another probable example of Apple choosing form over function. The lack of delete key forces you to type both the fn + backspace keys instead, which is an unnecessary pain, it’s not like understanding the difference between delete and backspace is that confusing to people using a complex machine like a computer.

The lid

I love the way the hooks for the lid come out just when you close it. It makes for a neat screen without protruding bits of metal or plastic. My main issue with the lid is with its limited opening angle: if you’re just a little tall and you place your laptop on your lap then there is no way to open the screen enough to have it properly face you. This is also an issue if you place your Macbook Pro on a cooling pad or a riser that’ll put the laptop too vertical (for instance to free some space around it when using an external keyboard): you just can’t use these devices.

The sound

That one really makes me mad: the MacBook Pro has audio issues that you won’t even find in a US$15 MP3 player and it’s totally unacceptable. When idle, I can hear hissing sounds that vary in power and frequency if I slide the volume control; when playing music, there is a lot of noise and “cutting” sounds between songs. These are not noticeable when using the integrated speakers but,they become really annoying once you use earphones. I am sorely disappointed with sound quality to say the least.

Electronic noises

On top of the annoying sounds from the sound chipset, the LCD inverter also makes a hissing sound that increases in volume when I lower the LCD brightness… Coupled with that, the processor also makes a hissing noise when it gets into its C4 power saving state… The noise is probably not high enough to get the laptop fixed but it might be great as a mosquito repellent.

Boot ‘song’

Apple knows best and they know that your only aim in life is to become a poster boy for the brand. When booting/rebooting your mac, it likes to play its welcome song that says “hey, over here, I’m a mac and I’m telling the world I’m booting up. Everyone listen how awesome I am!”. The perverse thing is that even if you have earphones plugged in (as in: you don’t want to disturb people around you) the boot song will be played on the speakers… Of course, there is no option anywhere to disable it. Apple knows best. After much trials, I found that booting under OSX, lowering the volume to zero, then rebooting under windows and changing the volume there would be OK: no more booting song -at least for now- and I can still change the volume in OSX and Windows.

Conclusion

Would I buy a Mac again knowing what I know now? Mmmm, probably not. I find the annoyances a bit too much relative to my expectations and my usage scenario. To be fair, it’s not all bad and most of the time I’m happy with my mac but I find myself trying to avoid the things that infuriate me and it’s not really what I want from a laptop; it’s supposed to liberate me and give me the freedom I need to get things done, not get in the way. Re-adapting to a strangely layout keyboard, having to deal with Apple’s brand awareness arrogance, battling with stuff that you just normally take for granted, all this is a bit too much of a price to pay for what is essentially for me a Windows development machine. I prefer to waste my time actually getting things done rather than searching forums on how to keep Windows and OSX time in sync or bring back my machine for repair if a CD stays stuck in the CD Drive.

So, let’s say that our relationship is more ambivalent than needed to be.

Another mouse utility called iMouse, for performing right-clicking in Windows this time.

]]>2Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=1492009-01-07T08:43:55Z2008-08-31T06:04:39ZMy trusty old gigantic Sony Vaio is about 4 years old. It served me well and still works but it’s about to become my main development machine for the next couple of months and I can’t afford to have it die on me during that time. It was time to get something as gigantic and more up-to-date in terms of technology.

I use VMware on my main desktop to keep multiple OS setups that match typical configurations of my customer’s machines. This allows me to test my software before deployment and make sure everything works as expected. It saved me many times from strange bugs and I would consider these final tests to be a mandatory step before deployment. My old trusty vaio would be hard pressed to run any of these without slowing down to a crawl.

I looked at some possible replacements. Initially I checked Lenovo’s offerings but they don’t seem to offer anything in large screen size (WUXGA 1920×1200) (Note, actually, they have, but not really for me). Dito for Dell, not counting their humongous XPS M1730 luggable gaming machine that was wayyy over the top as a work computer, not to mention probably heavier than its volume in pure gold.

On a hint from a friend I checked out Apple’s online store and saw they had a nice Macbook Pro configuration. I went to check it out in the retail store close to my office and they had that exact specification in stock, so, in what must have been the highest rated expense/time-to-think ratio of any decision I ever took, well, I bought it…

The spec, some bragging rights:

Macbook Pro 17″

Core Duo T9500 2.6GHz processor

nVidia 8600M GT 512MB graphics card

200GB 7200rpm drive

Kingston 4GB DDR2 667MHz RAM

Hi Resolution 17″ 1920×1200 glossy screen

It’s a very nice machine, Apple knows how to make nice hardware, there is no question there. OSX has some cool features, some of them still a bit foreign to me and some minor annoyances are creeping up, like Thunderbird’s not picking up my system date and time settings and displaying the date in the wrong format (a pet peeve of me), probably not Apple’s fault but annoying nonetheless. So far so good and while I don’t mind using OSX for my browsing, email and creative stuff, that machine is meant to be running Windows Server 2008 x64 as a development platform.

Why Windows Server 2008 x64?

Well, it has some excellent features, a smaller footprint than Vista, all the aero eye candy, is apparently noticeably faster than Vista and has none of the nagging security prompt (you are considered administrator though, so keeping safe is entirely up to you). The 64 bit version can also address the full 4GB of RAM without limitation and all server features are optionally installable. By default, the installation is actually pretty minimal and you have to set services and options to get Windows configured as a proper workstation. It is after all, meant to be a server. Oh, I almost forgot that there is also support for HyperV, although you must make sure you download the right version (if you list all available downloads in your MSDN subscription, you’ll see some that are explicitly without that technology).

Installing Windows Server 2008 x64 is remarkably easy.

You’ll need to repackage the ISO as it won’t work properly (something to do with non-standard file naming options). It’s fairly easy if you follow the instructions from Jowie’s website(cached version): you can get the ImgBurn software for free as well, which is a good find in itself. It should’t take more than 30 minutes to repackage the DVD.

In OSX, go to Applications > Utilities > Boot camp and follow the instructions on screen. You will be able to resize the default partition by just moving the slider. I left 60GB for OSX and allocated the rest to Windows. The good thing is that OSX can read Windows partitions, so you can always store data there. Windows however, can’t read the HFS+ mac file system, although there are some third-party tools that can do it [1][2][3].

Insert your repackaged DVD and Bootcamp will have rebooted the machine. After a few minutes of blank screen (and no HDD activity light to let you know something is happening), windows setup launches.

You will be then prompted with the choice of partition to install to. Select the one named BOOTCAMP, then click the advanced options button and click format. From there one, windows will install everything, then reboot, then carry on installing, then reboot one last time.

In the Control Panel > System > Advanced System Settings > Advanced > Settings > Advanced > Processor scheduling, set to Programs instead of Background services.

Activate your copy of Windows using Control Panel > System. I was getting an error code 0x8007232B DNS name does not exist error. To force activation, just click on the Change Product Key button and re-enter the same key you used during install. Windows will activate straight away.

When booting your Macbook, press the Option key and you will be presented a list of boot choices.

]]>22Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=952009-05-04T23:37:53Z2008-07-18T09:16:15Z Years ago I did a small utility to convert DWG or DXF files into a vector-graphic, zoomable, SVG preview. The Linux command-line utility was used in a larger drawing management application that I had built before I left that company for greener pastures.

The Linux server-based application would scan the vast (100,000s of CAD drawings) and, depending on their format, attempt to create thumbnails and extract textual information from them and populate a database so we could easily find related drawings through a simple web-interface.

The software would present thumbnails of the drawings; when the drawing was in DWG or DXF format, the thumbnail would be an SVG vector representation of the original drawing instead of an image. That made the preview much more useful as you could zoom in and still retain enough detail to ascertain if the drawings was actually what you were looking for.

Other file formats (TIFF, HPGL plots, etc) were transformed into PNG image previews that were saved into 2 size: a small thumbnail, good enough to be displayed in a list, and a larger one that would show more details.

The server software was written in Perl, with some of the converters in C. Every night, the server would go through the whole tree of drawings on the filesystem, looking for drawings it hadn’t seen before and it would pass them to the appropriate plug-in for extracting text -where possible- and create thumbnails.

Memories of fun projects…

Download links:

]]>4Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=932013-09-12T03:32:36Z2008-06-20T08:48:31Z 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:

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.

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 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

License

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.

References

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

]]>7Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=862009-02-03T06:50:20Z2008-06-07T11:15:29Z 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:

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.

Resources

]]>5Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=762014-10-22T11:50:57Z2008-05-20T10:00:54Z This project provides a custom and enhanced message box replacement for the default MsgBoxfound in Access. A Test database containing all the code for Access 2007/2010/2013 is available at the bottom of this post. (UPDATED Saturday 21OCT2014 to VERSION 1.10.)

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 colour 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.

From of version 1.4, it will display on the correct monitor in a multi-monitor environment.

Version 1.10 adds a feature that allows users to dismiss a particular message so it doesn’t appear again.

How to use it

Download the demo database below and copy (drag & drop) the following into your application:

the FormDialog form,

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.

'-----------------------------------------------------------------------------
' Simple use of the Plaintext box
' Note the use of n that will be converted into a newline
Dialog.Box "This is a plaintext message.\nClick OK to dismiss",
vbOKOnly + vbinformation, _
"Message Title"
'-----------------------------------------------------------------------------
' Getting the result back
Dim dr As vbMsgBoxresult
dr = Dialog.Box("Are you sure you want to delete?", _
vbYesNoCancel + vbQuestion, "Confirm action")
If (dr = vbYes) Then DeleteRecords
'-----------------------------------------------------------------------------
' Using named parameters
Dialog.Box Prompt:="All your bases are belong to us", _
Buttons:=(vbOkOnly + vbCritical), _
Title:="Bad error"
'-----------------------------------------------------------------------------
' Using the RichBox to display simple HTML
' The first line will be bold, then the word 'button' will be printed in red
' Here the \n will be escaped to '<br/>' tags to simulate newlines.
Dialog.RichBox "<strong>This is a bold message</strong>.\n" & _
"Click the <font color=""#FF0000"">button</font> to dismiss.",
vbOKOnly + vbInformation, _
"RichText Message Title"

Options

There are a few additional settings that can be used to change the behaviour of the enhanced message boxes.

Custom buttons

You can customise the button labels instead of using the default ones (thanks to Kristjan for the suggestion):

Button delay

One is that you can adjust the delay before the buttons become activated.

'-----------------------------------------------------------------------------
' Use the ButtonDelay to specify the time in seconds before the buttons become activated
' The default is 2s. Use 0 to activate the buttons immediately.
Dialog.Box Prompt:="All your bases are belong to us", _
Buttons:=(vbOkOnly + vbCritical), _
Title:="Bad error", _
ButtonDelay:=1
'-----------------------------------------------------------------------------
' Change the default delay value.
' To disable the activation delay
Dialog.DefaultButtonDelay = 0
' To make the user wait 3 seconds before they can press any button
Dialog.DefaultButtonDelay = 3

Beep

Another one is that you can enable or disable whether beeps should be played or not.

Save Folder

It is recommended to set the the folder where we should save the content of the message when the user clicks the Save button on the message box.

'-----------------------------------------------------------------------------
' Change the save folder.
' By default, the text messages will be saved in the same directory as the database.
' Here we want them to be saved to a temp directory
Dialog.DefaultSavedTextFileFolder = "C\:temp"

These few settings make the enhanced message box more customizable.

Raw text and paths

By default, the enhanced dialog box will escape certain sequences in the message to convert them to their printable version:

Escape sequences like \n and \t are converted to newlines and tabs spaces

Unicode sequences are converted to their symbol: \u20ac is converted to the euro symbol €.

If you do not want this behaviour (for instance you need to display data that contains lots of \ characters), use the NoStrEsc option:

'-----------------------------------------------------------------------------
' By default, all messages are unescaped.
' Here however, we want to disable that so we can display
Dialog.Box Prompt:="A path c:\my\doc\file.doc", _
NoStrEsc:=True
'-----------------------------------------------------------------------------
' Change the default behaviour. This is False by default.
Dialog.DefaultNoStrEsc = True

Alternatively, you can use the helper function dialog.EscBackslash():

'-----------------------------------------------------------------------------
' Use EscBackslash() when you only want some portion of text
' to display '\' correctly, like paths.
' Here however, we want to disable that so we can display
Dialog.Box Prompt:="A path " & EscBackslash("c:\my\doc\file.doc")

Don’t display this message again

Based on suggestions (and on a feature I wanted to implement for a while), I added a way to allow the user to choose not to display a particular message again.

Note that this feature will only work for dialog boxes displaying a single vbOKOnly button. It makes some sense since if you ask the user to choose between multiple actions, you can’t really expect their choice to be the same every time the message is displayed.

To make the dialog box dismissable, you only need to provide it with a unique ID for the message, using the DismissID option:

The user can then tick the box and this particular message will never be shown again (unless we reset the setting for it).

To ensure that the user’s choice is remembered even if the Access application is updated, the message’s DismissID is stored in the registry under:HKCU\Software\VB and VBA Program Settings\<AppFileName>\DialogBox, where <AppFileName> is simply the name of your Access file (without the path).

You can easily re-enable a particular message or all messages from your code:

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.

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% of 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:

Public Function ShowModal() As VbMsgBoxResult
...
' Here we reset the result for the clicked button such as vbOK, vbYes, etc
' This is set in each Button's Click event
m_Result = -1
' Wait for the user to click a button
Do While (m_Result = -1)
DoEvents
Sleep 50
Loop
ShowModal = m_Result
End Function

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.

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

Upgrading from an older version

If you are already using the enhanced DialogBox, upgrading to the newest version is simple.

In your Access application:

delete the FormDialog form,

delete the Dialog module.

delete the API_GetTextMetrics module if you have it (used in versions before 1.5)

Download the new version of the demo database below and open it.

drag and drop the `FormDialog to your application

drag and drop the Dialog module to your application

That’s all you need to do.

Code and demo database

You can download a database containing all the necessary code as well as a number of tests. This version contains the database in Microsoft Access accdb format (the code relies on features that don’t exist in pre-2007 versions of Access).

v1.5: 23JUN2013 Many thanks to contributors Steve Spiller, Jiriki and Kytu for improving and pointing out issues. See details below.

Moved the code from the API_GetTextMetrics module into the FormDialog class to reduce the number of necessary files (now only FormDialog and Dialog objects are necessary).

Corrected bugs on the test form (button delay and beep options on the form were not wired up correctly in the test form)

RichBox was not initialising its buttonDelay correctly, resulting in the first call to use a 0 delay instead of the DefaultButtonDelay value.

Corrected bug reported by Jiriki on 06JUN2013 (when the ButtonDelay was set to 0, the dialog would just close the first time the dialog was opened).

Focus issues should be solved: the buttons are now properly focused and will behave as the standard dialog box (you can hit ENTER or ESC on the keyboard once the buttons are visible to confirm the default dialog action or cancel it).

Addressed parent form focus issue mentioned by KyTu on 19JUN2013: when closing the dialog, the parent form will be properly focused instead of the Navigation Panel.

Now supports both x86 and x64 Office systems (32 and 64 bits versions of MSAccess). Many thanks to Steve Spiller for sending me the updated database.

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.

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.

]]>150Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=732013-09-12T03:33:30Z2008-05-06T03:06:19Z 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:

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.

]]>39Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=722014-12-01T04:31:51Z2008-05-03T13:20:43Z Microsoft Office 2007/2010 comes with 3 colour (color) schemes. Users can easily change it but when you deploy an Access application under the Runtime your users have no way to set the colour scheme as the application’s options are not available. (Article and Code Updated 01DEC2014.)

Luckily for us, the global colour scheme setting in the registry under the Key:

The values being stored under that key are, For Office 2007 and Office 2010:

1: Blue

2: Silver

3: Black

For Office 2013:

0: White

1: Light Gray

2: Dark Gray

With this information, we can easily both read and set the colour scheme. The only caveat is that I could not find a way to notify Access to reload the setting automatically once it is changed, so users will have to restart the application before the change becomes active. A small price to pay but if anyone has a better idea, please let me know.

To write the new value to the registry I use a set of WIN 32 APIs that are more flexible than the default ones provided in VBA.

You can download the sample database as it contains all necessary files, including the definition for the Win32 API functions.

References:

]]>14Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/2008/ms-access-modal-dialogs-with-transparent-backgrounds/2013-09-12T03:33:55Z2008-05-01T11:36:36Z Microsoft Access Team made an interesting post and a follow-up on how to add a transparent layer that cover the screen to focus the attention of the user to a login form or other important popup window.

The trick is to use some WIN 32 API calls to modify the transparency of a standard MS Access form made to cover the screen.

The effect is quite neat and I thought I would try it and make a sample database for others to tinker with it. My version allows you to chose between covering the whole screen or just the main Access window and it will test if it’s running under a Remote Desktop Terminal and disable the layer in that case.

Update 07MAY2008

Following Rob’s improvements I made another sample database that incorporates his code with a few improvements:

I added the LightBoxForm.LayerToFullScreen property so users can choose explicitly how they want the layer to be shown.

I moved the code to hide the layer into a Hide() sub so you can just show/hide the layer using LightboxForm.Show and LightboxForm.Hide.

I changed the Form’s Resize event code in the LightBoxForm class to avoid flickering: resizing the form within its Resize event actually trigger the Resize event again a second time which causes flickering. I simply modified the code to make the form totally transparent (opacity of 0) the first time the event is fired and assign it the expected opacity when the event handler in re-entered.

Samples

There are now 2 sample databases. Ech zip contains a Microsoft Access 2007 ACCDB file and its conversion to Access 20001 and Access 2002-2003 MDB but please note that I have not been been able to test those in older version of Access and that form transparency doesn’t work in Operating Systems older than Windows 2000.

Troubleshooting

If you are getting security warnings: make sure that you open the database from a Trusted Location or you will receive a security prompt. If you don’t know how to do that, check these steps.

If the layer appears on top of the login form instead of behind: make sure that the top-most form has ist Modal properties set to Yes and the frmLightBox form has its modal property set to No. If you improve on it, please let me know and I’ll post it here for all to find.

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

]]>19Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/2008/apples-os-edge-is-a-threat-to-microsoft-really/2008-05-22T16:12:19Z2008-04-13T11:13:18ZBusiness Week has a recent article where the author foresee the demise of Windows in favour of Apple’s OS. Reading it, I couldn’t help thinking I was reading one of these overenthusiastic 1925 popular-science article promising us that within just a few years we would all use our own flying car to get to work. Yeah, right, mine is parked right under my window.

The basic premise of the article is that Apple will attack the corporate market through the back-door, using the iPhone and its forthcoming development platform. The author contend that development to the iPhone will drive interest in the Mac and allow Apple to displace Windows by offering more business-related applications that would eventually not require Microsoft’s OS running side-by-side in your Mac, freeing you of the dominance of the evil empire. Please read the article first. Done? Hmmm… Ok, let’s see…

First, let’s be nice: if Apple want to eat at Microsoft’s dominance of the OS market, then all the better: more competition will drive innovation and choice, which is always nice. There is certainly some truth to the idea that Apple could offer some useful tools and technologies to the corporate world.

The problem is that author of the article was a bit too much of a Mc Fanboy(TM) to make his arguments compelling.

Let’s start direct quotes from the article.

Speaking of the ability of the Mac to quickly switch from a Windows application to a Mac by using a keyboard shortcut:

“Windows users, in the very near future, will be free to switch to Apple computers and mobile devices, drawn by a widening array of Mac software, without suffering the pain of giving up critical Windows-based applications right away.”

Useful as it may be, it’s forgetting one thing: if you need to be able to run your windows application under a Mac, you will need to give Microsoft money for the Windows license, making the newly found convenience of running the two OS together seamlessly a fairly expensive one, both in terms of computer resources and money. While it will interest a lot of people and they may use their windows apps on Apple hardware is this really compelling enough to make it a viable migration path?

About the new Mac OS kernel:

“That kernel has proved easily adaptable across the entire Apple product line, from highly complex servers all the way down to the relatively simple iPod Touch. Such modularity allows Apple to add whatever functions are necessary for each product environment—all while maintaining cross-product compatibility. By contrast, Microsoft has held on to an OS tethered to the 1980s, piling additions upon additions with each upgrade to Windows. With last year’s arrival of Vista, Windows has swollen to 1 billion bytes (a gigabyte) or more of software code. The “Mach” kernel of the Mac OS X, however, requires less than 1 million bytes (a megabyte) of data in its smallest configuration, expanding modestly with the sophistication of the application.”

All this is so silly and skewed that I wonder where to start. First, kernel size is irrelevant: a basic kernel functionality does not make an operating system. Additionally, I doubt that we’re comparing the same functionality and for desktop or server applications this is utterly irrelevant. For small devices Windows has its own flavours and they can quickly be deployed to almost any hardware with minimal effort. Applications built for Windows built for mobile devices can be ported to Windows without too much trouble, and vice versa. Whatever platform you’re developing on, applications for small hardware are always an exercise in compromise: there is no way that you can just recompile an app for a different target and have it just work beautifully in any device. A mobile app must take care of power requirements, limited screen estate, memory and CPU limitations and specific usability issues like the presence of a touch-screen instead of a keyboard and mouse. The point is that you have to take the platform into consideration when crafting software. Besides, using .Net makes it fairly easy to develop applications for any flavour of Windows. Additionally, whether the OS under the XBox is different or not from the desktop version of Windows is also irrelevant: you can develop games under the XNA platform that compile and work just the same under Windows as they do on the Xbox.

Now, for the argument regarding the “additions upon additions” made upon Windows that contribute to its bloat, it’s an easy shot to make and an unfair one: Apple has had no regards for its legacy applications and since its primary market was consumers, it could get away with making each of its major OS release incompatible with the previous one. We can argue whether it was a good thing for Microsoft to keep all the quirks from previous releases of its operating system alive but we must not forget that in the corporate world, Windows has close to 100% market share on the desktop. What that means is that Microsoft could not afford not supporting business applications across versions. it would have been suicidal to even envisage dropping compatibility. If Apple ever get into that corporate market above a few percent it will have to guarantee compatibility between versions of its OS and programming tools if it wants to have any chance at being taken seriously at all. When companies spend money developing an application that is critical to their business, they don’t want to hear about its supporting OS becoming incompatible every couple of years. Apple would have to support and maintain compatibility for at least 10 years for every major OS version. So far Apple hasn’t shown that it was capable of that type of commitment.

A small digression. A couple of weeks ago I found some old files of mine on a floppy. They were Ami Pro files, made at a time when Ami Pro 3.1 was the best word processor around. Problem was that I could not safely open these files any longer: filters for various word processors would mangle the complex layout of the pages, making them un-usable. Out of curiosity, I found a full version of the original Amipro 3.1. I had no expectations of being able to run that setup package. After all, Ami Pro 3.1 came out in 1994, before Windows 95 was even released! Well, I clicked on that setup.exe file and watched the installation process go through… Everything went fine. Surely, I thought, there is no way this is going to run under Windows XP. It’s going to crash for sure. I double-clicked on the 16 colour icon and lo and behold, the whole thing actually ran! Flawlessly! I was able to open my old files without any issue, save them under another format. Everything worked, miraculously. That application was written at a time the Internet didn’t even exist yet I was able to install and run it without problem on a current operating system. Back to our regular schedule.

“Despite Apple’s relative scarcity on corporate desktops, Mac laptops are already well accepted within the enterprise, with a market share of more than 20% and growing. For business travellers, the new MacBook Air, some three pounds lighter than comparable Windows-based laptops, already offers one huge advantage.”

First, Apple has a almost 20% market share on overall laptops sales only, not corporate sales. Second, that figure is for the US only. Apple’s laptop market share in the world is certainly not bad, but it’s a quarter of that figure, making adoption of Apple laptops outside the US very small indeed. Third, while Apple laptops are certainly sexy, they bring their own issues to businesses: lack of in-house serviceable parts, issues with making them fit into a complex infrastructure, hardware and software compatibility problems. Bringing Macs into your organisation can be painful. Of course, it’s never impossible, but beyond simple setups, it takes time, energy and money to make stuff work seamlessly. So I contend that, unless you have a serious commitment to Apple, most of the Macs getting into companies are for simple usage: fetching emails, browsing, making presentations and maybe Photoshop, although it seems that the application that was once the sole reason for some to use macs is now going to switch side, at least for a little while. For Macs to become a first-class corporate platform would require that Apple makes a very serious commitment to cross-platform development to make applications work on Windows as well. I seriously don’t think that the ability of Macs to run Windows side-by-side will do much: as mentioned above, the expense incurred offers no real benefit to businesses and having to support both platforms would increase the cost of ownership because of the upfront cost of buying and installing multiple OS, 15% higher average cost of Apple laptops compared to other similarly specified laptops and the cost of maintaining and supporting multiple hardware and OS stacks. Slickness can only get you so far I suppose.

“While Mac desktops offer a growing number of superior features over Windows desktops”

Seriously dude, what superior features?

“Apple’s recently introduced Leopard servers compete in a market of unhappy Vista server buyers where Microsoft’s market share is only 40%. Leopard has a decent chance to expand from its small beachhead.”

Huh? Vista servers? WTF is that? You can’t be talking about Windows 2003 because it’s actually a rock-solid platform and there isn’t much to complain about. Windows 2008 is just coming out and it looks just as promising. Apple has some seriously nice server hardware, it’s so beautiful to look at all this engineering that it gets me all hot and bothered. Seriously though, on the server side, Leopard would not be in competition with Windows but with *nix. Server-side applications like ASP, Exchange, Sharepoint, SQL Server and the myriad other Microsoft-only server software will only work on Windows. Apple is not going to compete as a platform for hosting these which means it will host services traditionally found on Linux/Unix/BSD systems. In that *nix camp Apple would certainly be a good contender, although it would have to prove that it can compete with the low license and hardware cost of its competitors and offer more.

One more thing to remember: Apple is in the hardware business: it uses software to lock people into its hardware business. I’m all for competition but at least using Linux or Microsoft products doesn’t lock me into a single hardware/software platform pair: I have orders of magnitude more choices when it comes to my servers, desktops and laptops than what Apple can offer me. Apple products may be slick and beautifully engineered but they certainly don’t offer more freedom: chose an iPhone and get stuck with a single network provider, buy an iPod and get stuck with iTune, buy a mac and get stuck with Apple, limited software offerings, no serious games and limited hardware support.

One other thing to keep in mind is that Microsoft has acquired a huge weight in terms of software development power: new technologies have been pouring out of Redmond at a pace that is impossible to follow. The number of developers and companies able to develop software for Windows platforms is wayyy above what Apple can dream of at the moment (we’re talking about millions of developer making a living off Windows). Apple will need to make serious efforts to woo the huge amounts of developers it needs to bring enough business and non-business applications to its platforms before it can become a serious competitor to Windows in the corporate world. Whether Apple has the capacity and will to do that instead of remaining a consumer-oriented company remains to be seen.

That being said I wouldn’t mind developing software for Apple platforms. I would love to be able to use a generic “surface laptop”, a sort of larger iPhone that could be used by sales people for taking orders and showing off new products.

Anyway, the point of all this was simply to offer a modest reality-check to an article that should have been a bit more measured and balanced in its fanboy-ish enthusiasm.

]]>1Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/2008/wpf-and-silverlight-it-will-take-a-while/2008-05-25T12:24:07Z2008-02-10T08:45:07ZDecoupling the User Interface from the underlying code has been one of the holy grails of application development. Layers of indirection and new patterns have been invented over time to try to separate what the user does from the back-end data. It’s been a long and difficult journey but WPF is the last attempt at completely separating the user interface from the gut of an application, leaving graphic designer do what they do best and programmers do what they do best. I think this disconnect has actually hindered the adoption of WPF to a great extent. I also believe that Silverlight, while showing great promises, is going to take a long time to get off the ground.

Good graphic designers who work on User Interfaces are too few and far between, and large organisations will take time to get staff with a sufficient level of expertise to be useful in developing meaningful WPF/Silverlight applications. Until now, the bulk of the development effort was done by developers. Most ISV only have developers onboard.

As far as I can tell, it seems that main reason behind this slow adoption is that WPF/Silverlight has only been noticed by developers and they don’t know what to make with it. WPF/Silverlight are not really developer-friendly: the development infrastructure is there but we’re left with a blank canvas and nothing to drag and drop onto it.

There will be a great gap, probably lasting a few years, before there are enough experiments in these new user interfaces that some useful lowest common denominator can be exploited by non-designers. We’ll have to wait for tool vendors to provide the bulk of user interface blocks and for a few new patterns to appear before all the designer-challenged developers like me can make decent interfaces without requiring a professional graphic designer in their midst. In the mean time, we’ll probably have useless, but pretty, applications, some useful, but ugly ones and a lot of people scratching their head trying to couple the designers and the developers in a working relationship.

]]>2Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/2008/ms-sql-server-express-a-good-choice/2008-05-25T12:23:20Z2008-01-30T07:03:09ZMicrosoft SQL Server comes in many editions, ranging from completely free to use and distribute to versions costing tens of thousands of dollars. For small businesses, or when you can live with the limits imposed, the Express edition is one option to consider.

Here are some reasons why SQL Server Express may be a good choice:

You’re upsizing an Access database

SQL Server is the natural extension of upsizing an existing Access database. It work automagically with minimum effort providing that you followed some simple good-design rules from the start.

You’re future-proofing your needs

Because SQL Server comes in many flavours, you know you -or your customers- can upgrade to a more capable (albeit more expensive) version in the future if needed.

Very flexible

As usual with a lot of Microsoft development tools, SQL Server will happily let you shoot yourself in the foot by providing you with a fairly easy way to treat your database as a complete development platform. It’s good in the sense that you have interesting tools and capabilities included in the server, and it’s bad for the exact same reasons. I tend to prefer database servers to be just that: data repositories, and I’m not too fond of relying on specific, non-standard features of a particular database system, but what do I know.

Excellent out-of-the-box development support

Deep integration with .Net and Visual Studio, without any effort, Microsoft saw to that of course. In some cases, such as LINQ to SQL, it’s almost the only real choice, although the other database vendors are working hard at the necessary providers, so that lead should be short-lived. There is something to be said about developer productivity: you have to give credit to Microsoft for making their tools well integrated and usable from each-other. What it means is that for small developer shops there is much to gain in surrendering to this “ease of the default”. Of course, it’s a double-edged sword, but having a complete development infrastructure work out of the box is certainly a big help, and if you don’t like it, you’re still free to chose something else.

Lots of tools

With SQL Server Advanced Services, you also get Server Management and Reporting Services. These are great tools made available for free. The only missing one for SQL Server is the Reporting designer. While the reporting service means that you can use existing reports, only SQL Server Standard and Enterprise have it. There is an option for developers though: the (nearly free) SQL Server Developer edition is in fact the same as SQL Server Enterprise, without the license to use in non-developer or tester environment. This means that as a developer, you can create and distribute your reports to be used by your customers who will be using SQL Server Express.

Did I mention it’s free?

All this is free, as in beer, not as in liberty though. For commercial applications targeted at small businesses, SQL Server Express is a really good choice: you can distribute it without problem, the customer gets all the tools, can easily find outside support, and they can always migrate to a more beefy version if their needs grow, all that without having to depend on you. So it sort of offers customers a kind of freedom that they wouldn’t have with other choices.

Of course you can get that with other database systems, although you have to be careful which Open Source one you choose: I recently decided not to use MySQL any longer for the simple reason that it’s too expensive and restrictive in a business environment, at least for the kind of work I do.

Why would you not want to use SQL Server Express?

You don’t want to depend on Microsoft

That can be a good reason enough sometimes. There is nothing preventing Microsoft from crippling SQL Server Express in the future to force users to move to a paying version early. I suppose that whatever database system you use, even Open Source ones, there is always the possibility that the company supporting its development goes bankrupt, the Open Source projects goes dead or decides to go in a direction that doesn’t suit you..

It’s only supported on Microsoft OS

True, and that’s a good reason to chose something else. There is a hidden cost in SQL Server Express: it needs to run on a Windows machine, and that’s not free, although SQL Server will work on older Windows 2000 machines and Windows XP which are arguably not expensive.

Your database needs will exceed SQL Server Express specifications

If you think any of your databases will grow beyond 4GB or that it will get busy and you need all the RAM and CPU you can get, then SQL Server Express is probably not for you as it will only use 1 CPU and 1GB of RAM at most. If your needs go beyond that, then you’ll have to move to a paying version.

Upgrading can be expensive

It’s true that moving to the next cheapest upgrade of SQL Server Workgroup will cost you about US$700 for a 5 user license. The limits imposed on the database are much higher (2 processors, 3GB RAM and no size limit) but if you need more clients / or higher limits, then the expense will grow quite fast, and you’ll have to manage those hateful client access licenses.

Your needs are more modest

We haven’t talked here about single-file/single-user database systems. These databases don’t user resident services and are usually meant for more limited needs, sometimes allowing only a single user to be connected. The footprint of these non-server databases is a lot smaller, typically only requiring a single dll or a handful of files to be installed. They are extremely useful for desktop application that do not really require multi-user support or advanced security features. Here again, Microsoft offers SQL Server Compact, which, despite the name, doesn’t have much to do with the other SQL Server editions. This one is also free, but has a limited feature set and only allow single-user access as it is meant to be a lightweight database and works well in limited memory environments such as those found on mobile devices.Of course, here again there is a lot of competition: Thunderbird, SQLite, MS Access and VistaDB (for embedding into .Net applications, not free) to name a few.

These are pretty good times when it comes to databases: we get more choices now than we ever had. As usual, choosing a database as a back-end for your products isn’t easy: you need to consider cost, licensing, support and the future. There isn’t a single database system that will meet everyone’s needs for all types of use, so choose carefully. SQL Server Express is a very good contender in that market. It should not be dismissed out of hand because it’s from Microsoft, in the same way that PostgreSQL shouldn’t be dismissed because it’s Open Source. Just use the tool that best answers your needs for your particular circumstances.

]]>0Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/2007/people-mind-your-dates-plz/2008-05-25T12:25:13Z2007-12-15T15:34:46ZWhat does 04/05/01 mean to you? Let’s make it easy: is that date in 2001 or 2004? And if I write it like 04/05/2001, is it really better? are we in April or May? And the answer is…

If you are from North America and a handful other countries 04/05/01 would mean 5th of April 2001. If you’re in Asia or some eastern countries, if could be 1st of May 2004. For the rest of the world, it would look like 4th of May 2001.

We cannot ignore any longer the importance of writing dates in a format that is understandable by everyone. Still too often, web sites -even big companies– with a global audience state dates in a format that is probably obvious where they live but not consistently so to everyone else.

Developers are sometimes guilty of this sin, as can be seen in the format chosen to specify dates in Microsoft Access SQL, which requires that hard-coded dates be formatted in the US rather than the more neutral ISO8601 format. I wonder how many bugs that little egocentric feature caused in business applications that where developed by foreign coders… I know it also happened to me at least once…

The worst thing is that when you write 04/05/06, you may very well have thought about the rest of the world and meant DD/MM/YY; alas, unless the context grants us some clarification or the actual date is unequivocal, there is sometimes no way for a lot of us to know for sure what you really meant.

We should ban the use of the short date format in communications that may be read by more than 3 people, in today’s world, that’s pretty much all communication.

Instead a simple format such as 05MAY2006 is clear an unequivocal: it’s easy to read and understand, regardless of where you live. You’re free to add spaces, dashes, dots, comas or slashes to it if you want, you can use 2006MAY05 if you prefer or even MAY05,2006, whatever pleases you: it still remains readable.

The rule is simple:write the year in full and never write the month as a two-digit number, that’s reserved for the day

The point is that you, me and the rest of the world can read it and understand exactly what was meant; it’s an easy problem to solve. Surely that means something to you?

References

]]>5Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/2007/thank-you-spammers/2013-07-14T14:35:31Z2007-05-31T06:06:48ZI’ve been tired of spam on my websites. The few hundreds messages spammers leave everyday are a bit of a nuisance. Now though, I’ve decided to make them work harder to get their messages ignored. Last week, reCAPTCHA came online. It’s an effort inspired by none other than Luis Von Ahn, so you know it’s good.

If you don’t know him then he’s the mastermind being similar projects that centre around a simple premise: make humans do the work that computers can’t do. One of his on-going projects is the ESP Game where 2 online players are trying to come up with a common description of a random picture. It’s apparently an addictive game and it helps solve a problem that computers are terrible at today: describing accurately the content of photographs.Google is using his research to make image search more useful by returning content more relevant to your queries.

So, what’s the relevance with the CAPCTHA? CAPTCHASs were invented to resolve a simple problem: stopping computers from automatically filling-in web forms to create accounts on popular free services that they would use to send spam from. They are a visual version of the Turing Test, elaborated by the WWII genius cryptanalyst Alan Turing as a way to test how far machines could behave like humans: not knowing who she was interacting with, if a person could not tell the difference between a human and a machine, then the machine passes the test. It’s a measure of the success -or lack of- of artificial intelligence and the idea spawned many others, including CAPTCHAs.

CAPTCHAs simply require that small problem be solved before a web form can be submitted. Typical problems include blurry and distorted images of text or numbers that would be very hard for computers alone to decipher, but that our brain has no problem solving. There are an estimated 60 millions of CAPTCHAs being solved by human beings every single day. That’s a huge amount of lost brain power as nothing really useful comes out of it (apart from preventing spam, of course).

reCAPTCHA‘s genius idea is to use that brain power to solve a problem that we would actually like computers to solve: digitizing books. There are millions of books that were printed in the days before computers became ubiquitous, and there exist no electronic version of them except scanned images of their pages.Optical Character Recognition software is getting very good, but when the scan is of poor quality or the book is old, many words cannot be automatically recognised. Humans on the other hand are quite good at reading words, even if they are badly distorted and barely recognisable. Instead of making up a distorted image that you would have to recognise, reCaptcha simply presents you with 2 words: one it knows and one it doesn’t and you’re asked to guess both. Every unknown word is checked multiple times by different people and you thus end-up with a very accurate interpretation of the word that can be fed back into the electronic version of the book being scanned.

CAPTCHA do not entirely solve the problem of spamming, but they are an financial issue to spammers: automated electronic system cannot solve good CAPTCHAs, so some spammer rely on low-paid humans to do the dirty work for them.

It’s fine by me: poor people are getting paid to do something useful (help digitise books) and spammers are wasting their money doing so. In my case, they lose even more, because I use moderation to read comments before they are visible and Askimet to detect spam, which means that however hard they try, their spam never gets anywhere anyway.

In the fight against spammers, it makes me happy to know I’m costing them something for a change…

]]>2Renaud Bompuishttp://blog.nkadesign.comhttp://blog.nkadesign.com/?p=392008-05-25T12:50:51Z2007-02-20T07:57:37ZMediaWiki 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: