Microsoft Office Tutorials and References
In Depth Information
Lesson 24: Data Access with
dEfiniTions of dATABAsE TErMs
Because this lesson introduces concepts for external data access, there is more
descriptive theory about databases than actual code examples. In Lesson 29, you’ll
see several working examples of how Excel utilizes ADO and SQL in conjunction
with Access databases. If you are unfamiliar with database terminology, the
following definitions for common database terms might help you throughout this lesson.
A database is an organized collection of related information.
DAO (Data Access Objects) is a library of objects and their associated methods
and properties that can be used to represent objects in databases, enabling Excel to
interact directly with databases through VBA.
DBMS is an abbreviation for database management system. Popular examples of
database management systems include dBASE, Paradox, and Microsoft Access.
A field is a column in a list such as in an Excel worksheet or Access database that
describes a characteristic about records, such as first name or city.
ODBC (Open Database Connectivity) is a database standard that enables a
program to connect to and manipulate a data source, allowing a single user to access
many different databases.
A primary key is one or more fields that determine the uniqueness of each record in
A query is a series of statements written in Structured Query Language to specify
the tables and fields you want to work with that add, modify, remove, or return
data from a database.
A record is a row of data in a table.
A recordset is one or more records (rows) of data derived from a table.
A relational database is a collection of data items organized as a set of formally
described tables from which data can be accessed or reassembled in many ways.
Prior to ADO, Microsoft’s primary recommended tool for accessing external
data was an interface called DAO, or Data Access Objects. The DAO interface
has become all but obsolete due to its limitations as compared to ADO, though
DAO is still supported by ADO. The two technologies share many of the same
code syntaxes but they are not the same in terms of flexibility and performance.
You still do have a choice between the two, but you’ll be much better served by
ADO, which is why it is covered in this topic.