OpenConnection("", , , sConn)
Set rs = cnn.OpenRecordset("SELECT * FROM msdbms", dbOpenDynamic)
iFieldCount = rs.Fields.Count
For i = 1 To iFieldCount
xlSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next i
xlSheet.Cells(2, 1).CopyFromRecordset rs
xlSheet.Select
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Columns.AutoFit
Range("A1").Select
'close workspace
wrk.Close
'release objects
Set xlSheet = Nothing
Set rs = Nothing
Set wrk = Nothing
Set cnn = Nothing
End Sub
CHAPTER 2 n DATA IN, DATA OUT 66
Using ADO in Excel 2007
ActiveX Data Objects (ADO) was introduced by Microsoft in 1996 and has become the successor
to DAO. Its database access technology is OLE DB (Object Linking and Embedding
Database), which is the successor to ODBC.
The latest version of ADO is ADO 2.8. ADO lets us access, edit, and update data from
many data sources by interfacing to these data sources via OLE DB providers. OLE DB
providers speak to the database engine more directly than ODBC, and provide us with better
performance.
In the examples in the previous section, we used DAO to interact with an Access 2007
database and an SQL database. You??™ll recall we could not interface with Access 2007 directly
with Jet, but we could interact using ODBC. In both cases, DAO goes through Jet, then from
Jet to ODBC, and then to the data engine. Then our data comes back. As you might imagine,
this may not be the speediest route to your data. ADO, on the other hand, talks directly to
your OLE DB provider, which speaks directly to the data engine, and vice versa.
Pages:
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92