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.

1. “Let Me Preface This By Saying…”

…that I like to say this to justify everything else I’m about to say. A fine example would be: “Let me preface this by saying that I have refused to listen to a single word you’ve said during the last hour of this excruciating meeting since it has clearly been a complete waste of my valuable time. Having said that, I suggest we setup a follow-up meeting to discuss what has been said here with the primary focus being clarification of said goals.”

2. “Having Said That…”

…let me restate that I’ve just said something of vital importance, so important in fact that it needs to be reiterated. Let me point out that it was, in fact, said. The words resonate in such a way that our discussion needs a moment to do nothing but ponder the sheer weight of their implications.

3. “That Said…”

…we can acknowledge the abbreviated version of the prior phrase, “Having Said That,” thus saving valuable fractions of a second whilst continuing to pollute our conversation with meaninglessness. Unfortunately, this provides us with precious little time to frantically think of something truly meaningful with which to follow up.

4. “You Know…”

…is the age old filler phrase which, when properly and abundantly utilized conveys to the audience an utter lack of concern for or understanding of anyone or anything. No, I don’t know. We’re supposed to be having a conversation, one in which I am in dire hope that you will shock and amaze me with your vast knowledge of a subject with which I could never have imagined being so intrigued, that is, until you provided me with this enchanting, illuminating explanation. Such depth and painstaking elaboration can so easily be avoided by these two infinitely presumptuous words: “You Know.”

5. “Wrap My Mind Around It”

Please do not attempt this, for it can only end in an emergency room somewhere, at which point you’ll be in no position to delicately explain your predicament. The upside is the psychoactive medication you may quite suddenly have legal access to. Avoid the antidepressants which take two weeks to “get into your system.” Two weeks is a long way down a road you’d prefer not to crawl on your knees. Whatever you do as you’re trying to “Wrap Your Mind Around” anything, do not, under any circumstances, “Get Your Head Around It.” You’ll find yourself in a different emergency room with less sympathetic orderlies who will be more than happy to staple your scalp back together like a shag carpet.

6. “Low Hanging Fruit”

This one goes out to my homies in an environment where such phrases can and will be interpreted in the most deviant of ways. Imagery will infect the minds of those so close to the edge that merely seeing those words typed in a large font in front of them could result in a month long rampage of debauchery worthy only of a Hunter S. Thompson novel. This is particularly risky now that Hunter S. Thompson is no longer with us to provide some semblance of sanity and hope to the proceedings.

7. “Man Alive”

I have yet to research this one. All I can say is that hearing it in the midst of a conversation in New York City or Long Island makes me feel like maybe I should have devoted at least a part of my upbringing tossing bales of hay in Iowa, or Idaho, or Kansas even. I don’t know if it’s a politically correct phrase designed to offend no one while still expressing shock or disdain. Color me offended by this one, so mission not accomplished.

8. “This is the Expectation”

This is my middle finger. It will get done, and if it doesn’t, it might have something to do with the fact that we are human. The harder you push, the less anyone wants to do anything for you. Please feel free to e-mail your grids, write them very legibly on your whiteboards, or better yet, detail them in voicemails at least five minutes in duration. I will promise to respond via Blackberry, while driving a stick shift in traffic, a novella describing each and every milestone which has been rendered obsolete due to the rapidly changing nature of our business, so we can waste no more time moving forward to the newly improved, lengthier set of expectations.

9. “The Take Away from This…”

…is that the more people you pack into a room for any specific purpose, the less likely that purpose will get accomplished in any reasonable length of time. The hope is that the attendees are folks that perform a valuable function within the organization, some perhaps perform multiple valuable, yet unsung, functions, due to their inherent nature to do what is ethical and preserve the organization for which they labor. The take away is most often cynicism due to a meeting such as a conference call between five to ten top level executives who spend 90 percent of the call holding down the mute button. Instead of a “Yes” or “No” you’ll hear a half-second of static to indicate that someone is listening to something. That keeps a conversation moving at break-neck speed. The take away is…

10. “We’re In Our Busy Season”

Congratulations. You now have full authorization to expect your associates to work round the clock. Surely their families will understand, after all, we’ve known about busy season far in advance. They have had plenty of time to restructure their lives around the needs of the organization. Some will celebrate Christmas or Hanukkah a month later. People will look at them strangely when they light a Menorah in April or try to cut down a Douglas Fir in August, but so be it. Their families, especially their preschool-aged children, have an implicit comprehension of the intricacies of corporate responsibility.

11. “Ethics Training”

I’ve needed this for over forty years. It’s an absolute revelation. Finally, I can dig up the old phone numbers of people I’ve wronged and explain that I had lacked the proper training which prohibited me from treating them like inferior beings. They’re still far inferior to myself, but there have been such advancements in the language for communicating this to the masses that I now believe I can significantly increase the percentage of my acquaintances that no longer view me as a mortal threat to their significant others, or themselves, or their miniature dogs that still think they’re dogs. Sure, there have been the occasional disappearances, but the place is quieter, peaceful even. I might even start wearing the tie.

12. “Privacy Practices”

Since you’re reading this, I’ll take a leap of faith and assume that you’re not receiving this post via psychokinesis. The notion of privacy is ludicrous, yet extremely profitable. Lawyers can keep this going in circles indefinitely. You’re on a computer. You complete various transactions on a computer. You might very well be connected to your entire life savings via a computer, or most likely a series of interconnected computers. Throw a couple of paragraphs up there that were composed by some folks claiming to be lawyers and rest easy. There really aren’t armies of organized groups in countries like Romania and Russia paying legions of people to acquire your personal information via the Internet, or if there are such nefarious activities, that legalese will protect you… or will it? Who is it designed to protect?

“In Conclusion…”

There is no conclusion. There will always be phrases that piss me off. I find myself saying things, and eventually they piss me off. I can be so damned repetitive, especially when I write. On the off chance that I write something I perceive as somewhat witty or might make someone laugh, I’ll stash it in my bag of tricks and plagiarize myself at a strategic moment. Who’s going to know? What will they say? “You unoriginal bastard, you just wrote that on your blog weeks ago. Can’t you come up with anything better than that?” Or maybe they’ll just laugh, preferably a large group of them, at the same time, in a genuine “that was really funny” manner, not that “this guy is a joke” kind of way. Days later, one of my killer catch phrases might just pop up in a meeting and become the mantra for that next huge initiative which will slaughter the competition provided the team can get their heads around it. You can’t be overly optimistic under the circumstances, as the majority can’t manage to punch a timeclock correctly.

I believe we’ve located the menorah. It’s been in our storage unit since we moved. We will probably light all the candles on the last night. I’ve been told, ‘You’re a good Jew.” This was at a time when I was at a gathering at my sister’s house. A friend of hers was having some difficulty with her digital camera, so I sprung into action. Afterwards, she said to me,”You’re a good Jew.” I think she was some high-ranking official at my sister’s synagogue. I was just helping someone out, and there she was slapping a label on it. When we finally get around to lighting the candles, I’ll be doing it more for the enjoyment of the children and my wife than anything else. If that makes me a good Jew, so be it. I’m just trying to celebrate.

Here’s a follow-up to the birthday festivities…

[youtube kbxpBiD5Ays]

The dream gift was originally intended for Ava: safety scissors shaped like a cute, friendly, innocuous beast which she could use for her endless arts-and-crafts projects, such as cutting up important government documents, credit card bills, bank notifications, etc. while her parents would guzzle cheap rot-gut whiskey and whine and laugh about the great old times when worry would kill you quicker than anything. Nikki took Gia out into the world on a quest for such treasure as would bring the light of glee into the eyes of our now-two-year-old, a quest which would end up in the bowels of holiday weekend shopping hell.

The beacon appeared towards the end of the excursion, and it is pictured here in all its packaged brilliance. What better offering to your innocent daughter than a friendly, inoffensively colored mallard to assist you with your paper shredding, chopping and older sister-threatening daily chores, particularly when it boasts ‘Shark Sounds when you cut!’ and one can only hope ‘Here’s to Swimmin’ with Bowed-Legged Women!’

The question remains, as we are compelled to ‘Also look for the Duck Scissors’ on the rear of the packaging, is whether the Shark Scissors quack. I might have to hunt down a pair.

[youtube f-TYvNRq6Qo]