Microsoft Office Tutorials and References
In Depth Information
Lesson 24: Data Access with
Data access with
activeX Data objects
The topic of data access has become one of the most intensive forces in driving the recent
development of commercial software applications. Data storage and search engine companies
have become the face of the worldwide voracious demand for accessing information.
Excel is without peer in its powerful features for calculating and analyzing data, and in its
ability to produce customized reports in an instant with VBA. For users who deal with extremely
large volumes of source data, Excel can still fall short as a data storage application. Microsoft
has built Excel with some robust methods for importing external data into your workbooks,
making Excel a terrific front-end application that analyzes data it does not need to store.
ADO is an acronym for ActiveX Data Objects, which is the technology Microsoft
recommends for accessing data in external databases. Excel’s spreadsheets, being tabular row and
column objects, share common features with database tables, providing a natural environment
for data to be transferred between Excel and relational databases.
From Excel, using ADO you can
Connect to most any external database in the Windows operating system, as long as
that database has, as many do, an ODBC (Open Database Connectivity) or OLE DB
(Object Linking and Embedding Database) driver.
Add, delete, and edit records from a database to your workbook, or from your
workbook to a database.
Query data to return a recordset, allowing you to import some or all records from a
database table directly to your worksheet, for whatever analysis you want to perform,
just as if the data was already in Excel.