VBA: Unicode Strings and the Windows API
We examine what happens when sending VBA Strings to the WinAPI and find some solution to avoid their unexpected mangling.
Last Update 01OCT2013.
Enhanced Message Box Replacement
A drop-in replacement for the standard messagebox that offers many enhancements, including the ability to display rich, formatted text.
Last Update 28SEP2013.
using .Net strings in VBA for fun and profit
Format VBA strings the .Net way!
Last Update 12SEP2013.
VBA for 32 and 64 bit systems
Make your code future-proof for all versions of Access, old or new!
Last Update 07JUL2013.
Changing the colour scheme programmatically
Allow your users to change the Office 2007/2010 colour scheme from within your runtime applications.
Last Update 31MAY2011.
Checking blank variables
A useful function to help test for ‘blankness’ of a VBA variable that could be Emtpy, Null, Missing, Nothing or an empty string.
Last Update 09SEP2009.
Run-time Error 3155 ODBC insert on a linked table failed
Correcting this strange MS Access error when using SQL Server and nvarchar fields larger than 255 characters.
Last Update 11JUN2009.
Access vs SQL Server: some stats (part 1)
Getting some baseline statistics about the performance of SQL Server vs MS Access on inserting records with autoincrement integer or GUID Primary Keys.
Last Update 16APR2009.
Building ‘upsizable’ applications
A few simple rules that will help making your Access application future-proof and allow it to be upsized to SQL Server with minimal pains.
Upsizing to SQL Server 2008
Don’t use the Upsizing Wizard provided in Access when you need to move your database to SQL Server. Use the free Microsoft SSMA instead!
Last Update 17MAR2009.
Checking network paths without freezing your application
Checking for the existence of network files or folders can be a pain in Access: it can take 30 seconds if the path cannot be reached during which your application will freeze and appear unresponsive. In this article I present a easy way to check for these troublesome paths without slowing down your application at all.
Restarting the database programmatically
Restarting the database from code used ot be a pain, now it’s trivial.
Last Update 13FEB2009.
Modal Dialogs with transparent background
As a follow-up to an article on the Access Team blog, show those important dialog boxes on top of a dimmed background.
I’ve been working with MS Access for a years now. Until then I had done everything in my power to stay away from this “Frontpage of the database world” as I liked to call it.
My hand was twisted when a customer wanted some basic ERP system that had to be implemented in Access since he wanted to keep some amount of control and it was the only sophisticated tool he knew how to use as a Manager.
It’s been an interesting journey so far. Access is one of those products that you love to hate.
After a while I had to rescind my view: it’s actually a very useful rapid application development platform that bridges the gap between pure database and pure GUI interface.
It’s not always elegant, and parts of it are showing their age (VB6 IDE, inconsistent VB syntax, less-than-glamorous controls) but Access 2007 has brought plenty of refinements that are keeping Access relevant today (ribbon, support for transparent images, theming and colour scheme, XP-styled controls, improved extensibility though Sharepoint and slightly better support for .Net).
Access gets a bad rap because the vast majority of its users are not professional developers: most people are power-users and managers and use Access as a simple tool to get the job done.
The resulting applications are functional but just looking at them is a hair-raising experience to a developer.
This doesn’t mean you cannot develop proper applications using proper coding standards. There are lots of talented Access programmers building professional solutions that solve real problems.
In this blog I share some of the titbits I created and knowledge I learned in the hope it will be useful to others.