Tag Archives: vba

Access Work

What’s so wrong with Access??

Be prepared for a slightly ranty post, but with a twist (i.e this time not about infographics).

I’ve been developing applications using Microsoft Access for over ten years now, and I still find it an extremely easy to use, versatile and powerful application development platform.  I still regularly build systems with it today, although my time is now somewhat split between Access, Excel and ASP.

About a year ago I met with a colleague from another organisation to discuss a regular annual return we send to report on our organisations progress.  In these talks we discussed data quality and how we as an organisation store and analyse our data for export.  The bulk of the data I access for reporting either comes from information stored in an old and somewhat clunky Access DB, and the rest from a number of linked SQL server views containing information from our primary MIS.  It works well, and the queries/forms I’ve created provide all the information and reporting that we need.

This ‘data expert’ spent a great deal of time explaining how we shouldn’t use Access for systems development as it is ‘clunky’, ‘unsecure’ and ‘unreliable’ as a platform.  At the time this bugged me somewhat but I let it go.

Earlier this week at a conference in London I bumped into the expert again, and the first thing she asked was whether we were still using Access to develop some of our business tools.  I confirmed and I got a ‘look’.  The ‘look’ said ‘you need to move with the times, mate’.  We went our own separate ways and I started thinking about things..

Over the past couple of days I’ve come to the conclusion that she is wrong, and has probably only ever seen the ‘bad’ side of Access usage (and believe me, I’ve seen some really bad Access databases in my time).

I do agree that, at enterprise-level it’s certainly not the best platform as a standalone DBMS – you’d be much better served to have your data stored in one of the enterprise-level DBMS systems, such as SQL server.  That’s pretty much a given, especially from a multi-user perspective.

Where I disagree with her most is in terms of using Access to develop a reasonably sophisticated front-end, with data stored in a separate and scalable database – in fact this in particular is one of the areas in which I think Access still holds its own.  With the use of careful planning, a decent, reliable, secure and versatile application can easily be put together, but I think only as long as the application is developed properly and sensibly.

What do I mean by properly?  Here’s a brief list:-

  • Controls and forms should be unbound.  Creating, deleting and updating records should be carried out using VBA to ensure reliability and properly defined control.  Access wizards and bound controls don’t provide enough data validation or error-checking.  Any decisions on how to deal with data shouldn’t be left entirely to the user, and the system should have adequate business logic inbuilt to make those decisions and act accordingly.  With a decent grasp of VBA it’s actually very easy to implement such logic seamlessly and transparently to your application.
  • Data shouldn’t be stored locally in an MDB file if possible – any data used by the application should be stored in separate tables, linked for example using ODBC and whatever necessary authentication.
  • Access applications should be locally installed for each user, and not stored on shared network areas, where files are at risk of corruption or accidental deletion.
  • Security needs to be implemented properly.

I’m sure there are more examples of ‘proper’ development I could highlight, but I’m tired, and my main point is that I don’t feel that there’s anything wrong with using Access as long as the application is developed properly, securely and using VBA and robust programing practices rather than macros, bound controls or wizards.

Before anybody feels the need to respond negatively to my comments, I want to clarify that I’m aware that there are many better systems to develop in than Access.  I merely find it irritating when people dismiss it out of hand, especially if they aren’t aware of what the tool is capable of if used sensibly.

Another similar view on this can be found here (I even felt the need to use the same post image!)

Any thoughts or comments?  Keep them constructive, please.

Tips Visual Basic For Applications (VBA)

VBA: The StrConv function

In excel, there is a function named PROPER, which takes text as an argument and formats it so that the first character of each word in the text is capitalised, it works like this:-

=PROPER(“this is my example text”)

and the result is:-

Proper Function Excel

It’s a pretty useful function.  I wanted to carry out similar text formatting in a VBA routine I’d written in Access, but didn’t want to reference Excel and then use Application.WorksheetFunction to call it.  That’s where the incredibly useful StrConv function in VBA steps in.

StrConv looks like this:-

StrConv(string to convert, conversion type)

The conversion type argument is one of a selection of constants which carry out various text manipulations.  Here’s a list of them and what they do:-

vbUpperCase – converts the entire string to uppercase
vbLowerCase – converts the entire string to lowercase
vbProperCase – capitalises the first letter of every word in the text string

There are some others, but I’m not going to discuss them as I’ve never used them.

So..to convert our text string using VBA, it would look something like this:-


StrConv Example
It’s that easy – no need to over complicate things!

Tips Visual Basic For Applications (VBA)

Microsoft Listview Control – a funny little bug..

For some reason I can’t yet fathom, the Microsoft Listview control (version 6.0) occasionally really gets messed up on my computer and I can’t access the properties for it by double clicking or selecting ListViewCtrl Object>Properties from the drop-down menu.

This is, like,  well irritating, as the kids would say.

I did, however find a way around it so that the properties can be re-activated as it were – which means I don’t have to delete, and then re-create the listview control every time this happens.

The solution is straightforward:-

  1. Right click on the listview control in design mode
  2. Click on ListViewCtrl Object>Convert..
  3. From the options, choose ‘Convert To’ and make sure ListView Control, version 6.0 is selected.  The text at the bottom of the dialog will tell you that nothing will happen and that the control won’t be converted.
  4. Click OK and you should have regained control over your control!

There is, however one other thing you should do if you have this problem, and that is to ensure that, if your listview is populated using VBA you use the ListItems.Clear method to clear the contents of the control before populating them.  This might seem like an unusual statement to make, as there should be nothing if the control has not been populated, but there is another element to this bug which, for some unknown reason pre-populates the control with whatever the contents were before you made a change and saved your form.  This is not always the case but it’s certainly worth as a precaution and to ensure that the control acts properly on all users machines.

Outlook Visual Basic For Applications (VBA)

Outlook: VBA code to display a random email from your inbox

Got loads of emails you need to action but don’t know where to start?  Run this outlook vba code or attach it to a menu item/button and it will display a randomly chosen email from your inbox.  I find this quite useful when I’m looking at the endless list of emails I don’t want to deal with – I just run the routine and make sure I deal with whatever email appears thrown at me.  It makes working through my email list more fun, too!

Sub ShowRandomEmails()

Dim myOLApp
Dim myNameSpace
Dim myFolder
Dim myItem

Set myOLApp = CreateObject("Outlook.Application")
Set myNameSpace = myOLApp.GetNamespace("MAPI")

Set myFolder = myNameSpace.GetDefaultFolder(olFolderInbox)

Dim intRandom
intRandom = Int(Rnd() * myFolder.Items.Count) + 1

Set myItem = myFolder.Items(intRandom)

End Sub