I learned to use one of Microsoft’s many abandoned APIs this week: DAO, the Data Access Object library.
It happened like this. At work we have a client with an existing application that uses a Microsoft Access database. He also has the interesting habit of specifying additions not in English but by creating sketchy database schemas in Access. Our work on the next version will be using Microsoft SQL Server, with which I have some familiarity—unlike Microsoft Access of which I know nothing. So naturally I was given the task of devising the new database definition.
My first couple of weeks were very frustrating. With SQL databases one can type questions in the SQL language to explore the relationships between database entities (they even store the database schema in its own database tables). The ‘easy to use’ query creator in Access I personally find bewildering. It is possible to write queries in SQL, but it makes it plain that it does not like you to do this, and offers less support than, for example, Microsoft SQL Server Query Analyzer. Access often fails with truly inscrutable error messages.
I wanted to use a mini-program to do some of the schema manipulation for me. Thus I needed extract information about the database definition in a form my program could process. Access has a nice visual interface for exploring the relationships between database tables, but no discernible way to extract the information in textual form. There is an Upsizing Wizard for converting Microsoft Access files in to Microsoft SQL Server databases, but I never got it to work—supposedly installing some service packs should have fixed it, but no dice.
I had a Python program
using the ODBC
support bundled with Mark
Hammond’s Win32 extensions. With this one can
discover the columns defined for a given table. With some blind
poking about I discovered how to get a list of tables using
OLE ActiveX COM Automation to talk direct
to the Access program. The programmer had used fairly
consistent, stylized column names, from which my program could
deduce a lot of the database structure. But something in the
combination of ODBC, Automation, Access and Python caused a lot
of crashes, which was annoying. Then a colleague suggested
using DAO.
DAO is one of many database libraries for windows, coming after ODBC and RDO and before OLE-DB, ADO, and ADO .NET. It was created to allow users of Microsoft Access Basic, and then VBA, VBScript and also VB to manipulate Microsoft Access database files (they like to call it the Jet database engine), and extended to allow connection to any ODBC data source. Nowadays a VB programmer would use a more recent library like ADO. But DAO, being Jet-specific, sees all when it comes to Access databases.
The O’Reilly book makes it all straightforward—there’s a list of tables, and a list of ‘relations’, and it pretty much works as it should. I’m using Python with the Win32com support for Automation to talk to the DAO subsystem, without any bother and with no more crashes. It also gives more precise info on the data types (ODBC says NUMBER; DAO can distinguish (Short) Integer, Long, Byte, etc). Equipped with this information, I can begin analysing the database layout in earnest.