Thoughts on replacing FileMaker Pro 11

Once upon a time database apps were priced and sold as a consumer product. Those were the days when computers were marketed as a replacement for a recipe book.

There were dozens of consumer oriented database products then, priced from under $15 shareware apps to around $100 for relatively full featured commercial products (AppleWorks, etc). Some of these products, particularly on the Mac, were astounding (though high end ones were priced as business products).

That era seems weird now. It definitively ended when Apple discontinued Bento. Shortly afterwords FileMaker was priced as a business-only product. Panorama X has followed a similar path. Tap Forms is sold at a consumer price, but it failed my basic trial tests.

The era of the affordable personal database app has passed. The only commercial remnant on any platform is Microsoft Access — somehow Microsoft can’t quite kill it. LibreOffice Base began as Microsoft Access clone and sort of runs on macOS, but parts of it still require Java.

My only current use case for FileMaker Pro is that it runs my personal password database. I could move that (yay data freedom!) to Microsoft Access in a VM, but Windows 10 is a monstrous amount of baggage to keep around just to run a personal password manager.

I could finally migrate to 1Password. Emily and I use it and I have to periodically dump data into it manually from my old password database. I wonder how long that product will last with Apple incorporating credential management into the operating system though.

Or I could adopt the lazy choice and stay on Sierra and FileMaker Pro 11 a while longer. I like the sound of that one.

Clean install of windows 10 on VMWare Mac - getting a license ($130)

I needed to use Microsoft Access.

I know what you’re thinking. You’re wrong. Yes, Access is a horrible old patchwork beast, but for some kind of data manipulation it’s still unequaled. It’s particularly good at mixing local data store with ODBC stores. It helps that I know where the bugs are buried — though Access 365 on Windows 10 is particularly buggy.

So about two weeks ago I fired up my old copy of VMWare Fusion 7, downloaded Windows Pro 64bit OEM and used my multi-platform multi-machine Office 365 license to install Microsoft Access. It was all relatively painless. I did find Fusion 7 isn’t happy with Yosemite virtual desktop, so I only use full screen Win 10 in just one display. That works until I do my El Capitan/Fusion 8 update.

The entire package takes up about 23GB on an external SSD. 

I did wonder how I was going to pay for Win 10. It was working without complaint. I figured I’d get some kind of notice. About two weeks after installation it began showing a watermark on the screen requesting activation and personalization features were turned off. That was a polite reminder.

I went hunting for a Win 7 or 8 license  to get the free upgrade — but no-one I knew had one to spare. My own Windows licenses was for XP, that didn’t help. I couldn’t find any good educational deals either. Amazon had lots of Win 7/8/10 licenses at suspiciously low prices, all of which seemed a mixture of counterfeit and genuine product. (Amazon — the crooked pawn shop of the Net.)

In the end I remembered PC Connection and found that while MSFT charges $200 for a Win 10 Pro 64bit license PC Connection had an OEM version for about $145. Once I knew the right price range I found an OEM version on Amazon that shipped from Amazon for about $135. I can’t link to it because Amazon’s fraud-friendly habit of consolidating product listings that ship from multiple sources mixed in their source with $105 versions that seem to include counterfeits.

It came in a legitimate looking Microsoft white envelope holding some kind of disk thing envelope (what’s a DVD?) with a sticker and license number on the front of the inner envelope. The license number was all I needed, it worked.

The weirdly under-appreciated power of Microsoft Access - Saved queries behave like Oracle Views.

It was only when I was recently forced to explore SQL subqueries (there's a reason people invented functions and even Entity SQL) that I realized why Microsoft Access [1] is so much more powerful than, say, SQL Management Studio.

Yeah, you can access lots of diverse data sources, do (except, weirdly, for unions) ANSI SQL with an excellent GUI tool, store data locally, etc etc.

That's good, but the weird power is that Access treats queries as though they were views. It's trivial to do deeply nested subqueries, create libraries of modular queries, etc.

Except I must be missing something, because this is too obviously useful...

[1] Old, weird, creaky, infested, bizarre, baroque Microsoft Access, whose useful bits have changed very little in the past 16 years and which is a Frankenstein of every Microsoft technology since 1990.

Access 2003: Join expression not supported with VBA type conversion

One of the stranger bits of my life is that I often do data manipulation using Microsoft Access 2003 in a Windows 2003 server VM.

 

It's a long story. I suspect I'm not completely alone however. Access 2003 is unbeatable for certain kinds of data wrangling across disparate data sources. Access 2007 was a quite severe regression (post-2000, software  regressions are increasingly common).

 

So perhaps it's worth sharing the weirdest bug I've seen in a while. I was unable to do a "left" join on two subqueries where one of the keys involved a type conversion (string/long using VBA CStr() or CLng() - I tested both ways). I got a meaningless "Join expression not supported" error message.

 

I think this has worked fine for years. I wonder if a forced security update broke something. I found if I switched the query from 'Dynaset' to 'Dynaset (Inconsistent Updates)' it worked. Snapshot didn't work.

 

Now you know.

 

For me, it's a sign that I either need to switch to Access 2007/2010 or switch jobs. (I don't think 2010 is 64bit, so I still face the problems with ODBC drivers and bitness. I might be better to try Access 2007 in an XP VM.)

Update: I'm seeing other issues. It feels like something very bad has happened, probably as a result of a Microsoft security update, but I can't tell what.

 

Update 7/4/2012: I'm not quite sure what's going on, still having some issues. It might be an ODBC issue. I think the error message is a red herring.

Manipulating JSON data in a traditional relational database (Microsoft Access, FileMaker Pro, Converters)

While I wait to see if Pinboard can fix their Google Reader JSON (JavaScript Object Notation) import, and while I consider Google Reader Share JSON import into WordPress, I'm also exploring JSON import/export tools. If, for example, I could import JSON into FileMaker Pro or other data management tool I might be able to manipulate the archive and produce a more useful WordPress import.

StackOverflow and its kin have a good set of references on this topic. Note that CSV can manage only very simple JSON; we really want native importers similar to what Microsoft Access tried with XML [1]. I suspect one approach might be to convert JSON to XML then use Microsoft Access 2010 import.

Incidentally, this topic veered off unexpectedly into something that's actually relevant to my work life and a Strata conference I'm attending in a few weeks.

As of today here are some of my pointers ...

• What is “JSON” and what does it have to do with distributed computing? « DivConq: great reference (see below for DivConq series on Access)
• JSON - Wikipedia, the free encyclopedia: I liked the comparison to XML [1]
• JSON.org: points to libraries
• 18.2. json — JSON encoder and decoder — Python v2.7.2 documentation: If I have to use a programming library to manipulate Google Reader json files my choices are probably Python or Javascript and I prefer Python.
• conversion - Convert JSON format to CSV format (for MS Excel) - Stack Overflow: JavaScript
• convert from json to csv using python - Stack Overflow
• Download JSON data and convert it to CSV using Python - Stack Overflow
• python - Convert JSON to CSV - Stack Overflow
• javascript - Python - convert csv file to JSON - Stack Overflow

For me this DivConq series was particularly useful because it placed JSON nosql processing in a familiar context - Microsoft Access.

• What is “JSON” and what does it have to do with distributed computing? « DivConq: best single reference for my purposes
• Export a Microsoft Access Database to JSON (Northwind Example) « DivConq
• Why Cassandra « DivConq
• Installing and Running the Cassandra Database « DivConq
• Migrate a Relational Database Structure into a NoSQL Cassandra Structure (Part I) « DivConq
• Migrate a Relational Database into Cassandra (Part II – Northwind Planning) « DivConq
• Migrate a Relational Database into Cassandra (Part III – Northwind Conversion) « DivConq
• Migrate a Relational Database into Cassandra (Part IV – Northwind Import) « DivConq

Maybe I should start using Apache Cassandra to manipulate my Google Reader JSON archive and prepare it for WordPress processing. For example ... Cassandra Development Environment in Mac OS Snow Leopard « BigDiver.

[1] I doubt JSON has truly significant advantages over XML as a data interchange format (see JSON Example and wikipedia xml/json). Alas, nobody asked me. Fashion is more powerful than geeks imagine.

Oracle ODBC access with 64 bit Windows 7

I know it's a dying technology, but I still get a lot of value out of Microsoft Access manipulation of data stored in Oracle tables. I have a Windows 2003 server VM that runs Access 2003 and has 32 bit driver access to our corporate Oracle tables.

We're moving to Windows 7 (finally, and boy is Win 7 ugly), so in addition to my ancient VM I looked into using Access 2007 with those tables. The first thing I discovered was the 64bit Oracle 10.x installer didn't install the expected Oracle ODBC drivers. I also noticed that Windows no longer includes Microsoft's ODBC driver ...

Data Access Technologies Road Map

... Oracle ODBC and Oracle OLE DB: The Microsoft Oracle ODBC Driver (Oracle ODBC) and Microsoft OLE DB Provider for Oracle (Oracle OLE DB) provide access to Oracle database servers. They are built by using Oracle Call Interface (OCI) version 7 and provide full support for Oracle 7. Also, it uses Oracle 7 emulation to provide limited support for Oracle 8 databases. Oracle no longer supports applications that use OCI version 7 calls. These technologies are deprecated. If you are using Oracle data sources, you should migrate to Oracle-supplied driver and provider....

I spent a few minutes poking around the net, and it looks like there's no easy way to install Oracle's drivers into 64bit Windows. Indeed, it's not clear they exist. I'll do a bit more research with some more focused searches and post an update here.

Progress, as is usual, is a mixed bag. Access was an ugly, awkward Frankenstein application, but there's nothing like it for mixing and matching a wide range of disparate data sources. The deeply nested and reusable query modules could be horrendously slow -- but for what I do the performance was often adequate.

I'll miss Access.

Update: Superuser.com tells me that there exists a 64bit Oracle 11g driver. However, the user's unspecified version of Access is 32bit and it can't use the 64 bit driver. We're on Oracle 10g.

Microsoft Access: Datatypes and Switch vs Iff program flow statements

I think this may be the most obscure blog post I’ve ever written. I only write it because, even after I knew the answer, I couldn’t find this documented anywhere. Perhaps this will help someone searching for an answer someday.

I’ve tested this in Access 2003, but not Access 2007 (a disastrous upgrade btw).

If you’re actually reading this, you are using Microsoft Access and you have been using the "Switch” “Program Flow Built-In Function”. This is a unique to Access hack that allows one to mix a bit of programming logic into a query. In the pile of hacks that make up Microsoft Access this one’s a bit of a gem. You can mangle quite a bit of data with it. I believe it is a call out to Access Basic.

You used the Switch statement with a numeric data type. When you were done you attempted to do something with the result, such as a Join on another numeric. You then saw a “Type mismatch in expression.” error message.

It’s not documented in Access, but the Switch statement forces the result to be a string. If you don’t want to change data types to a string you should use nested IIF statements in place of the Switch statement.

You can also wrap the Switch statement in another function that casts the string to a numeric value.

That is all.

PS.

As long as you’ve read this far, here are some of my personal notes on the Switch statement…

Access has 3 Program Flow built-in functions: Choose, IIf and Switch. I use IIf and Switch.

These are taken from Access Basic. They can be used in Access Basic functions, or they can be used as functions in a column name (where I use them).

Switch has the advantage that it can handle multiple conditions cleanly, and include an error statement. It is poorly documented (see below) [1]. It can be difficult to construct a complex Switch in the Access editor or function wizard, it's better to construct it in a text editor and paste it in.

Switch can use the True and False operators. So if you want to have an error result used when all matches fail, you set "expr-n" to the operator "True" (no quotes). You might think  you should put the most common condition first to reduce execution time, but Switch always evaluates all the expressions.

Example of use of Switch (in this case the test would never be reached, Null and Not Null covers all branches):

SNAP_LOCAL_CONCEPT_TYPE: Switch([RESULT_UNITS] Is Null,"NumericObservationWithoutUnits",[RESULT_UNITS] Is Not Null,"NumericObservationWithUnits",True,"ERROR: IMPOSSIBLE")

[1] From Access Help ...

Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True.

Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

The Switch function syntax has these parts:

Part Description
expr Required. Variant expression you want to evaluate.
value Required. Value or expression to be returned if the corresponding expression is True.

The Switch function argument list consists of pairs of expressions and values. The expressions are evaluated from left to right, and the value associated with the first expression to evaluate to True is returned. If the parts aren't properly paired, a run-time error occurs. For example, if expr-1 is True, Switch returns value-1. If expr-1 is False, but expr-2 is True, Switch returns value-2, and so on.

Switch returns a Null value if:

• None of the expressions is True.
• The first True expression has a corresponding value that is Null.

Switch evaluates all of the expressions, even though it returns only one of them. For this reason, you should watch for undesirable side effects. For example, if the evaluation of any expression results in a division by zero error, an error occurs.

Where did my Access 2007 object descriptions go? SP2 killed them.

Sometimes I have to use Access 2007. It’s always painful, but one of the worst agony comes because there are no object descriptions anymore. It’s hard enough to manage Access entities with object descriptions, but with Access 2007 all of my Access 2003 table and query descriptions are gone.

It’s weird because I swear I used to see them buried away in Access 2007 when I used it a year or two ago. Not in a useful place mind you, but at least they existed.

Turns out Office SP2 killed ‘em off:

After you apply 2007 Office system Service Pack 2, the description of an object does not appear in the Navigation Pane in Access 2007. This problem occurs when the Navigation Pane is viewed by Details.

There’s an August 2009 hotfix for this and sundry other Access 2007 bugs, unlike some hotfixes it is downloadable. I assume it will be included in Office 2007 SP3 when that comes out.

Wicked bug.

Relational database 101: A Microsoft gem hidden in plain sight

Colleagues sometimes ask me about where to get a basic introduction to databases. My informatics students should be asking that, but they usually don’t.

I’ve not had a very good response. It’s been eons since my first encounter with data models, ER diagrams, keys and the like. I dimly recalled the monstrous Microsoft Access manuals of old, which often included quite good tutorials.

That’s what led me to crack open the Table of Contents of Microsoft Access 2007. It took me a while to figure out that how to do that. You can either click on a tiny blue circle/question mark in the far right of the app bar, or you can type old F1. You can then click the wee blue book icon to see a Table of Contents or you can look at “Browse Access Help”. From either location you’ll see “Database design” as shown here in the Table of Contents:

Microsoft has put a lot of material behind that little Database design link:

Starting with Database design basics:

Wow. I feel like I’ve entered a dusty old library frozen in time, and opened a book untouched for decades (I did that once in rural Bangladesh – quite memorable). Microsoft’s tech writers are terribly underappreciated.

The materials include demonstrations, references to sample databases, etc. The vast majority of this material is applicable to any relational database, from poor little Access to Oracle.

Many students and tech workers have an unused copy of Microsoft Access near at hand. Even if you never intend to do anything with Microsoft Access, you might as well take advantage of the excellent interactive textbook that comes with it.

Microsoft Access 2007 – RIP

I’ve seen software die.

First the code gets crufty. Features pile on, but half of ‘em don’t work right. Old features might or might not work. There are security holes.

Then a bright new team gets the gig. Old code is hacked out, new ideas are grafted onto old models. Usually you end up with a cacophonous concatenation.

That’s how Access 2007 smells. I know the team tried hard, but it’s a train wreck.

It’s not just a few bugs, or one or two missing features, or a limited design flop. It’s all of the above and more. As a power tool for hacking relational data it’s following the FrontPage path to oblivion.

Yeah, I’ve written before about how bad Access 2007 is. Even so, I think I was in denial. It took trying to complete a significant data manipulation project to make me face facts.

Microsoft isn’t going to fix Access. They want to sell the latest iteration of SQL Server and their Sharepoint services – Access is a costly distraction that happens to work pretty well with the Great Satan (Oracle).

There will be another release or two, then it will follow the path of FrontPage - which was once part of the Office Suite.

See also:

• Access 2007- It's really bad
• Living with Access 2007- -Disabled Mode-, Trust Center, and a Bug
• Microsoft's Access 2003 to Access 2007 animated reference guide (Flash)

Microsoft Access 2007 - it's still lousy

I'm back using Access 2007 a year after I wrote about Microsoft Access 2007 reliability issues and suggested workarounds.

In the interim I've been using Access 2003 again.

There are some good things about 2003 (ok, just Sharepoint support), but, by and large, it's busted. It's broken in deep and inexplicable ways. Heaven be your friend if you should change a column name -- you may get weird and persistent side-effects.

Since nobody can imagine Microsoft going away, this kind of thing is more than a bit depressing. It's like spending an eternity in Limbo ...

Update 10/22/09: A bit more detail on the two latest bugs, now that I've figured them out.

I was getting a common and useless error message: The setting you entered isn't valid for this property every time I ran a query on a link to a Sharepoint List who's schema had changed since I'd created the link. In particular a field had gone from memo to string. I had to delete the link and create new one. Access 2007 is supposed to be able to regenerate its link on command, but it turns out that doesn't work.

The other bug I got was related to creating an alias to a Sharepoint List column name where the alias happened to be identical to another Sharepoint Column name that wasn't part of the query. Turns out it sort of works, but not really.
--
My Google Reader Shared items (feed)

SyncWiz for Outlook Review - how not to do trial software

When I was using Outlook 2003, I had a kludgy but reasonable way to get my corporate Outlook contacts to a primarily personal iPhone.

I started out using MobileMe to get PST transported contacts to OS X Address Book.

Once I'd done the bulk of the work I found my workplace Outlook 2003 could export .VCF (vCard) files that OS X address book could import. Contacts don't change too often, so I just sorted by Contacts by date revised and emailed the new ones.

Then I went to Outlook 2007, the poor, broken, abused step-child of the Office 2007 suite. Outlook was ugly but serviceable in 2003, but Microsoft butchered it for the 2007 transition. One of the many broken bits was the semi-documented vCard export. In 2007, the only vCard export option is tied to sending them as email attachments.

I could live with that, but in OS X Address Book these Outlook 2007 vCards have notes full of unreadable XML.

So, in desperation, I closed my eyes, tried to ignore my past experiences with Outlook Add-Ins, and tried this product in trial mode ...

SyncWiz

... With SyncWiz convert selected or all of your items to vCard, iCard, iCalendar (iCal), or vCalendar file format. This file is so portable and compressible, that you can easily send the whole folder to anyone (4000 contacts in a zipped Vcard file is less than 100Kb)...

After installation I tried the VCF export. SyncWiz told me I had more than 5 contacts, so it quit. It didn't export five then stop, it just quit.

I decided that was a strong sign this wasn't a serious product, so I uninstalled it.

I then restarted Outlook 2007 -- and found the Add-In had not been removed and Outlook was revolted by it.

So now Outlook showed the SyncWiz add-in as disabled. Fine, but I'd just as soon delete it forever.

Except, you can't delete an Outlook Plug-In from Outlook 2007. You can only inactivate it, and admire the festering corpse.

I hate Outlook Add-Ins. I ain't well disposed to either Microsoft or Outlook 2007 either ...

Project Contacts: Now mixing Outlook/Exchange, PST file, Outlook/Home, MobileMe Sync, OS X Address Book and the iPhone.

A recent Apple Discussion Thread led me to take a new direction with Project Contacts.

To put it mildly, there’s a lot of complexity in this post. However initial results are very positive. This method will require me to purchase a MobileMe account, something I was hoping to avoid. (See below for a partial index to past efforts.)

The end result is that I have a single collection of work/home contacts across iPhone and OS X Address book at home. The work contacts portion of this collection is updated weekly. At this time the update is one way, from Work to Home.

For anyone who may be facing these challenges, I have provided a skeletal outline here of what I did and what I would do if starting from scratch. You will see how insanely complex this is. Note that as of this writing the care PIM data that was once in Palm/Desktop is now scattered across Google (Calendar and a detached set of Contacts), Outlook/corporate, Toodledo and MobileMe. Everything does come together in my iPhone. The current solution involves a wide variety of vendors. For example, Apple's MobileMe calendaring is pathetic; far weaker than Google Calendar and a joke compared to Outlook (which makes Apple's no-show on tasks even more crazy). On the other hand Apple's Contact framework is very robust, much stronger than Google and a rival to Outlook.

This ruddy mess is a real indictment of Apple and a fat opportunity for the PalmPre.

So much for prelude. Here’s the outline, strictly for the uber-geek:

Here’s what I actually did:

• Copying contacts from Outlook/Exchange root to Outlook PST caused the EX (Exchange server x.500) email addresses to be updated to SMTP (standard internet) email addresses.
• PST on thumb drive to home (simple)
• Copy into Home Contacts
• Sync to MobileMe
• In MobileMe web assign all to a Group
• Sync to OS X Address Book (small conflicts)
• Sync to iPhone (ok)
• Sync to Outlook Home: Each Group in OS X Address Book became a Contacts Subfolder in Office 2003. This means the cardinality relationship to Address to Group may have to be One to One.

Expected problem:

• Contact belongs to two Groups in OS X Address Book (multiple inheritance)
• Contact assigned to ONE Subfolder in Office 2003.
• In OS X change Group assignments.
• What happens in Outlook?

Here’s what I suggest doing (LOTS of backups of OS X Address Book as go along)

1. Outlook/Corporate create PST file, copy work contacts. Do not copy lists or groups of contacts, only contacts.
   
2. PST file to thumb drive
3. Home Outlook mount PST data file. Make sure Contacts folder is empty
4. Sync iPhone to OS X Address Book
5. Create new group in OS X Address Book that will hold corporate contacts
6. Sync to fresh MobileMe Account
7. Sync fresh MobileMe account to home Outlook
8. Now Outlook will have an empty subfolder. Dump the Contacts transported into the PST file into that empty folder.
9. Sync from Outlook to MobileMe
10. Sync from MobileMe to OS X Address Book
11. Sync to iPhone

A partial index to past and related efforts at work/home Contact integration:

• Synchronization (all)
• MobileMe (all)
• Outlook (all)
• Contacts (all)
• Synchronization is Hell.
• Root causes: Why you can't sync your work and home calendars to your phone
• Project Contacts: Integration across iPhone, Google and whatever
• Work home contact integration: Outlook to Google to OS X Address Book (X.500 EX email problem)
• Synchronization case study: Address Book, the iPhone, and Google Contacts
• iPhone sync to Google contacts - 3 methods and work/home implications
• Outlook contacts to OS X Address Book - 3 techniques
• Google saves my iPhone (Google Exchange Server emulation for iPhone)
• Googaby: Novel approach to Google Contacts and OS X Address Book integration
• OS X Address Book to Google Apps Contacs using Google ActiveSync
• Work home contact integration: Outlook to Google to OS X Address Book
• Access OS X Address Book records using OpenOffice database?
• Synchronizing iPhone and Gmail contacts: getting OS X Address Book into Gmail
• gSyncit vs Google Calendar Sync with Outlook 2007
• gSyncit for Outlook 2007 to Google Calendar and Contacts Sync
• Prediction: We won’t see a fixed MobileMe until 10.6 is out
• MobileMe alternatives – including for iPhone Sync (MacWorld)
• Palm Pre is Exchange sync based
• iPhone: MobileMe vs. Google services
• MobileMe, Microsoft Outlook, Exchange, iTunes and yes, sync Hell
• Is it too late to go back to Palm 1994?
• Palm to iPhone - the update
• Synchronization is hard - more evidence
• Palm to iPhone migration - address book and notes
• Spanning Sync tells us what's wrong with Google's free CalDAV sync
• Apple can't do synchronization - again
• MobileMe syncs with Outlook (but then Apple turned it off for Exchange clients)
• Interoperability and my Contact information: Microsoft Outlook and Access, FileMaker Pro and Palm Contacts

Update 5/15/09: Now that I've got this working I'm trying various optimizations. For example, my contacts don't change that often. It's easy to create a view in Outlook that sorts by modified date. It's fairly trivial to send out a few changed .msg in an email and let Outlook at home merge them in. I still have to think about how to work with Google's Contacts, but I'm seeing a few interesting options.

It's weird how powerful MobileMe contacts are, yet how feeble MobileMe calendaring is. We're due for a MobileMe relauch, so I expect some developments before September.

Lastly, I should probably mention why I took this route. The more I looked at the workarounds for getting Outlook/Exchange corporate contact data to Google or the OS X Address Book the worse they looked. Their are problems with data models, problems with the intractable horror of the Outlook Add-In architecture, problems with Exchange server and problems with corporate access. This approach is crude, but for me, once I figure it out, fairly painless. I think it will fly until we get something better.

In the meantime, I'm rooting for the PalmPre to humiliate Apple and make them reconsider the direction they're taking.

Update 5/15/09b: Now that I've got this setup working I can see weird new affordances. For example, one of my top 10 OS X frustrations is the inability of FileMaker to work with the Address Book SQLite data stores. Ahh, but now my address data is synchronized between Outlook/Home and Address Book, and I can use Microsoft Access with Outlook/Home. So I can clean things up there, and MobileMe sync will propagate my fixes. I think I'll find a way now to get my Google Contacts into the battlefront.

Update 5/16/09: Great comment by Faheem, who's achieved a similar outcome using Plaxo without paying for MobileMe. I took a look, but Plaxo didn't feel right for me.

Office 2007 - the bizarre bits

I've been using Office 2007 on XP for a few months.

My overall impressions are:

1. Excel: few real changes, maybe slightly improved
2. PowerPoint: Using themes in PPT 2003 mode is ... a recipe for madness.
   
3. Word: mostly better, but some odd new gaps
4. Access: buggy and mostly worse for what I do (data analysis, manipulation)
5. Outlook: almost every significant new feature is broken, some hideously broken. For heaven's sake, don't try the feed reader sync with IE 7 on a non-trivial feed list -- unless you like to see your machine meltdown. Some ancient bugs were fixed.
   

If you're on Office 2003 you should wait about 2 months post Office 2007 SP2.

The main benefits of Office 2007 seem to be that it does work, awkwardly, with Sharepoint 2007 document management, whereas Office 2003 is a total fail. (Non-Office applications are even worse, which suggests that the European anti-trust effort has finally collapsed.)

Which brings me to the bizarre stuff I keep running across then forget about. I'll put one in one or two then add more. These aren't bugs -- those are too numerous (esp in Outlook 2007) to list. These aren't rants on the half-baked user-customizeable quick reference bar or the new UI (which is dumb but I'm used to it now), these are just bizarre design choice.

1. Word tables: There's a whizzy new table creator, but we've lost control over row and cell attributes. Try to copy table cell shading from one cell to another cell. The table overall didn't work; the Office team should figure out what went wrong and fix it.
2. Word auto-format: Even more intrusive, though less often wrong.
3. In Outlook 2007, unlike 2003, you can't
   
4. PowerPoint "Theme" revision - at least when used in PPT 2003 compatibility mode. Oh, my, Lord ...

Update 11/4/09: The more I use it, the more I wish Microsoft would go away (they won't)

1. In Outlook there's no visual indicator to differentiate between blocked times (appointments) and meetings (has attendees). When I look at my calendar I'm not sure whether I'm late for some work I set for myself or if I've got people waiting for me.
2. I hate, hate, hate that I can't get PowerPoint to open windows on more than a single monitor. (To be fair, I think Microsoft screwed this up ages ago).

See also:

• Lessons from Outlook 2007 Notes colors
• Reverting from Office 2007 to Office 2003: MAPI32.DLL vs. MSMAPI32.dll
• Office 2007 Diagnostics includes memory and disk check tools

Microsoft Access 2007 reliability issues and suggested workarounds

The following TechRepublic list was written for Access 2007. My impression is that Access 2003 is becoming less reliable over time, probably due to security fixes that aren’t fully tested on 2003. Even so, I think 2007 may have more corruption problems, though presumably Microsoft will eventually fix them.

10 ways to prevent Access database corruption | 10 Things | TechRepublic.com

#1: Split your database …

#3: Don’t use memo fields

Avoid using memo fields if possible. They often, indirectly, cause corruption….  If you need memo fields, keep them in separate tables and create a one-to-one relationship to the parent table(s). Even better, move memo tables to a separate backend database file and name the file accordingly, to indicate its purpose.

#4: Don’t store picture files

Usually, you shouldn’t store picture files in a database. If you must, treat them the same way you would a memo field (see #3)…

#5: Create temporary tables to speed up queries

If you run complex or nested queries (where one query pulls data from others that hit still others), Access may write a lot of temporary data that you never see. Most often, this happens when a query that works with a small amount of data performs slowly, putting stress on the JET engine. If Access chokes during this process, you can end up with a corrupt backend file.

To prevent this problem, write some of the temporary data to temporary tables. There’s no universal method to recommend. Analyze the specifics and run some tests to find the best solution. However, sometimes the use of just one temporary table can minimize the chance of corruption and speed up the queries by a factor of 10 or more.

#6: Be careful with wireless networks (WiFi) (and other less reliable network connections)

Access 2003 does not support views, I think they would help with #5.

The list of “risky” behaviors is an indictment of Microsoft’s software quality. Memo fields, for example, are very useful! I never used to see problems with nested queries in 2003, but I have recently.

My personal favorite is #1 on the list. I store data that isn’t going to be changed in a read-only file (set read-only file attribute).

The NULL comparison trap: Escape with NVL (Oracle) and Nz (Access)

This is disgusting. I’m sure I once knew better; maybe writing this up will help. A quick Google search tells me I’m not alone in my stupidity, so maybe this will help others who aren’t truly DBAs, but have to play with data.

I got caught be the NULL trap. I think this might be why some databases are designed to never allow NULL values, but to use, for example, an empty string.

The NULL trap is impeccably logical, but infuriating.

Say you compare the values of two fields, one of which is Non-Null and one is Null. Are they different?

I think so when I look at them, but I think the “proper” answer is “NULL”. Meaning – “I don’t know”.

That’s because NULL doesn’t mean “nothing”, it means “could be anything”.

Consider this Microsoft Access 2003 query:

SELECT pub_CC_qry.CG_CC_ID, pub_CC_prior_qry.[Abbreviation prior], pub_CC_qry.Abbreviation
FROM pub_CC_qry INNER JOIN pub_CC_prior_qry ON pub_CC_qry.CG_CC_ID = pub_CC_prior_qry.CG_CC_ID
WHERE (((pub_CC_prior_qry.[Abbreviation prior])<>[abbreviation]));

It returns 62 rows. It doesn’t return 28 rows where one of the two columns contains a null value.
This qeury returns 90 rows, but it’s a mess:

SELECT pub_CC_qry.CG_CC_ID, pub_CC_prior_qry.[Abbreviation prior], pub_CC_qry.Abbreviation
FROM pub_CC_qry INNER JOIN pub_CC_prior_qry ON pub_CC_qry.CG_CC_ID = pub_CC_prior_qry.CG_CC_ID
WHERE (((pub_CC_prior_qry.[Abbreviation prior]) Is Null) AND ((pub_CC_qry.Abbreviation) Is Not Null)) OR (((pub_CC_prior_qry.[Abbreviation prior]) Is Not Null) AND ((pub_CC_qry.Abbreviation) Is Null));

The secret is the Nz function. Wrap any column that might contain a NULL in Nz, and LOT of things, including iff comparison functions and SQL comparisons work a lot nicer:

SELECT pub_CC_qry.CG_CC_ID, Nz([Abbreviation prior]) AS Abbreviation_Prior, pub_CC_qry.Abbreviation
FROM pub_CC_qry INNER JOIN pub_CC_prior_qry ON pub_CC_qry.CG_CC_ID = pub_CC_prior_qry.CG_CC_ID
WHERE (((Nz([Abbreviation prior]))<>Nz([abbreviation])));

Arrgggh.

So what about Oracle?

I’m not precisely sure how Oracle is handling this situation in comparisons. I’m still researching Oracle’s behavior. It does, however, have a function similar to the Access Nz function; it’s the nvl function:

How to use the nvl function in Oracle

The NVL function is used to replace NULL values by another value.
The syntax for the NVL function is:
NVL( value_in, replace_with )
value_in if the function to test on null values. The value_in field can have a datatype char, varchar2, date or number datatype.
replace_with is the value that is returned if value_in has a null value. The NVL statement works like this pl/sql code:
if (value_in is NULL) thenreturn replace_with;elsereturn value_in;end if;Sample code: select nvl(salary, 0)from employees;select nvl(ref_code,'Unknown')from users;

Here’s an example, and a response from a friendly DBA:

Select * from A, B where A.ID = B.ID and A.name != B.nom

Imagine

A.ID = B.ID = 7
A.name is NULL
B.nom is not Null

How do you get Oracle to return the row in B where B.ID=7?

Answer (I think for our purposes this would work if we just used NVL([column_name],’’) for our string comparisons)

I think what you're after is the NVL function, which converts a NULL to a substitute value for comparison's sake.

So in your query, we would replace a null A.NAME with an outlandish value like 'A1B2C3', and replace a null B.NOM with same.

SELECT * FROM A, B WHERE A.ID = B.ID AND NVL(A.NAME, 'A1B2C3') != NVL(B.NOM, 'A1B2C3');

The above query will *not* return rows where A.ID = B.ID and A.NAME and B.NOM are *both* null. If you also want those rows, then you need to tag on that condition to the end of the query like this:

SELECT * FROM A, B WHERE A.ID = B.ID AND ((NVL(A.NAME, 'A1B2C3') != NVL(B.NOM, 'A1B2C3')) OR ((A.NAME IS NULL) AND (B.NOM IS NULL)));

Access 2003 and bugs with special characters and escape sequences

Microsoft Access has some curious behaviors with certain characters. They can be very hard to reference in searches or text operations. Most software (ex. grep) uses “escape sequences” or delimiters for characters like #-()[]?* etc that may have special meaning, but Access struggles here.

From the Access Help files (which are impressive, but often like reading the scrolls of a long lost civilization), we can see that even the expected behavior is very obscure:

• Wildcard characters are meant to be used with text data types, although you can sometimes use them successfully with other data types, such as dates, if you don't change the Regional Settings properties for these data types.
• When using wildcard characters to search for an asterisk (*), question mark (?), number sign (#), opening bracket ([), or hyphen (-), you must enclose the item you're searching for in brackets. For example, to search for a
  question mark, type [?] in the Find dialog box.
• If you're searching for a hyphen and other characters simultaneously, place the hyphen before or after all the other characters inside the brackets. (However, if you have an exclamation point (!) after the opening bracket, place the hyphen after the exclamation point.)
• If you're searching for an exclamation point (!) or closing bracket (]), you don't need to enclose it in brackets.
• You can't search for the opening and closing brackets ([ ]) together because Microsoft Access interprets this combination as a zero-length string. You must enclose the opening and closing brackets in brackets ([[ ]]).
• A Microsoft Access project uses different wildcard characters than an Access database uses. For more information, search the Microsoft SQL Server Books Online index for "wildcard characters"…

I love the last bit, where Microsoft hints that even the odd rules Access once used won’t necessarily keep working.

As of June 2008 though things are worse in a patched version of Access 2003. I couldn’t get search on a [ character to work at all, and I had to escape a single quote using: """".

I think software ages at dog speeds, and Access is well into senescence.

Microsoft Access 2GB file size limitation and the Invalid Argument error message

Every time I ran “Make Table” in Access 2003 I got an “Invalid Argument” error message.

Google found the explanation in seconds:

Error message when you run an append query, run a make-table query, or import data in a large Access database file: "Invalid argument"

In Microsoft Access, when you try to run an append query, to run a make-table query, or to import data in a large Microsoft Access database file, you may receive the following error message:

Invalid argument.

This problem occurs when the size of the Access database file approaches the 2 gigabyte (GB) size limitation.

The limitation still exists in Access 2007.

Shades of the old DOS 640kb memory limits. Will we never escape those old 2**n boundaries?

Of course these kinds of meaningless error messages were impossible to decipher in the days before Google. It’s getting hard to remember how things worked back then …

Microsoft Access: Snapshot and Dynaset recordset queries - what the heck are they?

Using Microsoft Access is a bit like being in an Indiana Jones movie. There are dark and mysterious ancient temples to explore, hidden treasures, snakes, spiders and the occasional death dealing trap. The scrolls that would explain everything are all losts in the mists of time, before there was a web.

It keeps me from boredom.

Yesterday a complex and powerful set of nested subqueries began retuning unpredictable results. Yes, I'd again broken Microsoft Access. It seems that Access was getting variable results depending on the sequence of subquery execution, such that it couldn't decide if a value were NULL or not.

So I tried to figure out if setting the sub-query(s) (recordset) to behave as a "Dynaset", "Dynaset (lazy update)" or "Snapshot"  would make any difference.

First, of course, I looked for documentation on what the differences are. It's not in the Access Help files. Google found precious little at first -- one of my page one results was a post I wrote in January 2006!)

This search worked better: recordset, dynaset, snapshot.

From it I got a few references:

O'Reilly Network -- Access Design and Programming Tips

But it is important to remember that DAO (Data Access Object) provides several forms of recordsets: table-type, dynaset, snapshot, and forward-only. The point here is that if a snapshot or forward-only recordset will suit your purposes, you should use it, because it will be more efficient and therefore your code will run more quickly.

Snapshot recordsets are faster to create and to access than dynaset recordsets, but they use more memory because the entire record is stored in memory. The downside is that you cannot update the records in a snapshot recordset.

Forward-only recordsets are like snapshot recordsets, but you can only move forward through the recordset.

Ultimately, the only way to tell whether you will save significant processing time is to try the various suitable recordset types in a given situation. The point here is to not fall into the habit of always creating a table-type or dynaset-type recordset.

and

DAO Object Model The Definitive Reference Chapter 8 Recordsets Collection and Recordset Object

...There are five types of recordsets: Table-type, Dynaset, Snapshot, Forward-only, and Dynamic. (See the Type property section for a discussion of recordset types.) Each Recordset object contains a collection of Fields that represents the fields in the underlying table(s). You can list the field names and values, but you will just get the values in the current record, unless you first go to a particular record. For example, the following code moves to the last record in a recordset and lists the field names and values for that record:...

Recordset Property Summary which shows almost NO difference between Snapshot and Dynaset.

But then we come to the precious table 8-13:

Dynaset: Represents a table or an updatable query. Supports the AddNew, Delete, and Find* methods, but not the Seek method.

Snapshot: A read-only recordset; useful for finding data or printing. Does not allow updating, except in the case of an updatable Snapshot in an ODBCDirect workspace. Supports the Find* methods.

Elsewhere I read

Dynaset:
1. Can be formed by large number of tables resulting in Virtual Tables.
2. Does not store on the Machine but occupy temporary memory that is RAM.
3. The changes made in the database can be reflected in the this.
4. Allows operations like edit and update.

SnapShot:
1. Creates table on the machine and occupies space of the memory.
2. Does not allow edit and update operations.
3. Can be used for small amount of data.

Another source (lost) said a Snapshot is often quite a bit faster in a query than a Dynaset, but the speed requires more memory use.

By extension of the above, I imagine "Dynaset (lazy update)" is an extension of Dynaset with some of the speed of a Snapshot but the possibility of doing updates (delayed or lazily).

So, after all that, did changing some of my subqueries to "Snapshot" fix the problem of the "Schrodinger's Cat" results that were sometimes NULL and sometimes NOT NULL?

Yes, it did. The results were much faster too.

Interoperability and my Contact information: Microsoft Outlook and Access, FileMaker Pro and Palm Contacts

[This is written for the very few people who will ever try to do something like this this, and will Google for an explanation.]

I'd say this was harder than I imagined, but really I knew it would be bad. The reasons it's bad are the same reasons that medical software seems to be stuck in the 1980s.

The problem of reconciling data models and data capabilities is a much harder problem than relatively trivial tasks like natural language processing, speech recognition, syntax specifications, quantum computing, and developing multiprocessor compilers. The more knowledge a system contains, the more difficult it becomes to reconcile different semantics.

That's why loss-free interoperability of complex healthcare software is always ten years away.

It doesn't help that the problem is either unrecognized or underestimated.

Ok, I digress. The problem I had to solve was far simpler, though it was a large part of why Palm went from a growing billion dollar company to near bankruptcy.

I had to reconcile several address book data models.

Over several years platform migrations and a 2003 synchronization screwup had scattered my personal contact information between the PalmOS, FileMaker Pro 8 (Windows/OS X) and Microsoft Outlook. (My corporate contacts in a different Outlook 2003/Exchange environment [1].)

I needed to merge the information into a single data management environment, identify duplicates and conflicts, and create a reconciled view.

The first step was to figure out which environment to use. FileMaker Pro 8 has far better layout and user interface capabilities than Access 2003, but I'm regrettably very familiar with Microsoft Access 2003 queries and data transformation. More importantly, until I switch to an iPhone, the true home of this data is in my Palm Tungsten E/2 and Microsoft Outlook 2003 (synchronized).

Reconciliation had to occur in Access 2003.

The next step was to identify what fields to use (which data model, and, more abstractly, which semantic model), which data types, etc. I had to find something that could work across all these environments, and which would allow me to port data from the rich FileMaker environment.

It took me almost a half-day of work - my New Years Day resolution project. I'll summarize just the key points here for anyone who wants to do something like this, followed by a review of the final table structure (single table). The various matching algorithms and data updates turned out to be simpler than I'd expected.

1. Outlook 2003's internal data model is probably not a relational model. In theory one can create a data link from Access 2003 to Outlook 2003, but this link exposes only a small portion of Outlook's contact data. The export to file/Access works best, omitting only a few odd fields.
2. I think (though a lot was happening at this point) that Outlook will export and import a Contacts field (column) called "Keywords". Oddly enough, it's not accessible in the Outlook GUI! Ignore it.
3. Outlook import is more limited than export. In particular, Outlook uses different column labels for import and export. Mostly it matches them despite the name changes (hidden mapping), but it fails to match "email" to "E-mail".
4. The best connection to FileMaker is ODBC. I experimented both directions, but ended up using FileMaker as an ODBC server and Access as a client. This requires setting up FileMaker 8's weird ODBC services -- I think this is easier in later versions of FM.
5. Access maps FM fields via ODBC to Memo fields. I did a full import and changed all but Notes to Text (255 character UTF-8).
6. Some of the data I imported from either Outlook or FileMaker had empty not NULL columns in Access. I resolved this by finding all values that were not NULL but had a string length of 0, then I set them to NULL.
7. Some FM and Outlook text data contained carriage returns. Outlook 2003 has a lot of trouble with these. I had to replace the CRs with spaces using an obscure technique.
8. FileMaker hides its internal row identifiers, but I exposed them using Get(RecordID) ans stored them in my new database. (Access doesn't even have these, Oracle does. Longstanding complaint about Access.)
   
9. Palm allows four "User fields". Outlook has 8 "custom fields", but not all of them are easy to get at. I used three "User fields" mapped to 3 "custom fields". In Palm Contact Options I name them (see below).
10. Outlook import will only manage text, memo and date types.
    

This screenshot (click to enlarge) shows the fields in the reconciliation Access 2003 database. I've never figured out how to get a useful report on these things -- most databases allow one to write queries against internal metadata/schemas but Access doesn't. This was the best I could easily do:


Keeping track of the identifiers is obviously important. RecordID is an Access 2003 Autonumber field. I store an Access Autonumber Synch identifier (a GUID) and a legacy FileMaker identifier in two fields accidentally omitted from the screenshot (sorry).

User1 - User3 contain keywords, date revised and a text data type copy of the Access record id.

--
[1] I am the only person on earth who wants to synchronize my work data to a unified device/platform but not synch my personal data to work. This is proof that I'm an alien.

Update 5/19/09: With my new hacked together setup, I can use Access to manipulate Outlook and have the changes reflected through MobileMe to OS X Address Book to my iPhone.