Microsoft Access
Technical Articles
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.Enhanced Message Box Replacement
A drop-in replacement for the standard messagebox that offers many enhancements, including the ability to display formatted text.
Last Update 17MAR2009.Restarting the database programmatically
Restarting the database from code used ot be a pain, now it’s trivial.
Last Update 13FEB2009.Changing the colour scheme programmatically
Allow your users to change the Access 2007 colour scheme from within your runtime applications.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.
Why Access?
I’ve been working on MS Access for almost a year 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 last year when a customer wanted a simple 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 wrap 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.
