Excel VBA Macro Programming

ActiveX Data Objects (ADO) is the latest Microsoft technology for connecting to databases. ADO is a Component Object Model (COM) that you can direct to use the data from the ODBC link that you just created. The ODBC 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 that 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 2.7 Library and Microsoft ActiveX Data Objects Recordset 2.7 Library, as shown in Figure 16-4. You may have earlier version numbers of these going back to version 2, depending on what version of Windows you are running, but they will still work in the same way. If you do not have Version 2.7, use the latest version that you have. Set both the check boxes on the left and click OK.

Figure 16-4: Putting in a reference to Active Data Objects

Now you can use the following code example:

Sub Test_Db() Dim MyCon As New Connection MyCon.Open "NWind" Set rs = New Recordset rs.Open "select firstname,lastname,title from employees", MyCon, _ adOpenForwardOnly, adLockReadOnly, adCmdText co = 1 Do Until rs.EOF ActiveSheet.Range("a" & co).Value = rs!firstname ActiveSheet.Range("b" & co).Value = rs!lastname ActiveSheet.Range("c" & co).Value = rs!Title co = co + 1 rs.MoveNext Loop rs.Close MyCon.Close End Sub

The first thing this code does is set up an object called MyCon as a Connection object. The Connection object is then opened using the data source name NWind that you set up earlier and sets up a connection to the database based on the information you provided in the DSN.

Next , it creates a Recordset object. A Recordset is an object representing a chunk of data that can be a query, a table, or a SQL statement. In this case, it takes data from the Employees table. Parameters are included for cursors and record locking.

A variable called co is set to 1, which gives a dynamic reference point to write data into the spreadsheet row by row. As you write each row of data in, this is incremented to point to the next row.

Make sure you include the line rs.MoveNext . This is the instruction to move the record pointer to the next record in the recordset. Leaving out this command is a common and easy mistake to make, but if you do not include it, the same record will be read each time, and it will never reach the EOF (end of file) marker ‚ the code will never finish and will appear to crash.

The code then loops through, reading a row from the database and writing the row of data into the spreadsheet, until it hits the EOF marker on the recordset, meaning all records have been viewed .

Using the ActiveSheet object, it then writes into columns A, B, and C the fields firstname, lastname, and title from the recordset. Notice that on the Recordset object an exclamation point (!) is used instead of a dot (.). This is to indicate that you are using a field name on the Recordset object and not a property or a method, for which you would use a dot.

Finally, the Recordset and Connection are closed because there can be access problems for other users if these are left open. Your spreadsheet should now look like Figure 16-5.

Figure 16-5: Results of running the example to draw data from a database table

This VBA code allows you to execute queries on another database and to draw the data onto any position on your workbook. By using an update query, you can also write data back into the database.