Sunday, November 16, 2014

You know how Access 2013 won't open the Northwind 2000 .mdb file?

You know how Microsoft Access has been engineered to lack the ability to open Access .mdb files? Brain dead idea by the team. Even if they want to deprecate .mdb files and move onward, you want to be able to open or transfer legacy files from wherever or whomever you got them. That's your  user owned and invested data, not some arbitrary decision to be made by the database vendor. Now, if your organisation has moved on to Office 2013 and abandoned Office 2010, you will have to beg, steal or borrow a machine that can open and convert .mdb files.

It's a really silly situation. Because nobody told the Visual Studio 2013 / .NET framework 4.51 team to abandon this Access '97 .mdb file format as a data source.

Monday, July 28, 2014

Visualising Data

It's really hard to visualise data on-the-spot when it's not data that I live with.

Wednesday, April 30, 2014

Happy 50th Birthday, BASIC

I work almost daily with Microsoft Visual Basic for Applications - the language built into Microsoft Access, Excel, Word, Powerpoint, Project. It could stand improvements in elegance and modernity - that's what Visual Basic.NET was supposed to be. Except that Microsoft Office is written in COM architecture, not .NET - so VB.NET doesn't look like it's going to be embedded into Office-as-we-know-it.

But that's an aside. VBA itself is a descendant of the Microsoft's earliest BASIC, which was patterned after Dartmouth BASIC

Wednesday, March 5, 2014

Getting database data from Microsoft Excel

A friend asked me whether Excel could reach out to SQL Server data and grab data. I haven’t checked out this facility for a long time, so whipped up Excel 2013 and gave it a whirl.

Firstly, in Excel 2007, 2010 and 2013 the external data commands have been consolidated into the External Data tab of the Excel Ribbon. That’s good and easy to find.

Secondly, Microsoft Query is still alive and well for ODBC and local database connections. Except that no cosmetic work has been done on it – it looks really ugly on Windows 8 and the File Open / Save dialogs are even older, circa Windows 3.1 Despite this, it is still the nexus that connects Excel to these data sources.

Thirdly, with the new generation Excels, data grabbed from databases is dumped into Excel Tables – which is good – one of the excellent features in new generation Excel versions is the Excel Table.

Excel External Data Sources

As you can see, Excel continues to expand it’s ability to retrieve data from many kinds of data sources.

For this discussion, we are interested in classic tabular data sources – Microsoft Access database, SQL Server database, ODBC databases.

Notice Microsoft Query is an option. This will launch Microsoft Query.

Workbook Connection and data in Excel cells

After you have defined your request, you will be able to save a separate .dqry file or embed the request directly into the current Excel file. Your Excel file will have a Workbook Connection

Defining your Microsoft Query request

Microsoft Query has a simple user interface to

  • choose which database table or view/query
  • choose which columns you want
  • state your criteria to filter the data (parameterised stored procedures are not supported)
  • choose the sort order

You can use wizard driven screens or non modal user interface – the user interface crudely looks like Microsoft Access’s Query Design screen

Wizard dialogs

Microsoft Query Design Screen

More Advanced Data Retrieval

If you need to perform more complex data retrievals and coupling with parameter driven server-side Stored Procedures / Queries, you can use the VBA language in combination with Data Access Objects or ActiveX Data Objects to establish the request programmatically and form a Recordset which can then place the data into Excel cells. See KB 185125