SkillZDesign.com

Here are a couple of not-so-very-well documented tips for MS Access 2000. I haven’t tested them on any other versions of Access, though I suspect most of them should work on later versions.

These tips require the use of the Microsoft Visual Basic Integrated Development Environment (IDE), which can be accessed by Alt-F11.

Use the Immediate Window

The Immediate Window can be accessed via Ctrl-G (or View - Immediate Window). It is an invaluable asset when debugging or testing. I’m not a programmer. I like to say I know just enough programming and scripting to be extremely dangerous. Despite this, I’ve saved myself days, possibly weeks, of time and frustration using this tool.

Use the Question Mark (?)

I discovered this by accident, but I remembered back in the old days programming Basic on my Atari 400 that the question mark character was shorthand for “Print.” As it turns out, VBA is a distant cousin to the old Basic. You can still use the “print” keyword, but the question mark is your friend. (Note that the question mark will be converted to the “print” keyword automatically in the Code Editor.)

Get Full Path and Connection Information to Linked Table

This can be a huge time saver, especially when working with databases that have a lot of linked tables and those with long path names. Sometimes the Linked Table Manager utility just doesn’t cut it.

? currentdb.TableDefs("tablename").Connect
or
print currentdb.TableDefs("tablename").Connect

Try it. You just might like the results.

Incorporate Custom Functions in Queries

This one might scare some people, as it requires some hand coding of SQL. That means leaving the comfort zone of the visual Query Designer and entering the land of Structured Query Language. It also requires some hand coding of VBA. This can be terrifying. Once you’re over the initial fear, however, you’ll be pleased to know that it’s pretty easy to get started.

Let’s take a sample table. We’ll call it tblPeople. Please note: The prefix “tbl” is a standard naming convention, but I won’t get into that here. I’d highly recommend the VBA Developer’s Handbook - (buy it here) for anyone interested in getting their hands dirty with that sort of thing. We have three fields we’re concerned with: fname, mname, and lname. We have three records:

Suppose we wanted these names to print out in a mail merge-friendly format. One method would be to hard code the logic in SQL. My personal preference is to write custom functions for tasks like this, as you end up with more readable and maintainable SQL. For instance, here’s the function:

Function fnNameForMerge(strFName As Variant, strMName As Variant, strLName As Variant) As String
fnNameForMerge = strFName & IIf(strMName = "" Or IsNull(strMName), " ", " " & strMName & " ") & strLName
End Function

We can now test the function using the Immediate Window:

Once satisfied, we can now use the function in the following SQL statement:

SELECT tblPeople.fname, tblPeople.mname, tblPeople.lname, fnNameForMerge(fname,mname,lname) AS NameForMerge
FROM tblPeople

This can also be done in the Query Designer:

The results look like this:

The more I use Access, the more I tend to dig into the SQL code. I’ve found myself leaning towards custom functions whenever my SQL starts getting messy, particularly when logic starts creeping in, such as in the example. Even if you’re a SQL-phobe, consider that it’s still pretty easy to use custom functions in the Query Designer. You will, however, need to acquaint yourself with Modules.

Don’t let VBA scare you. You don’t have to be a “programmer” to mess around with some code. It just might save you some time or even add quality years to your life. I know it has for me.

My Rant Against Anti-Automation

Unfortunately, there seems to be a tendency within organizations to disable macros due to the proliferation of malicious code. This inevitably means more people doing more manual labor, typing things they’ve typed hundreds of times before, moving the mouse and clicking on things over and over, inching ever closer to Repetitive Stress Injuries. It’s a shame. The last I checked, computers were supposed to automate things. Personally, I’d rather take the risk and encourage people to experiment, to unleash their Inner Automator. Repetition is evil - unless you’re actually LEARNING from the exercise.

If you’ve found any of this useful, please feel free to drop me a line or comment.

Tags: General, Scripting is Cool, Technology //