Bacon Bits:

Let’s face it, Access isn’t typically a tool you would think of when considering a dashboarding platform. The reporting tools in Access, as slick as they are, don’t lend themselves to visualizations.

That is, Access doesn’t offer a whole lot in the way of dashboarding graphics. Sure, it has charts and conditional formatting, but nothing like Excel.

In this post, I’ll show you a hack to spruce up your Access reporting by using your own dashboarding graphics.

Step 1: Create your graphics

First, you’ll need to decide which graphics to use. The idea is to convert your graphics into bitmap files and place them into a directory.

I used some VBA to strip gauges from the Google Charts API and place them into bmp files. Don’t ask for the code because it was an ordeal involving the Web Browser control and SnagIt API calls.

Anyway, I have 100 bmp files numbered 1 through 100. Each file number corresponds to a value from 1 to 100.  So file 50 represents 50%, file 40 represents 40%, etc.

Step 2: Create table to hold your graphics

The next step is to create a table with three columns:

• A number column that will hold numbers 1 – 100.
• A Pcnt column that will hold .01 – 1
• An OLEObject column that will hold the Bitmap graphics.

Step 3: Get your graphics into the table

There are lots of ways to get your graphics into the table. I personally used some VBA to suck them in (that’s for another post).

The easiest non-VBA way is to drag them into the OLEObject field. Simply drag the graphics into the appropriate record.

For example, the bitmap file titled 40 would go into the record where the ValueNum field is 40 and the ValuePcnt field is .40.

When all is said and done, you should have a table where each number and percent value corresponds to the appropriate bitmap file.

Step 4: Create your Query

Once you have your “DashboardGraphics” table, you can link it to any analysis using the ValueNum or ValuePcnt columns.

The OLEObject column (the column that holds the bitmaps) in this example is called “GaugesHiGood”. As you can see, the GaugesHiGood column is brought into the query.

I’ve saved this query as Query1.

Step 5: Create a Form or Report from your Query

I used Query1 to create this form. I brought in all the fields from my query – including the GuagesHiGood field (the OLEObject field)

Here is the same Form in Continuous Form view.

Step 6: Fill your Graphics table will all kinds of stuff.

You can have more than one OLEObject field. That means you can have all kinds of different visual representations of 1 – 100.

I order to avoid a verbal thrashing from Jon Peltier (who hates gauges), I’ve decided to add some progress bar images to my graphics table.

Here, I’ve changed Query1 to pull my new ProgressBarHiGood OLEObject field.

There you go Jon…death to gauges.

A Note about Embedding Graphics:

I know that most Access gurus say that it’s not a good idea to embed images into a table. They say it bloats the database and causes performance issues.

The truth is, these graphics are so small they don’t really impact the size or performance of the database. I’ve got a table with 600 dashboarding graphics and it added only 10 megabytes.

As an alternative to embedding your graphics into OLEObject fields, you can use a text field that holds the paths/URLs for your graphics. Then you can make Access dynamically call up your graphic using that path. I can cover that in another post.

Here is a sample file you can play with: Download DabaseGraphics Sample File

• Previous Entry: Adding Pictures to Your Buttons
• Next Entry: Smoked Gouda, Apple and Bacon Grilled Cheese