What I Learned Today

Access vs. the Recursive Query

Today I was creating a tiered billing system (again in Access). The
natural solution in a (forgive the pun) normal database would be a
recursive/hierarchical query (each tier for a client has a parent tier
or is the root). Before even creating the structure for this query, I
was pretty sure that access wouldn't support such an advanced (and
until recently, also unavailable in it's big brother - SQL Server)
feature. A quick google comes up with this MSDN article from the
Access cookbook:
http://msdn.microsoft.com/en-us/library/aa188216(office.10).aspx.

The article explains a very manual way to query against a "self join"
(basically, make n copies of the table depending on how deep your
hierarchy is). This is, of course, *not* the correct way to write a
true recursive query, but it does give me a little bit of insight as
to why access/vba developers think the way they do. They are (to a
certain extent) trained into overly-simplified, somewhat limited
solutions.
They are the epitome of "good enough" solutions. Which, in a lot of
scenarios, is fine.

In the end, I added an "Order" column. Ugh.

What I Learned Today

I've been inspired by Seth Godin's "Is effort a myth?" blog entry
(http://sethgodin.typepad.com/seths_blog/2008/10/is-effort-a-myt.html)
to try and follow his "effort diet". The hardest part for me is going
to be the writing part, so here goes.

This will likely start as a series of posts on Access (since, ugh,
that's what I'm working with right now).

I'm not a huge Access fan, but I think it's a great tool for single
user/semi technical/entrepreneurial type environments.

I've spent the past week working on modifying a billing application.
Something I learned today:

The DoCmd object has a ton of handy little shortcuts hanging off of
it. In a non access environment, you'd have to write DTS, maybe some
API calls, and do a lot more scrubbing to replicate the functionality
in any of these methods. The one I used today is DoCmd.OutputTo.
This lets you open a "Save As..." dialog to save some dataset
(probably works for reports, too).

DoCmd.OutputTo acOutputQuery, tempQueryName, acFormatXLS

Where tempQueryName is an ad-hoc query I created based on user input.
Cool beans! It doesn't get much simpler than that. Just goes to show: Access isn't always the devil.

I'd still rather be using a real tool though :-p

(though if there was a nice IDE and ReSharper for Access, not sure
that I'd be nearly so confident in that statement)