Posts filed under 'Database'
This project provides a custom and enhanced message box replacement for the default MsgBoxfound in Access. A Test database for Access 2007 is available at the bottom of this post. (Updated Tuesday 17MAR2009.)
What’s wrong with the default MsgBox
The default message box in Access is sometimes useful to warn, inform or ask confirmation from the user.
It has, however, a few drawbacks:
- It is bland: the standard message box does not even follow the currently selected Office 2007 scheme.
- The amount of text it can display is limited: if you try to display too much text it will be truncated.
- You can’t copy or save the content of the message.
- Because popup boxes are viewed as intrusive, people tend not to read them and end-up closing message boxes before they realize they may have contained useful information.
- They only displays plain text: you cannot format the message to draw attention to the key points.
- They are blocking, meaning that nothing can happen in the main application while the box is displayed (it can’t even shut down).
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.
How to use it
Download the demo below and copy (drag & drop) the following into your application:
- the
FormDialog form,
- the
API_GetTextMetrics module,
- the
Dialog module.
If you rename the FormDialog, make sure you replace any occurrence to it in the code, in particular in the Dialog module.
Since the enhanced message box is just a replacement for the standard one, you just use it like you would use the MsgBox.
There are a few additional settings that can be used to change the behaviour of the enhanced message boxes.
One is that you can adjust the delay before the buttons become activated.
Another one is that you can enable or disable whether beeps should be played or not.
The last settings is the folder where we should save the content of the message when the user clicks the Save button on the message box.
These few settings make the enhanced message box more customizable.
Large text
The standard MsgBox cannot display much text. On the other hand, there is no real limitation to the amount of text the Box and RichBox can display.
When the amount of information is too much to fit the maximum allowed size for the message box the text will overflow and can be scrolled up/down as necessary.
Limitations of the RichBox
The RichBox version relies on the normal TextBox control’s ability under Access 2007 to display RichText wich is nothing more than lightweight HTML.
Because font size may be varying a lot in the message, it becomes very difficult to accurately predict the size of the box needed to display the whole message.
Short of implementing a complete HTML engine, we have to rely on some assumptions to display HTML.
The risk is that sometimes the content may not properly fit the TextBox control in some circumstances.
If you use the RichBox, thoroughly try displaying your messages and tweak the HTML as necessary to include additional lines or non-breaking spaces to ensure that the result looks good.
If you don’t overuse font size and don’t display in multiple fonts the RichBox should do the right thing most of the time.
Don’t overuse the RichBox to display colourful messages. There is a fine line between being informative and tasteless. Keep colours and formatting where it is useful.
I think that in most cases, the plain text version Box is more than enough.
Replacing MsgBox in existing code
As I said above, replacing the standard MsgBox is easy but you need to make sure your search and replace parameters are configured correctly:
If you’re getting strange compile errors, it may be because you forgot to tick the Find Whole Word Only and some of the strings containing the letter sequence “msgbox” were replaced in the process.
If that’s the case, you can revert the damage by simply doing a search and replace across the whole project on:
- VbboxStyle or VbDialog.BoxStyle to be replaced with VbMsgBoxStyle
- VbboxResult or VbDialog.BoxResultto be replaced with VbMsgBoxResult
How it works
The code makes extensive use of Win32 API calls.
Most of the hard work is done in the FomDialog class form. There is too much there to really go into the details but you are welcome to have a look at the commented code.
The code relies also on a utility function from Stephen Lebans used to calculate the size of of text. I have made some minor modification to that code so I would refer you to his original implementation if you are interested in calculating TextBox sizes for forms or reports.
In the code for the FormDialog, I re-implement some of the expected functionalities of the MsgBox: proper arrangement of the buttons, displaying of the appropriate icon, etc.
Once this is done, we calculate the size of the textbox needed to display the whole of the message.
In the case of RichText, we first use Application.PlainText() to convert the HTML into properly formatted plain text. We then calculate the Textbox size using a slightly larger font than needed as a way to ensure that the content of the RichText message will fit the box in most cases.
Once we know the size of the TextBox, we can easily resize the form to properly display the TextBox.
If there is too much text, we resize the form to its maximum permissible (70% or screen width and 90% of screen height) and change some of the visual cues to let the user know the text is overflowing.
One thing of note is the way the form is kept modal.
Rather than using DoCmd.OpenForm and DoCmd.Close I use the form as a class and create an instance manually (see the code in Dialog.Box and Dialog.Richbox). I keep this instance alive until I got the form’s result back.
If you are interested in knowing how the form is made modal, this is the code in FormDialog.ShowModal() what keeps the form open until the user clicks a button:
The Sleep() function is a Win32 API that stops the current process for the given number of milliseconds. This in effects hands back the control to the Operating System for a short time. That way the system is still responsive and does not consume resources when it’s just waiting for user input.
Sample database
You can download a sample database containing all the necessary code as well as a number of tests.
This version only contains the database in Microsoft Access 2007 accdb format.
Download the EnhancedMsgBox01.zip (116KB), v1.3 containing the ACCDB database.
Code Updates
v1.3: 17MAR2009
Thanks to Henry of Access-Pro.de for proposing a correction to the default buttons behaviour.
- Updated behaviour for the default buttons. They are now focused in a way that matches that of the standard msgbox.
- Reversed the naming of the buttons on the form to make it a bit more consistent with the standard box.
v1.2: 07SEP2008
Thanks to Andy Colonna (http://www.pcesoft.com) for uncovering the following bugs (check out his free Spell Checker with source code!):
- Corrected bug in
Form_FormDialog.FilenameSanitize() function that would
fail to remove all invalid characters for a file name.
- File name for the saved text message will be truncated to first 32 characters
of message box title in
Form_FormDialog.MakeFriendlyFileName().
- Changed the use of
FollowHyperlink to ShellExecute to avoid security warning
in some instances in Form_FormDialog.btCopyToFile_Click()
- Corrected twips to pixel conversion bug in
API_GetTextMetrics.fTextWidthOrHeight() that
would result in an improperly sized dialog box when the text message was too
wide.
v1.1: 08AUG2008
- Corrected code for
DefaultButtonDelay (thanks to Geoffrey) (was referencing
wrong variable, causing self-referencing code).
- Corrected code for
Box and RichBox to take the DefaultSavedTextFileFolder
into account (the path was previously not passed onto the dialog boxes and
the text file would always be created in the application folder instead of
the one specified by DefaultSavedTextFileFolder)
- Added license notice at top of source code.
v1.0: 20MAY2008
Resources
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.
May 20th, 2008
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 programmes.
How to use it
Download the sample database below, copy the Utilities module or just the Restart sub defined in it into your own application.
To use it, just call the Restart sub and the application will close and re-open.
If you supply the optional Compact:=true parameter, the database will also be compacted during the restart process.
This will work for normal databases (mdb/accdb) and also compiled (mde/accde) and runtime (accdr) databases as well.
Important note
If you want to use this code do not enable the Compact on Close option in Access for your database as the code doesn’t pick that up yet.
Instead, you can either simply call restart Compact:=true on user action (for instance from a menu) or on other triggers, for instance when the database is being open and hasn’t been compacted for more than a week.
How it works
If you’re curious about the technical details, here is how it was put together.
The main idea is that the MS Access database application has to be self-sufficient and restart itself by performing these steps:
- create a small batch file
- run the batch file, passing the path and extension of our database
- close the main application
- the running batch file would wait for the MS Access lock file to be removed
- once the lock file disappears, we open the database after compacting it if required.
The key point here is that the batch file cannot just reopen the database right away: if the application is big or if it’s compacting on close for instance, it may take several seconds to actually close.
The only moment we can be pretty sure that the database is effectively closed is when the lock file is deleted by MS Access.
The batch file is hard-wired in the Restart sub that does all the work:
When the application runs the batch file, it passes 4 arguments:
- the full path to the MSAccess.exe executable (used for compacting the database)
- the full path to the database without the extension
- the database file extension without the leading “.”
- the appropriate database lock file extension (
laccdb or ldb).
This allows us to easily construct the path to either the database or the lock file at line 07 and 09.
Line 08 is actually only inserted if we need to compact the database: it simply launches MSAccess.exe with the /compact command line switch.
The funny use of PING is actually a simple way to wait for some time before we check if the lock file is still there or not. There is not 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 inexistent, but valid, IP address.
Once the lock file has disappeared, we open the database at line 09 and then delete the batch file itself so we leave no leftovers.
The other thing of note is that we now use a counter to keep track of the number of times we checked the existence of the lock file.
Once this counter reaches a pre-determined amount (60 by default, ~ 45 seconds) we consider that there is a problem and the database application didn’t close, so we just exit and delete the batch file.
Download the DatabaseRestart.zip (48KB) containing both an Access 2007 ACCDB and Access 2000 MDB test databases.
Other implementations
Code Updates
v1.2: 13FEB2009
- Added optional parameter to compact the database during restart.
v1.1: 09AUG2008
- Now a separate test database (used to be bundled with the Colour Scheme sample).
- Added support for older Access versions (an Access2000 MDB is now included).
- Corrected wrong lock file extension for accd* files.
- Added a time-out feature after which the batch file will delete itself
after a while if the Access lock file wasn’t released
(for instance following a crash).
- Added checks to delete the batch file if it has not deleted itself for some
reason (for instance after a reboot).
- The batch file now has a unique name based on the name of the database,
allowing multiple databases to be restarted from the same directory.
- Added license notice at top of source code.
- Updated the article to reflect the changes.
v1.0: 06MAY2008
This work is licensed under a Creative Commons Attribution 3.0 Unported License.
May 6th, 2008
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 31MAY2011.)
Luckily for us, Office 2007 stores the global colour scheme setting in the registry under:
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Common\Theme
and Office 2010 in:
HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Common\Theme
The values being stored under that key are:
- Blue scheme
- Silver scheme
- Black scheme
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.
Download the ColorSchemeV1.3.zip (31KB) containing the ACCDB database.
The sample also contains some code to restart the database. This is the subject of another post: Restarting and compacting the database programmatically.
Improvements/uses:
- Find a way for Access to reload the settings without having to restart the application.
- Use the knowledge about the current scheme to change the other colour settings in the application (or even adapt the form’s theme).
Updates:
- 31MAY2011: added support for Office 2010.
References:
May 3rd, 2008
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.
Download TransparentLayer02b.zip (138KB), recommended version
(improved, more flexible version, based on Rob’s updated article).
Download TransparentLayer01b.zip (122KB), original version
(simple code, based Rob’s original article).
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.
May 1st, 2008
Microsoft 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.
References:
January 30th, 2008
Next Posts
