MS Office is pretty ubiquitous and lots of the free versions of office software are, in my opinion, pretty rubbish. So we use Office 365 on all machines whether PCs or Macs.

The good news with 365 is you automatically get updated versions and this week we have found the bad news is that you get updated versions!

We’re currently working with a new client to create a customer management system that is web-based and automates much of their contact messages, standards renewal notices, etc. Their current system is written in VB and uses an old Microsoft Access database (yes I know, an Access database). So a starting point is to get the old database and convert it into a usable and suitably normalised format. When I say database everything is shoved into one table with a huge text field holding multiple items of key data in the same field.

All good so far, the data is there and we can set up procedures to convert the text field content into suitable data types and store those. However when I try and access their replicated database Microsoft Access won’t open it as it is simply too old and the automatically updated Office 365 no longer has the Jet driver to open it.

A quick search via Google and Microsoft have answers for this in their knowledge base so no problems, “Step 1 Open the database …” Er it doesn’t open is THE problem so the solution can’t start with that. It was looking like Access 2013 wouldn’t do the job so install an old version of Access with the Jet driver and then do the conversion or find some dodgy software or try and install the Jet driver separately.

Then after a good swear (under my breath of course!) at Microsoft an idea popped up. Does Excel still have a Jet driver in its import from database routines? The answer was yes and files were converted to a spreadsheet. The new spreadsheet can be used to import into something useful like MySQL for further use with a little SQL work to get everything in the right format.

A great example of “re-framing” in positive psychology jargon and we can now crack on and create the solution, no thanks to Microsoft though.