..:: Access-Freak :: Getting started with Microsoft(R) Access 2007 (Step by Step Tutorials/Samples...)

I will blog on this page about computer related topics like Microsoft®, Access, Java...as 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.

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. Amazon.com'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).

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 http://www.flv2mp3.com, 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 http://code.google.com/apis/maps/documentation/flash/reference.html and there are quite a few neat code samples at this location http://code.google.com/apis/maps/documentation/flash/examples/index.html.
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
import com.google.maps.LatLng;
import com.google.maps.Map;
import com.google.maps.MapEvent;
import com.google.maps.MapType;
import com.google.maps.LatLng;
import com.google.maps.LatLngBounds;
import com.google.maps.services.ClientGeocoder;
import com.google.maps.services.GeocodingEvent;
import com.google.maps.overlays.Marker;
import com.google.maps.overlays.MarkerOptions;
import com.google.maps.InfoWindowOptions;
import com.google.maps.controls.ZoomControl;
import com.google.maps.controls.ControlPosition;
import com.google.maps.controls.*;
import com.google.maps.MapMouseEvent;
import com.google.maps.services.GeocodingResponse;

//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);
this.addChild(map);
setChildIndex(map,0);

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

//geocode function
function doGeocode(event:Event):void {
//clear old marker overlays
map.clearOverlays();

//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!");
}
else{
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
geocoder.geocode(strAddress);
}
}

//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
map.addOverlay(marker);
}

//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();
myMapTypeControl.setControlPosition(bottomRight);
map.addControl(myMapTypeControl);

//enable zooming
map.enableContinuousZoom();
map.addControl(myMapTypeControl);
}

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]
AccessShortcutVersion=1
DatabaseName=DatabaseName.accdb
ObjectName=FormName
ObjectType=Form
Computer=YOURCOMPUTERNAME
DatabasePath=C:\Documents and Settings\YOURFOLDER\Desktop\DatabaseName.accdb
EnableRemote=0
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 (http://www.sqlite.org/download.html) and this SQLite Administrator (http://sqliteadmin.orbmu2k.de/). Both are free and very feature rich. Another interesting tool that might come in handy is this MDB to SQLite converter (http://code.google.com/p/mdb-sqlite/). 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.

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

October 2008 - Death by chocolate

Ok...so 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;

try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
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("email",210,50);
g.drawString("Date/Time signed up",340,50);
int i = 0;
while ( rs.next() ) {
//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
rs.close();
conn.close();
}
//System.out.println("Connected To Access");
}
catch (SQLException se)
{
System.out.println(se);
}
catch (Exception ex)
{
System.out.println(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 (his-airness@oli-s.de; 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.

Click To Play

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 = "you@email.com" '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

oTxtStream.Close

Set oTxtStream = Nothing
Set oFilesys = Nothing

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

With objMail
.BodyFormat = olFormatHTML
.HTMLBody = txtHTML
.Recipients.Add "user@email.com"
.Subject = "Customer Data"
'.Send if you want to send it directly without displaying on screen
.Display
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'> Company</td>" & _
"<td bgcolor='#7EA7CC'> Last Name</td>" & _
"<td bgcolor='#7EA7CC'> First Name</td>" & _
"<td bgcolor='#7EA7CC'> Job Title</td>" & _
"<td bgcolor='#7EA7CC'> Business Phone</td>" & _
"<td bgcolor='#7EA7CC'> City</td>" & _
"</tr>"

i = 0

Do While Not rs.EOF

If (i Mod 2 = 0) Then
rowColor = "<td bgcolor='#FFFFFF'> "
Else
rowColor = "<td bgcolor='#E1DFDF'> "
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>" & _
"</tr>"

rs.MoveNext
i = i + 1
Loop

strMsg = strMsg & "</table>"

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

With objMail
.BodyFormat = olFormatHTML
.HTMLBody = strMsg
.Recipients.Add "user@email.com"
.Subject = "Customer Data"
'.Send if you want to send it directly without displaying on screen
.Display
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'>" & " " & "<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 "user@email.com"
.Subject = "YourSubject"
'.Send if you want to send it directly without displaying on screen
.Display
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 Zazzle.com. 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

<quote:>

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

http://www.microsoft.com/downloads/details.aspx?FamilyId=D9AE78D9-9DC6-4B38-9FA6-2C745A175AED&displaylang=en

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.

Enjoy!

<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.