A Utility to Help With Ensuring Uniform Control Width on Forms and Reports

Tony Toews has posted a neat little utility that grabs the control widths and a few other details for all the controls on your forms to help you make sure that you've sized things consistently throughtout your application. This will be especially useful when you expand a field and have to change all the controls it's used in.

See Ensuring uniform control width on forms and reports

A Ribbon Class - Another approach to using the new ribbon in your applications

Much has been written about the new "Office Fluent User Interface" that has been introduced with Office 2007. Also known as the ribbon, it's a whole new way to work with Access, Word, Excel and Powerpoint, replacing the menus and command bars we've all become accustomed to.

Like them or not as a user, Access developers have to deal with the reality that the custom command bars they have been using in their applications the past don't work the way they used to.

In this article Albert Kallal presents a neat approach to solving the problem of having your application interact with the ribbon. He's created a ribbon class which uses methods similar to the old command bar methods to operate on icons within the ribbon. I haven't tried using it in an application yet, but I ran the demo and it looks pretty neat.

See also: my other ribbon posts.

What is the Fastest Way to Return the Record Count of a Table?

Roger Carlson has posted an interesting analysis of the various ways to get the record count of a table. Take a look here.

Treeview Example- Filling Recursively, Populating With Names of Folders

Here's another working Treeview example from the code archive at UtterAccess.

Quote: This little database illustrates the implementation of Microsoft Knowledge Base Article Q20981 (How to Fill a Treeview Control Recursively). In addition, frmFolderExamples illustrates one way to fill the Treeview Control with the names of the folders located on a drive. It also shows how to return some of the properties of the treeview nodes as well as how to clear a treeview. I use some code harvested from Candace Tripp’s Browse and Open Files Database as well as ideas from Luiz Cláudio’s Treeview Control for Database Objects.

Roll-Your-Own “Traffic Light” Icon Set

Quote: Here’s a great tip suggested by Clint Covington. Using an expression and a Rich Text text box, you can create a traffic light indicator in an Access form or report, as in the following illustrations:

I really like this use of a Rich Text text box. We used it in my shop successfully- I think it was the first Access 2007 specific feature we used. (Rich Text text boxes became part of Access in the 2007 version, I think.) Take a look at http://blogs.msdn.com/access/archive/2009/04/16/roll-your-own-traffic-light-icon-set.aspx

This solution gives more options for colouring controls on your forms and reports than conditional formatting does.

Using Right-Click With Treeview Nodes

Lets say you have a treeview and you want a user to be able to right-click a node and then be able to either delete it, copy it, or view details about it. How do you trap the right-click event? How do you show a context menu? And how do you know what node was clicked?

There's no OnRightClick event for Treeviews- that would have been the easiest solution. There are MouseUp and MouseDown events that know what mouse button was pushed- maybe that would help.

It turns out the secret is MouseUp.

Read more »

Programming The VBA Editor

Programming The VBA Editor describes methods for programmatically interacting with the VBE editor. Quote:"You can use these features to write custom procedures that create, change, or delete VBA modules and code procedures." The article is written for Excel but I think it should be adaptable to Access. (I haven't tried it yet- if you try it let me know how it works!)

Use WShell to Retreive the Path to Windows Special Folders

Sometimes in your program you'll need to find the path of the user's MyDocuments folder, or to the Desktop, or to the Favorites folder. Because different versions of Windows will place these folders in different places, it's important to ask Windows for the location of the file, instead of hard coding it or figuring it out from the user name.

This post describes a method for using the Windows Script Host to retrieve these names. The Microsoft documentation is here: SpecialFolders Property.

Here's an example of how to build a VBA function that uses this method within MS Access to find the path to the user's MyDocuments folder.

Read more »

Decompiling an Access Database

Sometimes your database starts to exhibit unexplained behaviour- something that used to work suddenly stops working, or seemingly inexplicable errors appear. Or sometimes the database gets larger than it used to be (even after compact/repair) or starts to load much more slowly.

One of the solutions often recommended is to decompile the database.

Read more »

The world's largest collection of connection strings on the Internet

Ran across this today: The world's largest collection of connection strings on the Internet.

If you want to use ADO you need connection strings. And if you want to access something outside your local Access database you may need to find the right syntax for the particular type of data you want to connect to. Carl Prothman has compiled sample connection strings for everything from Jet to AS/400 to MS Project to Oracle to.... You get the idea.

The Normal Forms in a Nutshell

Good database design is key to success with application development in Access. The principles for database design in Access are the same as they are in any relational database, and one of the core principles is normalization. Within normaliztion there's a concept called "normal forms". Roger Carlson recently posted a series of articles that explain the normal forms. Take a look. It's a great way to get an introduction to this important concept.

Accessing external data using the IN clause

Accessing external data using the IN clause. This article on the Microsoft Access Team Blog shows some of the ways to access data from outside your database by specifying the source of the data in the "In" clause of your query.

Quote:The other use of the IN keyword is as part of the SELECT statement or FROM clause, and is called the IN clause. The IN clause is used to access external data, and can be used in place of linked tables. For example, let's say that you had a SQL Server database that is used as part of an application, but you don't want to maintain a DSN to connect. You could use the IN clause in a query that uses a DSN-less connection to quickly read data from the external table.

It's one of those weird and wonderful things you can do in Access that many developers may never have tried!