Access 2007 Macro Fun

Okay, don't ask me how or why, but an issue with the Access 2007 Transfer Text macro came accross my desk. Macros... I thought these things would die out and go away, but apparently they are holding steady, if not increasing in popularity. But when macros break or don't work, who gets to fix them???? Developers... Developers, who would much rather be writing code and trying to figure out how to work the latest new design pattern into their current project. I digress....

Anyway, here are some tips for all you macrophiles wishing to use this particular macro.

• Use UNC paths
• Unless you are using the drives on your PC or ar 100% certain that mapped drives will remain the same in your organizaion for all users that will be using your app, you are only asking for trouble. Bone up on what a UNC path is here: http://compnetworking.about.com/od/windowsnetworking/g/unc-name.htm.
• Make sure the file you're importing is allowed by Access.
• The allowed extensions are: txt, csv, tab, asc, tmp, htm, html, but if you are so insistent that you need to allow some other extension, you can hack the registry using this MS article: http://support.microsoft.com/kb/306144. Please note that if you are using a 64 bit OS with 32 bit Office, you may need to also look in the HKLM\Software\Wow6432Node\ for the setting.
• Make sure you have enabled macros.
• This one is kind of a gimmie, but can get overlooked.

• If you cannot see the macro in the dropdown list, make sure you are showing all macro actions.
• The Transfer Text macro is not trusted or regarded as "safe" so Access hides it by default (that should tell you macrophiles something right there).

• Double check your import specification.
• You can get more info on this from Microsoft's documentation of the TransferText Macro here: http://office.microsoft.com/en-us/access-help/transfertext-macro-action-HA001226310.aspx.

So, if you are going to tread into the land of macros, make sure to play by the rules as much as possible. Utilize the microsoft documentation when setting them up, as it is rather thorough. If you run into trouble, retrace your steps, and double check everything. If all else fails and you do need to to go your resident developer, come with your hat in your hand and a shaker of salt (because he or she will probably make you eat it). Offer him or her a pizza, case of Mt. Dew, or 12 pack of their favorite beer for their trouble. After all, they are doing you a favor. :)

Hello Google Analytics! My name is... umm... err... meh

Okay, so here is the deal... I came up with this handy, dandy online newsroom app, that has a mailer service. The goal, cut down on the number of emails sent to all users of the company from 5 cagillion, bazillion to one.

What does it do, well it basically is your typical newsroom app, with categories, defined posters, and defined post approvers. Approved stories, of course, can be viewed via the application interface. Essentially people submit their would-be email announcement to this application and then once a day, at a specific time, an email is sent out, via a windows service, to all the users of the company, specifying headlines for any relavent information and highlighting the items for the current day.

The end result... big win in the eyes of the internal marketing folks, which of course bubbles up to the appropriate VP. Then, the inevitable happens. Email from the VP asking, "Can we track how many people open this email??" CRAP!! Well, tracking email stuff I know nothing about, but tracking websites, that I can handle!

Enter, Google Analytics. I have heard of it, and it is used to track and trend the external website for the company, but not anything internal. From what it looked like, Google Analytics wanted an FQDN when you set up a profile, and we use no such thing for intranet sites where I work for two reasons. 1) The end users here are not only a little dull, but lazy. I mean, who wants to type all those characters anyway. 2) The supported browser is IE which drops network credentials and prompts for a user ID and password for FQDNs that require authentication, unless you set them up in the intranet zone. Umm... can you say group policy nightmare for the engineers? Anyway, setting an FQDN for the site was not an option. So I turned to the IT staffer's handbook, Google.

I found a nice blog article by Mike Knowles (http://mikeknowles.com/blog/2009/07/11/EnableGoogleAnalyticsPageTrackingInASingleNameIntranetSite.aspx) explaining that this was possible by calling the _setDomainName method of the Google Analytics tracker object (<object>._setDomainName("none")). So I was off and running, but when I got the script, it looked nothing like what he had in his example, but rather looked like this:

Okay, so now what is going on? Well what is happening is the _gaq object is essentially a FIFO queue of commands that will be run by the Google Analytics tracking API. Documentation for the push method can be found here. Essentially, I needed to push the _setDomainName method into the list of commands before the _trackPageView method, as Mike's article suggests. So I modified my code to look like this:

All appears to be well now and I'm sure the VP will be yet another satisfied customer. Hopefully, this helps somebody out there. The documentation on the Google Analytics API is pretty good, and has some nice examples. Don't be afraid to venture forth there either, and remember, it will take 24 hours for the fruits of your labor to show up.