Linking to ADO databases with VBA
Part two of a five-part series of blogs

Learn how to write VBA macros to add, edit and delete data in Access, SQL Server and other databases, using something called ADO.

  1. Linking to Data using VBA
  2. The ADO Object Library (this blog)
  3. Of Connection Strings, Recordsets, Cursors and Locking
  4. Working with Records
  5. Finding Records Easily

This blog is part of both a complete Excel VBA tutorial and a complete SQL tutorial.

Posted by Andy Brown on 06 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

The ADO Object Library

To link to data from within VBA, you will usually use the ActiveX Data Objects object library (ADO to its friends).

If you're writing VBA within Access, you may well prefer to use DAO (or the Direct Access Objects library) instead.  You can Google ADO or DAO for more details.

You can create a reference to the ActiveX Data Objects library as follows:

  1. From the menu within VBA select: Tools -> References...
  2. Check the box next to the latest version of Microsoft ActiveX Data Objects that you can see, and select OK.

Here's what the dialog box looks like:

Tools references dialog box

Make sure you tick the box next to the library, rather than just selecting it in the list.

Which Version of ADO should you Use?

There are 7 different versions of ADO in the dialog box shown above - which one should you use?  The short answer is: the highest version number.  For more information on different versions of ADO, see this Microsoft ADO version history page.

Functionally ADO 2.8 and 6.0 are the same: the only difference is that 6.0 was provided for Windows Vista and later operating systems.

Now that you're referring to the ADO library, it's time to learn a little about esoterica such as connections and recordsets.


This blog has 0 threads Add post