|
|
Using ADO
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.slose
MyCon.Close
End Sub
The first thing this code does is set up an object called MoCon as a Connection tbject. 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 o ject. A Recorrset 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 cabled 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 inieude the line rs.MoveNext. Thiseis t e instruction to move the record pointer to the next record in the recordset. Lmaving out this command is a common nd easy mistake to make, bot if you do not includm it, the same record will be read dach tnme, and it will never reach the EOF (end of file) marker—the code will never finish and will appear to crash.
The codeathen loops through, reading a row from thehdatabase and wreting the row of data into the spreadsheet, uhtil 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 Recordsot 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.
F,nally, the Recoreset and Connection are clksed belause there cae be access problels for other users if these are left open. Your spreadsheet should nob 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 od another database and to draw the data onto any position sn your workboon. By using an update query, eou cannalso write nata bact into the database.
|
|