Glossary

ADO

ActiveX Data Objects. One of two commonly used code libraries for Access programmers writing code that interacts with data. See also the entry for “DAO,” and “DAO Versus ADO” in Chapter 0.

automation

Using Visual Basic code from within one Office application (such as Access) to open a different application (such as Outlook or Excel) and use its features.

backend

The MDB file (or other database) where the data (i.e., the tables) are stored in a split database design. See also “frontend.”

bound

Tied directly to a data source. See “Bound Versus Unbound Objects” in Chapter 0 for more info.

calculated field

A field whose value is the result of an expression, such as a field totaling up a customer’s purchases.

connection string

A string used in VB code that tells Access how to connect to a remote database. The string includes the server name and the database name and may contain the username and password.

control

A design element such as a text box, a combo box, or a label used to build forms and reports. Controls are used both to display data and to accept input.

control source

The place where a control gets its data, and where it stores user input. For example, if a text box has a field in a table as its control source, it automatically displays the value in that field—and when a new value is entered into the text box, it will be stored in that field. Not all controls have control sources.

criteria

See “query criteria.”

DAO

Data Access Objects. One of two commonly used code libraries for Access programmers writing code that interacts with data. See also the entry for “ADO,” and “DAO Versus ADO” in Chapter 0.

data type

Access handles different kinds of data differently. For example, you can subtract one number from another, but you can’t subtract a text string from another text string. Much of the power of databases comes from classifying data and specifying which type of data can go into which fields. Some common data types are Text, Number, and Date.

DBMS

Database Management System. The software that lets you create and use a database. Your customer MDB file is a database; Oracle, MySQL, and Access are DBMSes.

domain aggregate functions

Domain aggregate functions such as DLookup, DSum, and DAvg allow you to work with sets of records. For example, to compute the average salary of all employees in the employees table, you could employ DAvg as follows: DAvg("salary", "tblEmployees"). For more information, see the entry for “function,” and Table A-7 in the Appendix.

DSN

Data Source Name. Provides a sort of Windows shortcut to any ODBC data source (database, spreadsheet, and so on). Once you’ve defined a DSN (in Control Panel → Administrative Tools → Data Sources (ODBC)), you can just point Access to that DSN and it will know how to get the data out of the remote data source. See also “ODBC.”

expression

Any legal combination of symbols that represents a value; for instance, X/Y.

Expression Builder

A tool designed to help you construct expressions. It appears when you click the Build (…) button to the right of any text box where you can type in an expression.

field

For practical purposes, a column in a table—that is, a slot for a particular kind of data. For example, if you want to store cities and states in a table, you’d define one field for the city name and a second field for the state. Then, each row (or record) in the table would consist of one city and one state.

frontend

The MDB file where everything except the data (i.e., queries, forms, reports, and so on) is stored in a split database design. See also “backend.”

function

A prewritten procedure or routine that performs a specialized task.

indexing

A technique used to make search (and sort) operations faster; it relies on maintaining a presorted copy of the data.

ISAM

Indexed Sequential Access Method. A kind of data access that allows Access to efficiently process data that’s stored in a variety of file types (text files, spreadsheets, and so on).

join

A temporary merging of two tables into a single virtual table.

linked table

A table that is stored not in the current MDB file, but in some remote data source. This could be a second MDB file, or some other data source entirely (a spreadsheet, MySQL database, and so on). A linked table appears in the current MDB simply as a link; the data is viewable and may be modifiable, but the structure of the actual table can be changed only in the data source where it resides.

MDAC

Microsoft Data Access Components. An umbrella term for a collection of related data access technologies including ADO, OLEDB, and ODBC.

MDB file

An Access database file.

ODBC

Open Database Connectivity. A standard that enables different applications, systems, and databases to exchange data. ODBC is oriented toward relational data.

ODBC data source

Any data source for which you have an ODBC driver.

ODBC driver

Software that enables an application to communicate with ODBC data sources.

OLE DB

A Microsoft standard that allows different applications, systems, and databases to exchange data. The successor to ODBC, it works with both relational and nonrelational data.

OLE object

An Access data type that can accept objects or live links to objects, such as pictures, sound files, spreadsheets, or Word documents.

opportunistic locking (oplocks)

A Windows file-sharing protocol that allows multiple clients to share locks on a single file. In a simple locking scenario, once you have a lock on a file, everyone else is locked out until you release it—maybe hours later. Windows is smart enough to contact you if others need access and lets you release the lock temporarily.

parameter

A variable in a query. When the query is run, it prompts the user to supply the value of this variable. Typical examples are start and end dates: run the query, it asks for start and end dates, you supply them, and data is selected based on the values you provide.

query

SQL-based statements that typically retrieve data from tables. They can also be used to modify or delete existing data, add new data, and create new tables.

query criteria

Limitations that narrow down which data you want your query to retrieve. For example, you might just want to see sales order totals since March of last year, for the Northeast region. Since March and Northeast region would be your query criteria.

record

A row in a table or query result. Theorists distinguish between records and rows, but for practical purposes they’re the same.

recordset

A set of records that, for programming purposes, can be treated as a single object.

record source

The place where a form or report gets its data (i.e., its records). If you have a customers form that displays the data from the customers table, the customers table is the form’s record source.

referential integrity

If a record in one table refers to a record in another table (see “relationships”), that second record had better exist as long as the first one does. If it doesn’t, your data may become meaningless—for example, you could have an order and not know who ordered it. A properly designed database enforces referential integrity, which means that it does everything possible to prevent orphaned records.

relational database

Contrary to popular rumor, the term “relational” has nothing to do with the table relationships that are used to enforce referential integrity. Instead, it refers to the mathematical construct of a “relation.” A relation is any ordered set of objects, such as {"Washington", "George", 1776}. Tables are collections of relations that all have the same structure. There are other kinds of databases that use other mathematical structures as their foundations—for example, hierarchical databases are based on tree structures.

relationships

When you want a record in one table to refer to a record in another table, you must define a relationship between the two tables. For example, you could create a relationship in which the customerId field in every record in the orders table refers to the customerId field in every record in the customers table. Once you define this relationship explicitly, Access can enforce referential integrity—that is, it will prevent you from deleting one of the related records while leaving the other in place.

sandbox mode

In sandbox mode, Access won’t evaluate expressions that contain functions such as Kill and Shell, which malicious users could employ for nefarious ends. Instead, if it sees such an expression it issues an error message.

SQL

Structured Query Language. A popular programming language used for querying, creating, and modifying relational databases.

subdatasheet

A datasheet that is nested inside another datasheet and linked on a specific field. For example, the customers table (in Datasheet View) might have a subdatasheet that shows the selected customer’s orders.

subform

A form that is nested inside another form and linked on a specific field (or fields). For example, the customers form might have a subform that shows that customer’s orders.

subreport

A report that is nested inside another report and linked on a specific field (or fields). For example, the customers report might have a subreport that shows each customer’s orders.

unbound

Not tied directly to a data source. See also “bound.”

Visual Basic (VB)

A programming language (strictly speaking, Visual Basic for Applications, or VBA) that’s integrated into Access. You can use VB code to customize the way Access works.