Andrew Wrigley Gold Collection
Andrew Wrigley has more than a few tips to make you more productive when working in Access.
I sometimes think that my productivity can be measured in the number of buttons and menus that I don't click. Here are several ways that you can speed yourself up by simplifying some common developer tasks.
An alternative way of building custom toolbars
Note: Command Bars are tricky to use in Access 2007 Garry Robinson 2009
Good form design improves the usability of applications, so I spend a lot of time tweaking the layout of my forms and reports: sizing controls, changing horizontal and/or vertical space, send to back, send to front, and so on. Each of these commands is nested under the Form Designer's Format menu. Unless you can place the relevant menu commands onto a toolbar, such tweaking involves a maddening succession of clicks as you trudge through the Designer's Format menu just to get at the menu items you need.
It's easy, you think, to create a custom toolbar to hold these commands. However, you've no doubt already discovered that one of the more curious (and annoying!) shortcomings of Access is that many of the Format menu commands are simply not available in the Customize Toolbar dialog, nor is there a built-in toolbar with the commands you need (don't be fooled by the Alignment and Sizing toolbar–it doesn't apply to Forms and Reports).
To make your life even easier, I've included my own ControlFormatting toolbar in the download for this article that can be imported into Access. However, there's a simple workaround that will let you build your own toolbars. This workaround provides a simpler way of designing your own custom toolbars:
1. Open a form in design view or create a new form in design view.
2. Right-click in the menu and toolbar section of the Access window.
3. Choose Customize... from the context menu that appears. This will open the Customize dialog that allows you to modify existing toolbars or create new toolbars.
4. To create a new toolbar, click the New... button to the right of the dialog. You'll be prompted for a name for the new toolbar. After assigning a name, an empty toolbar will open as shown in Figure 1.
So far, you may be familiar with this process. Traditionally, you'd now go to the Commands tab of the Customize dialog, choose a menu in the left pane, choose a command in the right pane, and drag the command onto your new toolbar. Because some of the commands I want on MyToolbar are nowhere to be found in the Commands tab, I have to do things a bit differently... but by all means use the commands that can be found in the Commands tab, such as send to back, left and right align, and so on.
As an example, I'll show you how to add the "To Tallest" command to your new menu, though it can't be found in the Customize dialog. Without closing the Customize dialog, do the following:
1. Click the Format menu on the main menu bar of the Form Designer.
2. Click the Size submenu. This will display the Size submenu, which holds the commands I want to add to the custom toolbar.
3. Because the Customize dialog is open, you can now drag "To Tallest" to your new toolbar.
4. Go back to the Format menu and click Size. As you can see, "To Tallest" has disappeared from the submenu. Don't worry, you can reset the Format menu later.
You can now drag as many options as you want to your new menu. For instance, after dragging "To Narrowest" to MyToolbar, your menu should look like Figure 2.
The other benefit of this technique is that it makes it easier to find the commands you want because (presumably) you're already using them on their current menu. However, as you can see in Figure 2, the drawback is that you get the menu text on the toolbar, rather than an icon. I'll show you how to change this later.
When you're done, you'll want to reset the Format menu so that it still has all of its default commands that you know and love. With the Customize dialog still open, right-click the Format menu on the main menu bar and choose Reset, as shown in Figure 3.
If you now look at the Format | Size submenu, you'll see that the two missing commands are safely back in place.
As I noted earlier, the only problem with your new toolbar is that the menu items are displayed as text rather than as graphics, which is cumbersome for a toolbar. This is easily fixed:
1. Right-click on each button on MyToolbar and select "Change Button Image."
2. Select an image from the list of choices that appears. You now have both an image and the text on the toolbar.
3. Your last step is to right-click each Item on your toolbar and select "Default Style." This sets the item to display only the image.
Alternatively, you could choose "Edit Button Image" and roll your own button image, as I've done in the ControlFormatting toolbar included in the download file for this article. You can now close the Customize dialog and you have your own menu bar.
Adding shortcuts to the Start menu
As long as you're making yourself more productive, why not make your users more productive too? It's always a good idea to provide shortcuts for your users on the Start | Programs menu. This not only makes your app more user-friendly, it also makes it look more professional, as it sits shoulder to shoulder with the "big boys" such as Microsoft Access itself.
To get the shortcuts where you want them, you can either use a professional installation script, like the one provided by the excellent SageKey scripts (www.sagekey.com), use the Access Workbench or do it manually. Using the SageKey scripts is the standard professional approach, but it involves cost and a considerable learning curve. As this article is focused on providing quick-and-dirty solutions to everyday problems, I'll only cover the manual option.
Adding shortcuts manually isn't difficult. All you need to do is create a folder to hold the shortcuts you want to provide to your users. Name the folder as you want it to appear in the Start | Programs menu. Next, copy the folder with your shortcuts into the folder where your computer reads what will appear in the Start | Programs menu. This folder is located under the Documents and Settings folder, which is usually found on the same drive as the Windows installation folder (see Figure 4).
Please note that the Documents and Settings folder may be organized differently according to the version of Windows that you're using. In what follows, I'm referring to a Windows XP SP2 installation. The differences between versions aren't great, so using XP is a "good enough" illustration that will point you in the right direction.
Under Documents and Settings, you'll see the All Users subfolder, as well as other subfolders for each user on your PC. If you want all users on the PC to have the shortcuts, use the All Users subfolder. If you now open the All Users subfolder, you'll see that it contains various subfolders, including Desktop and Start Menu. Under Start Menu, you'll find the Programs subfolder that you're looking for.
All you need to do now is copy the folder you created into this Programs folder.
If you wanted a shortcut to appear on all users' desktops (instead of the Start menu), you'd copy it into the Desktop subfolder of All Users. This is a really simple way of making your users feel that your app is a professional bit of software as well as making it much easier for them to use.
A professional version of my update and versioning tool that can be downloaded as the sample for this article will be available from our Web site (www.wingspan.info) by about May 2005. The professional version will automatically generate and place the shortcuts where you want them, be it under the Start | Programs menu, the Desktop, or wherever, so watch this space!
Packaging command line switches
It's all very well to set up shortcuts for your users, but don't forget yourself! There are loads of tasks that you regularly perform on your apps that involve a tedious and repetitive succession of clicks–for example, decompiling an .mdb file that has VBA code, compacting a back-end .mdb file, and opening an .mdb file in exclusive mode to change a password.
What these tasks all have in common is that they involve the use of command line switches that can be used with Microsoft Access (see www.wingspan.info/AccessSwitches for more information on these switches). To perform these tasks, you have to click on the Start menu then Run, type the path of the .mdb file you want, and then type in the relevant command line switches you want to use before you hit the Enter key. If you have more than one version of Access installed on your development machine (say Access 97, Access 2003, and the Access 2003 Runtime), you may need to type into the Run dialog not only the path to the file itself, but also the path to the Access executable that you want to use. Getting everything right every time is at least tedious and more likely an error-prone nightmare.
The good news is that you can package these switches into convenient shortcuts. You only have to build the shortcut once and then, when you want to perform the given task for a given file, just double-click the appropriate shortcut. It gets better: My update and versioning tool included in the download file will automatically build some of the more frequently used shortcuts for each of your apps as a byproduct!
However, here's how to set up one of these shortcuts manually:
1. Start by browsing to the .mdb file that you want to use in your shortcut.
2. Holding down the Ctrl and Shift keys, drag and drop the .mdb file to your desktop (or wherever you please). This creates a shortcut to the file itself.
3. To add the command line switches, right-click on the shortcut and select Properties from the context menu that displays. This opens the Properties dialog for your shortcut.
4. The first change you want to make is to add the reference to the Access executable you want to use (as stated earlier, this avoids conflicts with other versions of Access that may be installed on your system). To find the path to the executable, you can use the SysCmd method in the VBA command window, passing the acSysCmdAccessDir parameter and the string "msaccess.exe" like this:
? SysCmd(acSysCmdAccessDir) & "msaccess.exe"
On my laptop, the result was the path D:\Program Files\Microsoft Office\OFFICE11\msaccess.exe.
5. Enclose the path in double quotes and copy and paste it to the beginning of the Target textbox in your shortcut (that is, before the path to your .mdb file). You should now see something like the example in Figure 5.
6. Click the Apply button to save your shortcut (this is where your command line will be checked for any errors).
7. Close the Properties dialog to save your shortcut.
In addition to creating a shortcut to your Access database, you now have a template for generating all of your developer shortcuts.
As your shortcut now targets the Access executable, rather than an .mdb file, you'll find that the icon has changed. You can set the icon image to any .ico file you want, but for developer shortcuts I prefer the Access executable icon.
At this stage, the shortcut will do nothing more than open the .mdb file. To get extra functionality, you need to add the command line switches that will implement the functions that you want. For instance, to use the undocumented /decompile switch that's so useful for decompiling .mdb files that contain VBA code, you can add that switch to the Target.
First, make a copy of your developer shortcut template and, once again, open its Properties dialog. Now, just add the /decompile switch to the end of the text in Target box: "D:\Program Files\Microsoft Office\OFFICE11\msaccess.exe" "E:\GetShorty.mdb" /decompile. Click the Apply button and close the Properties dialog. Voilà–you now have a shortcut that will decompile your app for you.
Some of the other switches may have special requirements and parameters. For instance, the /wkgrp switch takes as a parameter the path to the workgroup file you want to use for your file. However, they're just as easy to use as the /decompile switch, and using them in developer shortcuts can save you oodles of time and tedious clicks.
If you're like me, the frequency of your mistakes increases exponentially when you're performing repetitive tasks. Therefore, anything that standardizes my development workflow and anything that reduces tedious and menial clicks and keystrokes is pure gold dust. The tips described in this article are all simple and straightforward to use. Also, if you work in a small to medium office environment, you may find the update and versioning tool useful to develop a strategy for uploading updates out of development quarantine to a location where they can be distributed to your users' PCs. It may just spare you from doing what I once did: deleting the file that a colleague was working on...
Your download file is called 505WRIGLEY.ZIP in the file SA2005-05down.zip
This is found in the Gold Collection on this page