Access VBA Macro Programming

It is often a very good idea to test the query out on the database itself using SQL Server

Manager or TOAD (Tool for Oracle Application Development). However, the problem is that

you as a humble Access developer may not be allowed anywhere near these tools!

This route is considerably more complicated than using simple linked tables, but it has the

huge advantage of speed, which may be very important in your application.

Using ADO

ActiveX Data Objects (ADO) is the Microsoft technology for connecting to databases. ADO

is a Component Object Model (COM) that you can direct to use the data from an external

database either using a connection string or a DSN. The link tells your code where the

database is and gives the ID and password to get into it. ADO provides you with the tools to

hook into the database using that ODBC link and to read and write the data.

To use ADO in your code, you must first include a reference to the Object Library by

selecting Tools | References from the VBE menu. Scroll down until you get to Microsoft

ActiveX Data Objects 6.0 Library and Microsoft ActiveX Data Objects Recordset 6.0

Library, as shown in Figure 19-7. You may have earlier version numbers of these going back

248 Microsoft Access 2010 VBA Macro Programming

Figure 19-7 Putting in a reference to ActiveX Data Objects