How Access Works

Microsoft Access actually consists of two separate applications, which are presented as being part of a single software program. The forms, reports and modules comprise the Microsoft Access application itself – the user interface – while the tables and queries form the database engine.

You can see this most clearly in Visual Basic. Press ALT + F11 in any Access database to go into Visual Basic, then choose Tools / References from the menu. You’ll see a list of all of the programs Access could talk to, with a few at the top ticked. The main ones will be:

• Microsoft Access NN.0 Object Library (where the NN depends on the version you are using – 12 if you’re using Access 2007, 11 if you’re using Access 2003, 13 if you’re using Access 2010)
• Either Microsoft Data Access Objects, ActiveX Data Objects or Access Database Engine Objects, depending on which version of Access you’re using.

Choosing a Technology

In the beginning there was Microsoft Access up to and including version 97. If you write VBA within an old database like this, you don’t have any choice – you’ll have to use Direct Access Objects, or DAO.

If you’re using Access 2000, 2002/XP or 2003, your best choice will probably be ActiveX Data Objects, or ADO. This comes in many versions (typically from 2.0 to 2.8 and 6.0) – any will do. ADO has the advantage that it runs more quickly than DAO, and allows you to connect to other data sources (and not just an Access database engine).

Finally, if you’re using Access 2007 or 2010, your best choice will be to use Access Database Engine Objects.

Similarities and Differences

When you’re writing code in VBA, there are more similarities than differences between the 3 technologies. Most importantly, the all contain the concept of a recordset, which is like a table open in VBA. You can go to the first, previous, next or last record in a recordset, but can not go beyond the BOF or EOF markers (beginning of file/end of file).