I will blog on this page about computer related topics like Microsoft®, Access, well as post things of personal interest. I will try to keep the blog as current as possible, so please check back every now and then. Entries are in order from newest to oldest from top to bottom.

RSS Feed

December 2008 - 8 Golden Rules of Interface Design

December 2008 - Custom Freakazoid Action Figure

December 2008 - AutoCorrect behavior in 2007 Access Runtime

December 2008 - YouTube Video to MP3 audio

December 2008 - Using Google Maps Api and Evaluating Flash CS4 Pro

November 2008 - Missing "Create Shortcut..." Option in Acc2007

November 2008 - SQLite: a good file based RDBMS alternative for web apps

November 2008 - Oil Painting

October 2008 - Happy Halloween

October 2008 - Death by chocolate

October 2008 - The future of transportation

September 2008 - Access 2007 Web 2.0 sample form part I (video)

September 2008 - Java/Access 2007 sample applet (demo)

September 2008 - PHP/Access 2007 sample site (demo)

September 2008 - Coast to Coast

September 2008 - Hug a developer

August 2008 - Unexpected error with Email Data Collection?

August 2008 - Access Data in Email Body

August 2008 - The Square Root Of Three Poem

July 2008 - So You Think You Can Prance?

July 2008 - Nintendo Wii vs. Microsoft Xbox 360

July 2008 - Tired of MP3/Music DRMs?

June 2008 - Monopoly Groom's Cake

May 2008 - My Microsoft Surface Experience

April 2008 - Birthday Cake #4

April 2008 - Microsoft MVP summit

March 2008 - Microsoft Access 2007 Runtime updated

March 2008 - Access MVP T-Shirts

March 2008 - Combobox backcolor randomly changing (Acc2007)?

February 2008 - Sony to win at least one format battle?

February 2008 - What's up with shuffling?

February 2008 - Access 2007 making your life easier

February 2008 - Export report to Excel in 2007

Februrary 2008 - Screenshots of Ribbon application

January 2008 - AutoFormats not applying correctly?

January 2008 - Including Compact and Repair in Runtime distribution

January 2008 - In Loving Memory


January 2008 - VBA will be in Office 14


January 2008 - Birthday Cake #3


January 2008 - 10,000 visitors on


January 2008 - Microsoft Access MVP for another year




December 2008 - 8 Golden Rules of Interface Design

This past semester I had the priviledge of having a main contributor to the HCI community as the professor for my Human Computer Interaction course. His name is Ben Shneiderman and one of his famous concepts is his notion of 8 golden rules of interface design. These rules are mentioned in his book Designing the User Interface: Strategies for Effective Human-Computer Interaction. I thought they are pretty good and universatile and can probably be applied to all levels of interface design e.g. Access Forms/Repors, websites, mobile apps...I will only shortcut reflect on the rules so you all get the main ideas:

1) Strive for consistency:
- Consistent sequence of actions in similar situations
- Identical terminology in prompts, menus, help screens
- Consistent color, layout, capitalization, fonts...
- Exceptions (e.g. required delete command confirmations) should be limited

2) Cater to universal usability:
- Design for plasticity
- Recognize novice-expert, age, disabilities, and technology differences

3) Offer informative feedback:
- Every user feedback requires system feedback
- The more freaquent the action the larger the response

4) Design dialogs to yield closure:
- Sequence of actions should be organized (beginning, middle, end)
- Confirmation page at the end give sense of accomplishment, relief...
- E.g.'s checkout process

5) Prevent errors:
- As much as possible
- Allow no serious errors
- If error occures then interface should detect it and offer simple, constructive, and specific instructions for recovery

6) Permit easy reversal of actions:
- As much as possible
- Relieves anxiety since errors can be undone (encourages exploration)

7) Support internal locus of control:
- Experienced operators desire sense of being in charge
- Reduce surprising interface actions, tedious sequences of data entries, inability to obtain or difficulty in obtaining necessary information

8) Reduce short-term memory load:
- Short-term memory limitation (rule of thumb = 7 +/- 2 chunks)
- Requires displays to be kept simple, multiple-page displays to be consolidated, window-motion freaquency to be reduced, sufficient training time to be allowed

December 2008 - Custom Freakazoid Action Figure

My wife gave me the best early christmas present ever. I have been looking for a Freakazoid action figure forever to add to my collection but was never able to find one. They never made any merchandise when the show was on during the 90s and they didn't revamp any further efforts to bring anything out when the DVD collection was released early this year. So she took it in her own hands and contacted a few custom action figure artists. One actually responded and took on the challenge. The artist's website can be found here and check out the final result in the following image:

December 2008 - AutoCorrect behavior in 2007 Access Runtime

A fellow Access MVP has recently noticed that the AutoCorrect feature seems to be enabled by default within the Access 2007 runtime. He notice this because of the little flash icon button appearing in combo and textboxes (check image).

autocorrect options button

The icon appears if the following settings are set within Access under "Office Button--Access Options--Proofing Tab--AutoCorrect Options...--Show AutoCorrect Options Button" and you incorrectly type or format the typed content.

There are a couple of different ways to get around this behavior for your application within the Runtime edition. If you are starting an application from scratch and don't want any of the AutoCorrect features to effect some or all combo and textboxes then set these control's AllowAutoCorrect property to false. If you don't want to set the option for each control or you don't want to completely disable all of its functionality and just hide the earlier mentioned little AutoCorrect Option button, then you can use this property of the Application object in a startup form:

- DisplayAutoCorrectOptions (show/hide the flash icon button when an autocorrect occured)

You would set it as follows: Application.AutoCorrect.DisplayAutoCorrectOptions = False

December 2008 - YouTube Video to MP3 Audio

I recently was looking for a way to capture a YouTube video into a shareable format. My wife had been looking for a rare song that she used to have on LP when she was little. We couln't find it anywhere on the web as an mp3, but did find a YouTube video that someone submitted of a recording of the song. So it was up to me to get it into a format that she can use on her Zune. I was thinking of a bunch of different approaches including a decompilation of the FLV (flash video file) after grabbing it with one of the many FireFox extensions that can retrieve embedded media within a website. However, none really worked as well as I would have liked and it is hard to find a decent free flash decompiler on the web. While doing a search for one I actually stumbled upon this great website, which allows one to actually upload the FLV file and converts it into an MP3 or even easier it lets you provide the URL of the file. The resulting MP3 is of pretty good quality and enough to make my wife happy :). I thought of sharing this here in case anyone else is in dire need of retrieving some of these lost treasures that can't be found anywhere else.

December 2008 - Using Google Maps Api and Evaluating Flash CS4 Pro

While evaluating Flash CS4 Pro (which was just recently released) I figued I'd play around with the Google Maps API for ActionScript and put together a quick sample application. The project uses a dynamic database to store items and their location, which then can be searched and displayed within a mapping result feature. Check out the animated gif at the end of this post to get some idea of what all you can do with the free service provided by Google. The ActionScript API reference can be found here and there are quite a few neat code samples at this location
Below is some sample code that you might be able to use in your solutions:

//importing most of the necessary libraries for the mapping behavior

//global vars and mapping initialization var strAddress:String = new String();
var map:Map;
var geocoder:ClientGeocoder;
var marker:Marker;
map = new Map();
//google requires a free API key...for testing purposes initialize with empty string
//which stamps map with DEBUG MODE
map.key = "";
map.setSize(new Point(750,210));
map.x = 20;
map.y = 230;
map.addEventListener(MapEvent.MAP_READY, onMapReady);

//search button event listener btnSearch.addEventListener(MouseEvent.CLICK, doGeocode);

//geocode function
function doGeocode(event:Event):void {
//clear old marker overlays

//check if field values are entered to do a address search
if(txtAddress01.text == "" || txtCity.text == "" || txtZip.text == "" ||
cbState.selectedIndex == 0){
MessageBox("Missing Values:","Values Missing?\n\nPlease fill all required values?\nEverything marked with a * is required!");
strAddress = txtAddress01.text + "+" + txtCity.text + "+" + cbState.selectedItem.label + "+" + txtZip.text;
geocoder = new ClientGeocoder();

//add success and failure event handlers for geocoding result
geocoder.addEventListener(GeocodingEvent.GEOCODING_SUCCESS, geocodingSuccessHandler);
geocoder.addEventListener(GeocodingEvent.GEOCODING_FAILURE, geocodingFailureHandler);

//execute geocoding

//geocoding success event handler (the address was gound in Google's db)
function geocodingSuccessHandler(event:GeocodingEvent):void{
//placemaker array to show results on map
var placemarks:Array = GeocodingResponse(event.response).placemarks;
//create new marker
marker = new Marker(GeocodingResponse(event.response).placemarks[0].point,new MarkerOptions({label: "Marker"}));
//add event listener for marker
marker.addEventListener(MapMouseEvent.CLICK,function(event:MapMouseEvent): void {;
MessageBox("Item Description:","Address: " + placemarks[0].address);

//add marker to map overlay

//geocoding failure event listener (google didn't find address)
function geocodingFailureHandler(event:GeocodingEvent):void{
trace("We couldn't find that location in the Google database of addresses.");

//map initializer function
function onMapReady(event:Event):void {
//set center of the map's initial view
map.setCenter(new LatLng(38.986,-76.94255), 14, MapType.NORMAL_MAP_TYPE);

//create new contorl position to set position of zoom control
var bottomRight:ControlPosition = new ControlPosition(ControlPosition.ANCHOR_TOP_LEFT, 0, 0);
var myMapTypeControl:ZoomControl = new ZoomControl();

//enable zooming

Screenshots of a working sample in action.

November 2008 - Missing "Create Shortcut..." Option in Acc2007

In earlier versions of Access you could right click on an object within the database window to create a desktop shortcut specifically to that object. When you clicked on that shortcut the database launched right to that object.

Most people probably never used that feature but it is pretty helpful for some scenarios. Access 2007 seems to have gotten rid of the specific option to execute the feature or at least I can't find it anywhere. There are three workarounds to that:

1) If you still have Acc2003 installed then create a shortcut there as usual...then use NotePad to open it and modify the databasename, objectname and databasepath properties of the shortcut file to point to your new 2007 file and object.

2) If you don't have Acc2003 installed anymore or just want to create the shortcut file from scratch then use this template and modify the same information mentioned in step 1. Save the file with a .MAF extension.

[Shortcut Properties]
DatabasePath=C:\Documents and Settings\YOURFOLDER\Desktop\DatabaseName.accdb
CreationTime= 1c7b4fddcabacf4

3) This one is probably the easiest method. Just restore the Acc2007 application window so that you can see your Desktop in the background. Click and drag the db object you want a shortcut to from the Navigation Pane within Access and move it to your desktop. An appropriate shortcut file will be created for you.

November 2008 - SQLite: a good file based RDBMS alternative for web apps

I'm currently working on a school project of implementing a dynamic, web based application (check out the animated gif of screenshots at the end of this). The footprint/exposure of the site is small to medium sized and the implementation requires multiple developers. Since recourses and time are sparse we decided to stick to a file based backend. Naturally, I initially thought of using MS Access as the backend for the site, but as usual I'm not honored with working on a Windows server so I can't use ADO to access the file and I haven't had good experience using the ADOdb library with PHP. Not wanting to go overboard for the scope of the project with setting up servers (MySQL, SQL Serverů) a co-student suggested maybe looking into SQLite. I did some research and testing and was quite impressed. SQLite is a pre-compiled, self-contained, relational database management system as a C library.

SQLite3 is supported through PHP5's PDO class so all that is required for setup is to enable the php_pdo.dll and php_pdo_sqlite.dll files within your php.ini file. Two programs that might help you to actually work with the backend directly and not through PHP (for initial setup/configuration) are the SQLite3 command line tool ( and this SQLite Administrator ( Both are free and very feature rich. Another interesting tool that might come in handy is this MDB to SQLite converter ( It is based on Jackcess (a Java library to interact with Access files) and can automatically convert your MDB files without problems.

One thing to keep in mind is that the SQLite file requires full read, write and delete permissions on the server for all SQL statements to function correctly. If you are just doing lookups with SELECT statements then read access is sufficient. In our project we had to put the backend file into a separate server folder and chmod the permissions for that folder to 777 (full access on a Unix file system). So make sure your server supports that if you are interested in playing around with SQLite.

Conclusively, the more I work with SQLite the more I'm impressed. Specifically with the build in support within PHP, which makes communication between the frontend and backend very efficient as well as easy. So if you are working on a small to medium sized web implementation (less then 100k hits/day), then maybe check it out.

November 2008 - Oil Painting

I finally finished an oil painting that I've been working on for quite a while now. It's my first real attempt at the oil medium. Painting has always been and is especially now (in very stressful times for me) an outlet to stay sane. The image is of the DC capitol at night with the world famous DC cherry blossoms in the foreground.

DC Oil Painting

October 2008 - Happy Halloween

In case you missed last year's pumpkins click here to view them before checking out this year's batch.

From left to right: Goofy Frankenstein, Spider, Pumpkin Pie, Albert Einstein, Obama Change, Cat with mini pumpkin

Halloween 2008 pumpkins

October 2008 - Death by chocolate I'm a chocoholic and there is only one good way for me to go...death by chocolate (especially dark chocolate). I'm always searching for the best dark chocolate on earth. Maybe one day I'll have the resources and time to do my own cocoa tree breeding and create nice tasting chocolate recipes. For now I will give you a decent top five of dark chocolates:

1. Green & Black's Dark 85%
2. Sarotti No.1 Sao Thome 75% Cacao
3. Scharffen Berger Limited Series Bar #10: 72% Cacao Finisterra
4. Theo Venezuela Limited Edition Dark Chocolate Bar 91%
5. Noir Infini 99% Michel Cluizel

A good standard that can be usually found anywhere is:

6. Lindt Excellence 85% Cocoa

October 2008 - The future of transportation

I saw this very interesting article in a recent WIRED magazine publication. Shai Agassi is a very forward thinking business man, who is taking a stab at the toughest environmental issue. He came up with a detailed business plan as to how he sees the future of transportation and is now trying to implement it with his newly founded company (Better Place). I'm always impressed by such ambitious people, who believe in something so much that they give up almost everything (in this case it was a future CEO position at SAP, one of the largest software enterprises in the world). Entrepreneurship is a very intriguing concept to me. The text is a bit lengthy but well worth the read. You can head to the WIRED website to read the whole article.

September 2008 - Access 2007 Web 2.0 sample form part I (video)

I'm sure you have all seen the cool interactive Web 2.0 sites/apps that dynamically draw/refresh the screen based on user actions. There are some very good samples of what I mean on Facebook. I thought I put together a quick demonstration illustrating one way to spice up your Access forms to do similar cool features. Here is a screen capture of the demo.

September 2008 - Java/Access 2007 sample applet (demo)

As promised in my earlier PHP/Access 2007 blog post here is a quick sample of how to use Java to connect to an Access 2007 database. Again this is a DSN-less connection so no initial, manual setup required. In this sample I'm using the jdbc-odbc bridge driver provided with the Java JDK. There are many third party drivers available, too if you do not want to go with Sun's solution.

I wrapped the code in an applet class so that you could display the results from the database hit within an applet viewer e.g. your browser if you want. I used the same members_data.accdb file from the PHP blog post and you can download the .java (code) and the ACCDB from here (14KB). As before the connection string is looking for the ACCDB at: 'c:\temp\members_data.accdb'. Here is the complete code:

import java.applet.*;
import java.awt.*;
import java.sql.*;

// Extends applet (build code on the standard Applet class)
public class TestAccessConnection extends Applet
private static final String accURLPre = "jdbc:odbc:Driver=Microsoft Access Driver (*.mdb, *.accdb);DBQ=";
private static final String accURLSuff = ";DriverID=22;READONLY=true}";
private static String filename = "c:\\temp\\members_data.accdb";

public void init()
// Required but doesn't need anything...gets called when applet is initialized

public void stop()
// no actions needed for this small sample (method gets called when appled terminates

//draw on screen
public void paint(Graphics g)
// Draw text on screen (String, x, y)
g.drawString("Connect to Access 2007 db with Java (in Java Applet)",30,20);

filename = filename.replace('\\', '/').trim();
String databaseURL = accURLPre + filename + accURLSuff;

Connection conn = DriverManager.getConnection(databaseURL);
Statement command = conn.createStatement();
ResultSet rs = command.executeQuery("select * FROM members_data");
try {
g.drawString("First Name",30,50);
g.drawString("Last Name",120,50);
g.drawString("Date/Time signed up",340,50);
int i = 0;
while ( ) {
//int numColumns = rs.getMetaData().getColumnCount();
//for ( int i = 1 ; i <= numColumns ; i++ ) {
//System.out.println( "COLUMN " + i + " = " + rs.getObject(i) );
g.drawString("" + rs.getObject(1),30,(70 + (20 * i)));
g.drawString("" + rs.getObject(2),120,(70 + (20 * i)));
g.drawString("" + rs.getObject(3),210,(70 + (20 * i)));
g.drawString("" + rs.getObject(6),340,(70 + (20 * i)));
i += 1;
} finally {
//clean up
//System.out.println("Connected To Access");
catch (SQLException se)
catch (Exception ex)

Here is a small screenshot of what the result is when executing the above code:

You can obviously extend this much nicer and use ALL the posibilities of the Java drawing library (e.g. constructing tables, buttons, menus, or other UI features). As always I hope that someone found this useful.

September 2008 - PHP/Access 2007 sample site (demo)

I have been using PHP a lot more lately so I figured why not post a blog entry about that for a change.

You always see web samples/tutorials online that utilize PHP and MySQL as the backend or maybe if you are lucky at best ASP and an Access database. Of course PHP works great with MySQL, but it actually works pretty well with Microsoft Access, too.

I put together a quick sample that demonstrates a website running off an Access 2007 backend. It uses all sorts of extra PHP functionality including session handling, password hashing and salting for a more secure storage of the actual password within Access, validating email domains, error handling etc. This demo could be a stepping stone for implementing a website that requires user authentication for information only accessible to members. So I encourage you to continue where I left of and build some cool and exciting solutions if you do want to utilize the power and flexibility of PHP.

I was testing this demo with an Apache 2.0.63 HTTP server and PHP 5.2.6 installed on Windows XP. I didn't have time to test this on a non-windows OS. I will leave that task to you if you need that kind of environment. I am not covering the details of how to obtain and install Apache/PHP for this demo as there is plenty of existing online literature already available. If you are in a similar software situation as described above then a good start if you do not already have an Apache HTTP server and/or PHP installed would be the following two websites:

- Using Apache with Microsoft Windows
- PHP: Installation on Windows systems

You can download all the necessary PHP files (code) as well as the utilized Access ACCDB from here (23KB). The connection strings in the PHP files look for the Access ACCDB at 'c:\temp\members_data.accdb'. So either move it there or modify the connection strings to point to your new location. All that is left is to just dump all the PHP files from the downloaded zip file into your default Apache directory that you access with 'http://localhost' through your web browser. Normally it is under 'c:\Program Files\Apache Group\Apache2\htdocs\'. This sample uses a DSN-less connection so no need to manually set up any connections, which is usually not possible if you do not have the necessary authority on the webserver.

To play around with the sample navigate to the login-form.php file in your web browser with the Apache service running e.g.: http://localhost/login-form.php. If your installation was correct then you should see the following screen:


The application houses one existing user (; password = testpassword). So if you want you can log in as me and see what will be displayed. If you want to use your own credentials and follow the demo step by step then click the New User button, which will load the register-form.php file as shown in the following image:

The PHP code executed behind the Register button will do a bunch of validations including a check for values in all fields (they are required), comparing the two provided passwords for equality, actually testing the domain of the provided email address to ensure that it could be a valid email as well as hitting the Access backend file to determine if no user exists with the given User ID/email yet. If all the validations pass then an Insert SQL statement is executed and the new user is entered into the database. I have used PHP's md5 hash function to store the actual password value hashed with a concatenated hashed, random salt value to make the password storage as secure as possible. The random salt value is stored within the member record in the Access table as well to allow the user authentication at login. The PHP code that does all this looks very simple as shown here:

$salt = substr(md5(uniqid(rand(), true)), 0, 5);
$secure_password = md5($salt.md5($password));

As mentioned the above concatenates a 5 character long unique salt value that is hashed with the hashed value of the original password. The salt and the hashed password are stored and later used for a comparison with a user provided password on the log in form. If your new member registration was successful then you should see the following screen:

Just press Click Here to be redirected to the original log in form of the first picture. You can now use your own credentials to log into the site. Again the PHP script that is being executed after pressing Login does a few validations including a check if all values were provided, the User ID/email actually exists in the application, and if it does if the passwords match up. This is where the salt and user password are being retrieve and compared with the value of the form password field:

$rs_member->Fields['password']->Value != md5($rs_member->Fields['salt']->Value.md5($password))

If the information provided is not valid then you will be redirected to a log in failure page. If everything checks out then you should see this screen:

Session data has been created and is being used in the display of the page. You can press the My Profile button to see something similar to shown here:

As you can see only my information is being displayed for my profile page. To ensure that all session data is destroyed you can press the logout button and you will be shown to this page:

If you try to use the back button on your browser to return to your profile you should notice that you are not allowed to return to the previous state but instead get a page informing you about denied access.

That is pretty much all there is to having a database driven website that requires user authentication. You can probably make the provided code a little cleaner/tighter and as usual I hope that someone finds this useful and if you are lucky and I find the time I might do a similar post about doing the exact same thing with a Java Applet.

September 2008 - Coast to Coast

So I recently had to travel to the west coast and immediately went on a staycation in Ocean City, MD afterwards.
I took some pictures of my appearances at the two coast lines.

Seaside, OR
λ: 45.993121
φ: -123.920655

Ocean City, MD
λ: 38.397824
φ: -75.06235

September 2008 - Hug a developer

My brother pointed this out to me and I thought I would share it with all of you.

August 2008 - Unexpected error with Email Data Collection?

Are you receiving the following unexpected error when trying to send an email created by the new Data Collection Wizard in Access 2007:

Microsoft Office Outlook 2007 was unable to send the message to the following recipients because of one or more of the following reasons:

1. An invalid e-mail address
2. There was no data associated with the e-mail address

When going through the wizard to select the data/fields you want to send/retrieve/include in the email, try to include the Primary Key field of the underlying table source where the data is stored and the error should disappear.


August 2008 - Access Data within Email Body

The particular question about how to embed Access data directly in an email body instead of needing to attach a separate object keeps appearing in the forums. So I figured I will post a blog post mentioning several different approaches one could take to do this.

The first one is straight forward and assumes that you just want to send the data that is displayed on a form. It utilizes the build in SendObject method and just references the form controls when constructing the Body argument of the SendObject method.


Dim strToWhom As String
Dim strMsgBody As String
Dim strSubject As String

strSubject = "Your Subject here"
strToWhom = "" 'or reference a form control that holds the actual email address
strMsgBody = "Your Body text goes here! And you can reference controls, which hold values you want to send along e.g." & Me.DateControl & vbCrlf & "some more text!"

DoCmd.SendObject , , , strToWhom, , , strSubject, strMsgBody, True


Pretty straight forward, right? Now to some more interesting approaches. Let's assume that you have a report that you use to display data within Access on screen and you want to send the information within the report to some users but without the hassle of dealing with attachments that they have to save and open. We can actually output this report in HTML format, then open up its source code to grab it and just pass it along to the body argument of an HTML email that we created programmatically. The report within Access could look something like this:

The below function will do what we described above. I'm using early binding so make sure to set a reference to your version of the Microsoft Outlook Object Library.


Public Function HtmlEmail(strReportName As String)

Dim oFilesys, oTxtStream As Object
Dim txtHTML As String
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem

DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, "C:\temp\" & strReportName & ".HTML", False

Set oFilesys = CreateObject("Scripting.FileSystemObject")
Set oTxtStream = oFilesys.OpenTextFile("C:\" & strReportName & ".HTML", 1)

txtHTML = oTxtStream.ReadAll


Set oTxtStream = Nothing
Set oFilesys = Nothing

Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)

With objMail
.BodyFormat = olFormatHTML
.HTMLBody = txtHTML
.Recipients.Add ""
.Subject = "Customer Data"
'.Send if you want to send it directly without displaying on screen
End With

'you can delete the outputted file if you want
'kill "c:\temp\" & strReportName & ".HTML"

Set olApp = Nothing
Set objMail = Nothing

End Function


You can call this function from anywhere and just passing in the name of your report e.g.: ?HtmlEmail("rptCustomers")
The result email might look something like this:

Now this is already pretty nice and we don't need an attachment anymore. However, it does seem like a long workaround to get the final result with outputting a separate file and opening/closing it as well as deleting it later on. We can do better then that. Especially if we do not want to rely on an actual report to do the work. The next sample opens a recordset based on a query or table and loops through it to dynamically create the HTML output we need to pass along to our email message. This will also allow us more flexibility in formatting the data correctly, which is somewhat lost in the earlier approach. This sample uses an ADO recordset so make sure you have a reference to your latest Microsoft ActiveX Object Library set.


Public Function HtmlNoReportEmail(strTblQryName As String)

Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strMsg As String
Dim sqlString As String
Dim i As Integer
Dim rowColor As String

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

sqlString = "SELECT * FROM " & strTblQryName & ""
rs.Open sqlString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

strMsg = "<table border='1' cellpadding='3' cellspacing='3' style='border-collapse: collapse' bordercolor='#111111' width='800'>" & _
"<tr>" & _
"<td bgcolor='#7EA7CC'>&nbsp;<b>Company</b></td>" & _
"<td bgcolor='#7EA7CC'>&nbsp;<b>Last Name</b></td>" & _
"<td bgcolor='#7EA7CC'>&nbsp;<b>First Name</b></td>" & _
"<td bgcolor='#7EA7CC'>&nbsp;<b>Job Title</b></td>" & _
"<td bgcolor='#7EA7CC'>&nbsp;<b>Business Phone</b></td>" & _
"<td bgcolor='#7EA7CC'>&nbsp;<b>City</b></td>" & _

i = 0

Do While Not rs.EOF

If (i Mod 2 = 0) Then
rowColor = "<td bgcolor='#FFFFFF'>&nbsp;"
rowColor = "<td bgcolor='#E1DFDF'>&nbsp;"
End If

strMsg = strMsg & "<tr>" & _
rowColor & rs.Fields("Company") & "</td>" & _
rowColor & rs.Fields("Last Name") & "</td>" & _
rowColor & rs.Fields("First Name") & "</td>" & _
rowColor & rs.Fields("Job Title") & "</td>" & _
rowColor & rs.Fields("Business Phone") & "</td>" & _
rowColor & rs.Fields("City") & "</td>" & _

i = i + 1

strMsg = strMsg & "</table>"

Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)

With objMail
.BodyFormat = olFormatHTML
.HTMLBody = strMsg
.Recipients.Add ""
.Subject = "Customer Data"
'.Send if you want to send it directly without displaying on screen
End With

Set olApp = Nothing
Set objMail = Nothing

End Function


Again you can just call this function from anywhere passing along the name of your table/query e.g.: ?HtmlNoReportEmail("qryCustomers")
The final email might look like this:

This seems like a pretty nice, final result without the massive overhead. So I will leave it at that and hope that someone found this useful. Of course you can customize this further if you want. In case you want to reproduce the result, I used the Customer table from the Northwind sample database in this demonstration.

P.S. While we are already talking about embedding things within an HTML email message body. If you ever want to add an image to the body without relying on an outside source for the image to display then you can actually attach them to the email message and refer to the attachmends within your HTML body e.g.:


Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strMsg As String

strMsg = strMsg & "<HTML><Body>" & "<img src='cid:freak01.gif'>" & "<br>" & "<img src='cid:freak02.gif' >" & "</BODY></HTML>"

Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)

With objMail
.Attachments.Add ("c:\freak01.gif")
.Attachments.Add ("c:\freak02.gif")
.BodyFormat = olFormatHTML
.HTMLBody = strMsg
.Recipients.Add ""
.Subject = "YourSubject"
'.Send if you want to send it directly without displaying on screen
End With

Set olApp = Nothing
Set objMail = Nothing



August 2008 - The Square Root Of Three Poem

Even though the movie was awful, I thought this poem about the square root of 3 from the 2nd installment of Herald and Kumar was hilarious and any geek would most likely appreciate it:

The Square Root of 3

I fear that I will always be
A lonely number like root three
A three is all that's good and right,
Why must my three keep out of sight
Beneath a vicious square root sign,
I wish instead I were a nine
For nine could thwart this evil trick,
with just some quick arithmetic
I know I'll never see the sun, as 1.7321
Such is my reality, a sad irrationality
When hark! What is this I see,
Another square root of a three
Has quietly come waltzing by,
Together now we multiply
To form a number we prefer,
Rejoicing as an integer
We break free from our mortal bonds
And with a wave of magic wands
Our square root signs become unglued
And love for me has been renewed.


July 2008 - So You Think You Can Prance?

I created a new T-Shirt inspired by the popular FOX summer hit TV show So You Think You Can Dance. You can get your very own So You Think You can Prance T-Shirt through Click this link or the picture below. Customize it (T-shirt style, color, size...) as much as you want.


July 2008 - Nintendo Wii vs. Microsoft Xbox 360

I've had my Xbox 360 for quite a while now and have had some decent exposure to a Wii gaming console as well. So I'm doing the only logical/natural thing any geek would do in this situation and compare the two.

I might sound biased throughout this post but there is a reason why I've wanted the Xbox 360 over the Wii or Playstation3. I really like the interactive Xbox live feature that Microsoft has put together. It brings gaming to a whole other level. There are only a few games you can really sit in front of for a long time all by yourself without getting bored. The majority of game titles (especially any sort of sporting games, which I'm a big fan of) really thrive on multiplayer action. With the 360 this is easier done then said and within seconds you can play people from all around the world while talking smack over your headset. In celebration of the EURO 2008 I recently played FIFA 08 with my brother, who lives 4000 miles away in Germany while catching up on the latest personal things happening in our lives. So I'm really enjoying the social networking capabilities of Xbox live even though it might take some initial start up cost of a separate wireless receiver (for a wireless network) and a monthly Live subscription. I also really like the large game selection catalog, which now brings me to the Wii.

I must admit that I was intrigued by the new motion sensor type of game experience. However, after playing for about 30 minutes it really seems to get boring. How often can you really mimic the motion of a golf swing in Tiger Woods PGA tour or roll skiballs or bowling balls? I understand that it makes for a great family activity every now and then, where really everyone from the child to the grandmother can interact. By no means do I consider myself a serious gamer, but it just seems to get old at some point and the poor graphics that only maybe rival what you are used to from your old N64 or GameCube don't help at all either. The same counts for the majority of games that actually do a decent job integrating the motion sensor ability. Most of the games are Nintendo titles and even though I'm a large fan of the original Super Mario Brothers (who isn't really?) I just don't see that lasting. I understand Nintendo's family friendliness, but if 6 out of the 10 most popular Wii games are some spin off of the Mario saga then that seems very limiting. Furthermore, cooking or performing surgery with your Wii remote can only entertain you for so long. I must say that I do enjoy the fact that you can download old NES, N64, Commodore 64, even SEGA systems games (similar to Xbox Live's arcade downloads). However, paying the initial ~$250 for the console and then $5 per title seems a lot of money to play something that I can play on my old consoles or at least emulate on my PC for free.

I purposefully only tried to compare the gaming experience, features/capabilities and not the looks (I don't really care how the console looks as long as it performs as it is supposed to), reliability (all three PS3, Xbox360, Wii had recall/reliability issues...some more severe then others but hopefully things are somewhat stable across the board now), company service (I had my fair share of customer service issues with Microsoft trying to straighten out some Live account problems but I can't measure them to anything else since I haven't approached costumer service for the other consoles), etc.

As initially warned this came out much more one sided then I had hoped for. It is clear that it is hard to actually compare the two systems. They both seem to target completely different audiences. I appreciate Nintendo's forward thinking with the new motion sensor approach and they seem to stick to what has always made them popular over the years, family friendly gaming and their Nintendo characters. The Xbox on the other hand can act for me as a complete media hub connected to the PC, Zune, etc. and gives me ample of social networking power for the 5 minutes left in the day that I'm not connected to Facebook, LinkedIn, UtterAccess, StudiVZ, AIM, etc. on the web ;).

If only Microsoft would finally add an external BlueRay drive to support HD gaming/movies of the future and not just rely on download of that content through the Live Marketplace (btw...they just recently teamed up with NetFlex for more of that kind of content).


July 2008 - Tired of MP3/Music DRMs?

Ever since the days of organized, legalized MP3 downloads I've been annoyed with the DRM systems that govern the rights of the content. I just never felt like the music I purchased with my hard earned money belongs to me. I cannot use it as background songs in slides, put it on more then 2 portable devices, can only burn it so many times, cannot use MP3s from iTunes on my Zune...If I buy a CD I can do whatever I want with it for my own personal use. Furthermore, I've gotten really tired of certain marketplaces using stupid point systems to purchase merchandise. Well...these problems now seem to be solved with RealNetwork's DRM free Rhapsody downloads, which is following in Napster's DRM free footsteps. Real has teamed up with Yahoo and Verizon to bring true DRM free music (their whole catalog, which is not as limiting as the iTune's Plus store and it's cheaper and it doesn't require a binding membership) to everyone and everywhere. The prices are the usual 99cents/song or $10/album and like a long time ago you can do whatever you want with the music that you have purchased. The switch for me seems logical and if other main competitors don't follow then I don't see a reason to ever go back to buy from them.


June 2008 - Monopoly Groom's Cake

This is my wife's first cake that was not made for an internal family member. It is a groom’s cake for one of our friends, which put a lot of pressure on her. I think she brought her A game and delivered one of the best cakes yet. If you are unfamiliar with her earlier productions then check out these links: Thomas Tank Engine, Pirate Ship, Birthday Package, Farm Yard.

Obviously the theme of this cake is one of the most popular board games ever…Monopoly. Everything is customized based on the groom’s life (can you guess the name of the groom ;) and as usual everything was handcrafted and is completely edible. It is a one to one reproduction of the actual game (board size is 20x20”). By request the cake itself is a carrot cake with cream cheese filling.

May 2008 - Microsoft Surface Experience

I had the chance to play around with MS Surface. It is a really neat consumer technology that Microsoft has been working on for a long time. If you want to learn more about it check the cool demo videos at this site.


April 2008 - Birthday Cake #4

This is the fourth cake my wife put together. Earlier ones included a Thomas, pirate and a birthday package cake. This cake pictures a barn yard/farm theme with a silo, barn and a bunch of farm animals.



April 2008 - Microsoft MVP summit

I will post pictures and comments at the following site. Check it out if you are interested:

MVP 2008 Summit

March 2008 - Microsoft Access 2007 Runtime updated


I know lots of you are very anxious to get your hands on this build. You can go download the SP1 runtime.

This version of the Access 2007 Runtime contains files whose versions are slightly higher than the level of the same files that are updated by Office 2007 Service Pack 1 (SP1). For additional information about Office 2007 Service Pack 1 and the Access 2007 issues fixed by Office 2007 Service Pack 1, click the following article numbers to view the article in the Microsoft Knowledge Base:

936982 Description of the 2007 Microsoft Office suite Service Pack 1
942378 Issues that are fixed in Access 2007 by the 2007 Microsoft Office suites Service Pack 1

The Access 2007 Runtime updates files to correct some specific Access 2007 runtime issues that were not previously documented in a Microsoft Knowledge Base article:

* PDF/XPS Export: You may see a message similar to "can't save the output data to the file you've selected" or "The OutputTo action was cancelled", when you try to export an object to the PDF or XPS file formats from within Access 2007 Runtime.

* Text/HTML Export: You see a message similar to "can't complete the Output operation" when you try to export a report to the Text or HTML file formats from within Access 2007 Runtime.

* Pivot Table/Chart Views: Pivot Charts and Pivot Tables do not show the correct results when opened in Access 2007 Runtime.

* Access Data Projects: You see a message similar to "The record source [RecordSourceName] specified on this form or report does not exist", when opening a report in an Access project (*.adp) which is bound to a SQL Server object even though the record source exists.

The international versions of the runtime should be available in the next few weeks (I don't have a specific date as intl testing is ongoing). We will post download links as they come available.


<End Quote:>


March 2008 - Access MVP T-Shirts

Get your custom Access MVP gear for the 2008 global summit. Stand out in the crowd with any of these t-shirts, polos...
Keep in mind that you can change/customize color/size/style of any of the shown templates. I cannot guarantee the quality/look of these since I didn't purchase each for myself.

You can browse through the products above or just go to my gallery to see more details. Order soon to ensure that the shirts arrive before the summit. The processing/printing/shipping can take up to 15 days.


March 2008 - Combobox backcolor randomly changing (Acc2007)?

Are you having an issue in Access 2007 where the backcolor of your combobox changes to the backcolor of the form section the combobox is housed in when removing focus from the control? This assumes that the backsyle property of your control is NOT set to transparent. For clarity this image demonstrates the problem:

Don't worry you are not going crazy and you are not missing any property that might be causing this. After some investigations I have found that this might be related to an applied AutoFormat. The behavior ocurres if you are using a 2003 AutoFormat. Open the form in layout view and go all the way to the right under the auto format group of the format ribbon tab. Use the pull down to expand the options and select AutoFormat Wizard...there select the Access 2007 AutoFormat in the left column. This will reset your format settings so you will need to reapply your custom background color but it should fix the combobox issue.


February 2008 - Sony to win at least one format battle?

With the recent development in the brawl of HD-DVD vs. Blue-Ray it seems as if Sony will at least win one of its many format battles it fought over the years. Sony must have learned something in the defeats of Betamax, MiniDisc…and seems to come out on top with their support of the Blue-Ray next generation movie format.

After almost all the major Hollywood studios supported the format, other influential companies followed including retailers like WalMart and BestBuy as well as service providers like Blockbuster and Netflix. It even seems that one of the strongest supporters of the HD-DVD format, Toshiba has given up.

Now that we hopefully have the confusion over the two formats out of the way, we will probably soon see a major drop in prices and availability of HD-DVD products.

One announcement I’m really waiting for is the decision of Microsoft to manufacture an additional external Blue-Ray drive for the Xbox 360. Having initially gone with an external HD-DVD drive, which leaves the door open for later adjustments and hearing/reading positive statements about potential Blue-Ray support from Microsoft’s side, makes me optimistic for the near future. It might actually need to be a necessary move on their part if they want to continue to be a contender in the gaming console market.


February 2008 - What's up with shuffling?

So what’s up with shuffle algorithms? I’ve recognized that the Zune (80GB) doesn’t do such a good job of shuffling all its songs. With more then 5000 songs, it keeps coming up with the same artists and if it does play new albums then it is almost always the hits from those albums. It seems as if a star rating influences the queue that is created when using the “shuffle all” feature. I tried to get some feedback on this from the few existing Microsoft Zune MVPs but no luck.

I did learn that this is not unique to the Zune player. My SonyEricsson W580i (part of the walkman line) does a very similar thing. With more than 1000 songs, it keeps going back and forth between the same albums/artists. A quick fix for the phone was to actually turn off the shuffle feature but leave the queue in the play list. The phone will have sorted all songs by title, which turns out to be pretty random unless you listen to the Beach Boys who have plenty of songs that start with “Surf…” ;).

Back to the fact that this happens more then I thought it does. I was made aware that WMP (Windows Media Player) has the same shortcoming. This all leads me back to my original question…what is up with the shuffle algorithms? Why is it so hard to make the songs random and maybe implement a feature that does not play the song/artist/album again until all others have been played? What good is it to have 80GB of space when the shuffle feature keeps repeating the same old content? I could have fit 10-20 songs onto a 128MB flash player, too. Am I missing something? If anyone has any constructive comments or ideas on how to fix this problem, please email me from the contacts page.


February 2008 - Access 2007 making your life easier

Were you ever in the situation of supporting an application for several different screen resolution environments? Normally you had a few options you could use to make the application as visually appealing as possible. Some of these are either to just develop in the lowest common denominator (sometimes still a 800x600 resolution) to ensure that everything will fit on screen but also waste a lot of screen real estate for clients with larger screens. Another option would be to use a lot of code or third party implementations that detect the current screen size and adjust the control/object sizes and positions, which mostly costs a lot of unnecessary overhead. Yet a further option would be to create and deploy different frontends with different size adjustments to the specific clients, which results in a lot of development and future update time on your part.

Access 2007 implements a cool anchoring feature that could solve this problem for you all together without any effort on your part. You can find the feature under the Anchoring split button in the Size/Position group of the Arrange ribbon tab when in object design/layout view. You can somewhat see the effect in the little animated gif demo below:

This is obviously still not completely perfect since it doesn't resize the font/content of controls but rather just the controls itself. Though it is definitely a good step into the right direction and it makes it much more easier for the developer to deal with a mixed client environment.


February 2008 - Export report to Excel in 2007

A lot of people have been wondering lately why it is not possible in Access 2007 to export a report to the Excel format anymore. The Analyze with Excel feature has been completely removed. If you manually try to export a report to the Excel format you will find that the Excel button in the Export group of the External Data Ribbon tab gets disabled. If you try to do this through code e.g. with the OutputTo method you will receive a 2282 runtime error: "The format in which you are attempting to output the current object is not available."

The behavior and possible workarounds are described in this MSKB article:

There are several different speculations to the reasoning of the removal of the feature. Though no official statement has been made it sounds as if Microsoft had to remove it for legal reasons. Personally I think it was an unnecessary function in the first place. Reports are graphical representations of the actual underlying data. So if you want to output a report then it should probably be to a static format like PDF or SNP that will accurately duplicate the actual internal report representation. Reports can use grouping, filtering, sorting which all will effect the export. If you want to export to the Excel format or some other editable content then why not use the report's underlying source (query/table) instead. You can easily output them with either the TransferSpreadsheet, TransferText or OutputTo method.

Additionally, since we are on the topic of exporting data this is also mentioned in one of my tutorials:

If your Access 2003 application incorporates a custom command bar which includes the Export…/Import… command or you implemented the acCmdExport/acCmdImport from the AcComand Class of the RunCommand action anywhere within your application code you will receive an error within Access 2007. A workaround for this scenario would be to directly specify the export or import with the provided TransferSpreadSheet, TransferDatabase, TransferText, or OutputTo methods.


February 2008 - Screenshots of Ribbon application

I recently built a somewhat simple but yet powerful budget tracking application. The goal was a personal tracking application which can be easily used and navigated by computer unsavy users. I opted to utilize the Ribbon in Access 2007 for this. It eliminates the need for a separate switchboard like form. The tabbed document feature additionally keeps everything on the same screen without having to switch back and forth between dialogs. I think the main goal has been accomplished and I wanted to share the resulting look and feel of the application with you. As mentioned it is nothing fancy but sometimes less is more:


January 2008 - AutoFormats not applying correctly?

Have you ever tried to apply some of the cool AutoFormats that are available in Access 2007 and they just didn't seem to stick correctly? Here are a couple of hints that might help you:

In ACCDB file format created with Acc2007:

- If in design view make sure to select the form you want the AutoFormat applied to. It has to have focus for all properties to carry over. If you want to utilize the formats of the form header/footer then show them first (Arrange Ribbon group on the right...Form Header/Footer button in the top left) before selecting the specific AutoFormat.

- In Latyout view you can even have a control in focus for all the properties to carry over when selecting an AutoFormat.

In ACCDB format converted from an MDB created with a prior Access edition:

- The AutoFormat features (backgrounds...) might not apply correctly. Create a new file in Access 2007 and import all db objects from the old file into it (External Data Ribbon tab...Import group...Access button...). You should now be able to specify everything as you want to.



January 2008 - Including Compact and Repair in Runtime distribution

This question recently came up on UtterAccess forums. The OP had recognized the weird behavior that the default compact and repair feature utilized in a custom Ribbon is removed when the ACCDB extension gets renamed to ACCDR. I've tested this and came to the same conclusion. So how are you supposed to compact an application when it is distributed to clients who use the 2007 Runtime edition? The OP wanted to give the client flexibility to execute the process themselves. Here is the XML I used to test it:

<customUI xmlns="">
< ribbon>
<tab id="tab1" label="My Tab">
<group id="grp1" label="My Group">
<button idMso="FileCompactAndRepairDatabase" insertBeforeMso ="FileCloseDatabase" />

The conclusion I came to was to utilize the QAT (Quick Access Toolbar) instead of a custom Ribbon command. Open up the application as an ACCDB with the full version of Access 2007. Next to the QAT in the top left (right of the office button) you will see a drop down arrow. Select the More Commands...option. In the "Customize Quick Access Toolbar:" combobox on the right select For: c:\...\yourApplication.accdb. In the "Choose commands from:" combo on the left select All Commands and find the "Compact and Repair Database" command. Double click on it or select the Add button to move it to the right list box. Close the Access Options dialog. Close the application. Change its extension to ACCDR and open it up. You should now have the Compact and Repair command in the QAT and it should function as expected. I'm assuming something similar should work with appropriate XML code in the Ribbon customization that modifies the QAT. Gunter's page will get you started with that.


January 2008 - In Loving Memory

09/16/1919 - 01/16/2008

Nana will be missed by everyone.


January 2008 - VBA will continue in Office 14

There have been sevaral articles on the web lately rumoring that Office 14 will lack VBA (Visual Basic for Applications). This is obviously not true but the news spread like a wildfire. Doing some damage control there have been a few official releases from MS speaking the truth about the future of VBA. Quoting Clint Covington from the Access team blog:

As someone who is working everyday on Office 14, I can assure you that VBA is not disappearing in the next release of Office. VBA will continue to be a valuable option for developers to customize Office solutions to meet their business requirements.
<End Quote>

Furthermore, Joseph Chirilov from the Excel blog:

While it’s true that VBA isn't supported in the latest version of Office for the Mac and the VBA licensing program did close to new customers last year, we have no plans to remove VBA from future versions of Office for Windows. We understand that VBA is a critical capability for large numbers of our customers; accordingly, there is no plan to remove VBA from future versions of Excel.
<End Quote:>

Hopefully these news will clear up some confusion and spread just as fast as the initial false releases.


January 2008 - Birthday Cake #3

This time my wife wanted to go the elegant route when designing/building the birthday cake. Even though it was lacking a fun theme like the earlier Thomas or pirate cake it turned out to be the best one yet. The cake also allowed her to experiment with new techniques including airbrushing with food colors. As usual everything is edible including the large red bow made out of gum paste. The actual cake is red velvet with cream cheese icing.


January 2008 - 10,000 visitors on

Today this website reached a milestone with 10,000+ visitors since its official launch date on July 8, 2007. In case anyone cares I picked out some interesting statistical data collected from visitors:

70.94% of all visitors use Internet Explorer to view the site
26.04% of all visitors use FireFox to view the site
01.56% of all visitors use Opera to view the site

34.14% of all visitors use some kind of unknown web connection to browse
25.39% of all visitors use DSL
20.92% of all visitors use Cable
14.88% of all visitors use T1
03.77% of all visitors use Dialup

80.70% of all visitors use US english
03.12% of all visitors use GB english
02.56% of all visitors use German
02.26% of all visitors use French

49.82% of all visitors come from the USA
08.80% of all visitors come from the United Kingdom
04.68% of all visitors come from Australia
04.32% of all visitors come from Canada
02.51% of all visitors come from Germany

49.82% of all visitors come from search engines
39.32% of all visitors come from reffering sites
10.86% of all visitors are direct traffic

53.34% of site views is the tutorials sections
24.45% of site views is the home page
08.83% of site views is the blog


January 2008 - Microsoft Access MVP for another year

I just received the good news that I have been re-awarded for yet another year as Microsoft MVP (Most Valuable Professional). If you are not familiar with the MVP program you might find this website interesting. It is a great honor to be associated with so many great minds. I personally want to thank all the Access MVPs for their great support and help through the past year. In particular I want to thank Rob (MS), Micheal (MS), Ken, Teresa, Ken, Robert (for giving me the opportunity to work with the best), Crystal for her everlasting kindness and friendship...Eric for his exceptional support as an MVP lead, Clint and Zac for their hard and dedicated work on awesome products and for pushing me to get done with school that much sooner ;). Last but not least special thanks to each member of UtterAccess forums. I would have not been able to do any of this without them.

With the good news out of the way I can finally take time to plan for attending the 2008 summit. Hopefully school will let me be flexible enough to see you all again.