Follow @RoyOsherove on Twitter

How Access 2003 Helped Me Out

Kudos to MSFT for the latest installation of Office. Overall, Office 2003 beta is one great product. Outlook , being the main attraction for me for office use, is much more usable then it was before, with numerous usability enhancements.

There are a lot of new goodies to play with in all the office applications but perhaps the biggest change for me is the new found support for XML. And when I say support I mean an all out, no bars held, unconditional love, "When we say XML we mean it" kind of support. The kind that makes you giddy as much as I was when it helped me out just when I needed it most(I'll get to it, don't worry).

A small example of a cool XML support feature comes from Word. You can actually save a word document ("Save As...") in XML format. This means that every text, line formatting option and binary data is saved into a readable XML file to be consumed by any XML reader .Imagine Searching a word document using XPath!

Anyway, The thing I was most excited about that saved me hours of work is part of this XML revolution. It seems small. So small, infect, you'd wonder why it's not already there. Here's the story:

I'm currently working on a small project which involves converting proprietary format log files in my company into searchable data. My first goal was converting them to XML (Using Regex and XMLWriters - something which I will write an article about when I get some time). From that XML data, I could now load it into a Dataset and from there - Using the "Select" Statement of the DataTable Class - everything is much easier.

However, the Dataset holds some unexpected limitations: You can't do a Select on a specific field in a table, for one thing. Another biggie is that You can't execute Selects On multiple tables (Or any Database query for that matter).  I can see how letting Update's and Insert's into the Dataset object model can be a problem, since this violates the purpose of the Dataset of just 'holding' data, But issuing simple selects on multiple tables seems logical.

These limitations (Mainly the Multi table select) made me think about how I can import this XML data into an ACCESS database. This task seems very legitimate – I want my XML data to reside in an ACCESS database to provide me with better searching capabilities (Not to mention reporting and others..)

Amazingly  enough, all versions of Access prior to Access 2003 have no "Import XML" option!. Really. None. Nada. Zip. Zilch. It seems someone at MSFT just overlooked it , since it is such a legitimate feature. Access 2003 ,on the other hand, handles this import easily, with no problems. It was a breeze to do and saved me the hours of coding a custom transformation and import tool (Not to mention more hours to come when the requirements or XML schema would change and so on..)

I can only wonder as to how this never existed in a prior version of Access. It seems so trivial. Admittedly, I never had this need before so I assumed it exists. I can only imagine the painstaking efforts and man hours others had to do before this. I even bet there's a small market of tool vendors just for this missing feature! (Do you know of any tools to do this ?). Anyway, Kudos to MSFT, Better late then never.

P.S

Here's a quick one for you:

 Try creating a word document as XML and then try importing it into ACCESS. It doesn't work very good in this case! You get some funky  "ImportErrors" table entries  on truncation and stuff. Oh well. Hopefully it will work until Office 2004 ;)

P.S. 2

Anyone know of a good MDB comparison tool? I want to discover all the changes between two MDB files. I could not locate anything that can satisfy this seemingly simple request (Probably was not looking in the right places…)

3D Cynicism

Another Free Book