Special Edition Using Microsoft Office Access 2003

Microsoft's strategy, announced in June 2000, to reorient its operating systems, developer tools, and productivity applications from the Component Object Model (COM) to the .NET Framework and its Common Language Runtime (CLR). The CLR supports multiple programming languages, such as Microsoft's Visual C#, Visual Basic .NET, and Visual J#. Much of Microsoft's marketing campaign for the .NET Framework and Visual Studio .NET focuses on support for Internet-enabled XML Web services. Microsoft Office 2003 installs version 1.1 of the .NET framework to support Excel 2003 and Word 2003. The .NET Framework is a reincarnation of Next Generation Windows Services (NGWS), the follow-up to Microsoft's earlier Distributed iNternet Architecture (Windows DNA). See also CLR, COM, SOAP, and XML Web services.

The replacement for the Access related components of the Microsoft Office Developer edition (MOD). The extensions include a license for distribution of the runtime versions of MSAccess.exe and MSGraph.exe, a Package Wizard for creating Microsoft Installer packages (.msi files) for runtime applications, and two add-ins Property Scanner and Custom Startup Wizard for Access developers. See also MOD and VST.

See ADP.

Former name of Jet SQL, a dialect of ANSI SQL-92. See also ANSI, Jet SQL, SQL, and Transact-SQL.

See ASP and ASP+.

A Microsoft trademark for a collection of technologies based on the Common Object Model (COM) and Distributed Common Object Model (DCOM). See also COM, COM+, and DCOM.

A replacement term for OLE Automation mini-servers and in-process servers, also called Automation servers. See also Automation.

Insertable objects supplied in the form of .ocx files that, in addition to offering a collection of properties and methods, also fire events. ActiveX controls are lightweight versions of earlier OLE controls that also use the .ocx file extension.

See ADO.

See ADOX.

Files such as those created by Microsoft Excel 7+ and Word 7+ that can be displayed in their native format in Internet Explorer 3+. ActiveX documents originally were called Document Objects or DocObjects.

Another name for Visual Basic, Scripting Edition (VBScript), a simplified version of VBA designed for client- and server-side automation of Web pages.

An abbreviation for Advanced Data Control, the predecessor to Remote Data Service. See also RDS.

Wizards and other programming aids, usually in the form of Access libraries, that help Access programmers create and deploy applications. You use Access's Add-In Manager to install Microsoft and third-party add-ins. COM add-ins are a class of add-in objects introduced in Office 2000. See also COM and Builder.

An abbreviation for ActiveX Data Objects, which are similar in concept to the Data Access Object (DAO) and Remote Data Object (RDO). ADO uses Microsoft's OLE Database (OLE DB) technology to access data from various data sources, including text files and mainframe databases. ADO is a member of the Microsoft Data Access Components (MDAC.) ADO 2.7 is the preferred database connectivity method for Access 2003. See also MDAC, MSDE, and OLE DB.

An abbreviation for ADO Multidimensional (Expressions), the Automation library that provides access to CubeDef and Cellset objects created with the PivotTable Service or Microsoft SQL Server Analysis Services (formerly Microsoft Decision Support Services, MSDSS, and OLAP Services). See also Cellset, CubeDef, MDX, OLAP, and PivotTable Service.

An abbreviation and object library name for ADO 2.x. ADODB is used as an object prefix to specify the source of an ADO data object, as in ADODB.Recordset.

An abbreviation for ActiveX Data Object Extensions, a set of extensions to ADO 2.1+ that support Jet 4.0's new SQL Data Definition Language reserved words, such as CREATE GROUP and ALTER USER. See also data definition.

An abbreviation for Access data projects, an alternative to conventional Access client/server applications that store application objects (forms and reports) in Jet .mdb files. ADP requires an ADO 2.1+ connection to one of the five versions of SQL Server 7.0 or 2000 (Desktop or MSDE, Personal, Developer, Standard, Enterprise, or DataCenter editions). ADP stores application objects in OLE docfiles having an .adp or .ade (encrypted) extension. See also ADO, application object, docfile, and MSDE.

An abbreviation for Advanced Data TableGram, a Microsoft-proprietary MIME format used by Remote Data Services (RDS) to marshal Recordsets between client and server via the HTTP protocol. By default, ADO Recordsets saved to files use the ADTG format. See also ADO, HTTP, MIME, and RDS.

See ADTG.

The ANSI SQL functions AVG(), SUM(), MIN(), MAX(), and COUNT(), and Jet SQL functions StDev(), Var(), First(), and Last(). Aggregate functions calculate summary values from a group of values in a specified column and are usually associated with GROUP BY and HAVING clauses. See also domain aggregate functions.

A temporary name assigned to a table in a self-join, to a column of a query, or to rename a table, implemented by the AS reserved word in ANSI SQL. You can use AS to rename any field or table with Jet SQL or SQL Server's Transact-SQL. Alias is also an embedded keyword option for the VBA Declare statement. The Alias keyword is used to register prototypes of DLL functions so that the function can be called from programs by another name. Aliasing the ANSI versions of 32-bit Windows API functions to function names without the A suffix is common when converting Access 97 and earlier applications to Access 2003, which uses Unicode for text data.

An acronym for the American National Standards Institute. ANSI in the Windows context refers to the ANSI character set that Microsoft decided to use for Windows (rather than the IBM PC character set that includes special characters such as those used for line drawing, called the OEM character set). The most common character set is ASCII (American Standard Code for Information Interchange), which for English alphabetic and numeric characters is the same as ANSI. Windows 98 and Windows NT include ANSI (suffix A) and Unicode (suffix W) versions of Windows API functions. ANSI also is the standards body responsible for the definition of SQL. The latest ANSI standard is SQL-99 (also called SQL:1999 and SQL3); most of today's RDBMSs support the previous version, SQL-92. See also ASCII, SQL, and Unicode.

Any object in a database application that doesn't contain or define data (data objects). Access forms, pages, reports, macros, and modules are examples of application objects. Production Access applications usually store application objects separately from data objects. See also data object.

A piece of data supplied to a function that the function uses or acts on to perform its task. Arguments are enclosed in parentheses. Additional arguments, if any, are separated by commas. Arguments passed to procedures usually are called parameters.

An ordered sequence of values (elements) stored within a single named variable, accessed by referring to the variable name with the number of the element (index or subscript) in parentheses, as in strValue = strArray(3). VBA arrays can have more than one dimension, in which case access to the value includes indexes for each dimension, as in strValue = strArray(3,3).

An acronym for the American Standard Code for Information Interchange. A set of standard numerical values for printable, control, and special characters used by PCs and most other computers. Other commonly used codes for character sets are ANSI (used by Windows 3.1+), Unicode (used by Windows 98 and Windows NT), and EBCDIC (Extended Binary-Coded Decimal Interchange Code, used by IBM for mainframe computers). See also ANSI and Unicode.

An acronym for Active Server Pages, Microsoft's server-side technology for dynamically creating standard HTML (Internet) Web pages. Conventional ASP uses VBScript or ECMAScript to manipulate ASP objects. Access 2003 lets you export tables, queries, and forms in ASP format for Web deployment. Exported .asp files send data in HTML tables to client browsers. ASP.NET is the successor to ASP. See also ECMAScript.

An .NET upgrade to ASP that supports the Simple Object Access Protocol (SOAP), managed code compiled by the Common Language Runtime (CLR) and Web Forms, a new methodology for designing interactive HTML forms. ASP.NET also supports the creation of XML Web services. See also .NET, CLR, SOAP, and XML Web services.

To give a value to a named variable.

A process that can occur at any time, regardless of the status of the operating system or running applications.

A table that's not stored in the currently open Jet database (a native table), but which you can manipulate as though the table were a native table. In Jet 3+ terminology, an attached table is a linked table. See also linked table.

The process of verifying a user's identity, most commonly by a login ID and password.

An ActiveX and OLE 2+ term that refers to a means of manipulating another application's objects.

An ActiveX- or OLE 2 compliant Windows application with an application programming (macro) language, such as VBA, that can reference and manipulate objects exposed by (OLE) Automation servers.

Technically, any COM-compliant Windows application that supports Automation operations by exposing a set of objects for manipulation by Automation client applications. This book restricts the term Automation server to applications that expose application objects, but aren't ActiveX full servers. The members of Office 2003 are examples of full Automation servers.

A Jet 3+ replacement for the Counter field data type of Jet 2.0, Access 1.x, and Access 2.0. AutoNumber fields can be of the Increment or Random type. Fields of the Increment AutoNumber field data type usually are used to create primary keys when a unique primary key can't be created easily from data in the table.

A date used as a reference from which other date values are calculated. In the case of VBA and SQL Server, the base date is January 1, 1900, 12:00 AM. Dates earlier than the base date are negative.

The permanent tables from which a query is created, usually acting as the one side of a one-to-many relationship. Jet documentation also uses the term base table to refer to a table in the current database in contrast to a linked (attached) table. See also linked table.

A group of statements or database operations processed as an entity. Execution of DOS batch files, such as AUTOEXEC.BAT, and SQL statements are examples of batch processes.

A process in which multiple update operations on a Recordset are conducted on a locally cached copy of the Recordset. When all updates are completed, calling the UpdateBatch method attempts to make permanent (persist) all changes to the underlying tables in a single operation. Access 2003's ADP and DAP support batch updates without the need to write VBA code.

A string consisting of binary, not text, data that contains bytes outside the range of ANSI or ASCII values for printable characters or don't correspond to Unicode characters. Access 2003 requires that you store binary strings as arrays of the Byte data type to avoid problems with Unicode/ANSI conversion.

The process of connecting one object to another through interfaces. In Access 2003, local object binding is accomplished by COM interfaces and remote object binding by DCOM interfaces. See also COM, COM+, DCOM, and data binding.

A process that evaluates each bit of a combination, such as a byte or word, rather than process the combination as a single element. Logical operations and masks use bitwise procedures.

A type of arithmetic in which all digits are bits that is, the numbers can have only two states: on (true or 1) or off (false or 0). Widely used in set theory and computer programming, Boolean, named after the mathematician George Boole, also is used to describe a VBA data type that can have only two states: true or false. In VBA, true is represented by &HFF(all bits of an 8-bit byte set to 1) and false by &H0(all bits set to 0). Boolean is a VBA data type.

A term commonly used in Access applications to identify form, report, and control objects that are connected to and can receive values from data objects. See also binding.

A designated statement that causes VBA program execution to halt after executing the statement preceding it. To toggle breakpoints on or off, press F9 on the line of code in Access's VBA editor before which you want execution to halt.

A feature of Jet 3+ running under Windows 98 or Windows NT 4.0/2000 that permits the creation of Jet replication sets stored in Briefcase folders, which can be updated by mobile users. ADO 2.1+ supports Jet replication. See also design-master replica.

A component that provides assistance in defining new objects, writing SQL statements, or creating expressions. Buttons with an ellipsis symbol commonly open Access Builders.

Functions that are included in a computer language and don't need to be created by the programmer as user-defined functions. VBA has hundreds of built-in functions, many of which you can use in Jet queries.

A set of rules for adding or altering data in a database that are specific to an enterprise's method of conducting its operations. Business rules are in addition to rules for maintaining the domain and referential integrity of tables in a database. Business rules most commonly are implemented in the middle-tier of a three-tier client/server database environment. See also middle tier and three-tier.

A block of memory reserved for temporary storage. Caches usually store data from disk files in memory to speed access to the data. Access Recordset objects, which store in memory an image of data in tables or returned by queries are typical cached objects.

An acronym for Client Access License, which are required for client PCs connecting to Microsoft server products, such as Windows 2000+/NT, SQL Server 2000, and Exchange 2000. With the exception of bundled server products, such as Small Business Server 2000+ and BackOffice 2000+, separate CALs are required for each server product. CALs aren't required for local installation of or connections to MSDE, which is covered by an Office XP license. You do, however, require a CAL if a local copy of MSDE connects to SQL Server 2000, such as occurs with SQL Server merge replication. See MSDE.

Named for Ren[as]e Descartes, a French mathematician. The term describes JOIN operations that return all possible combinations of rows and columns from each table in a database. The number of rows in a Cartesian product is equal to the number of rows in table 1 times that in table 2 times that in table 3, and so on. Cartesian rows that don't satisfy the JOIN condition are disregarded.

A process that deletes data from one table based on a deletion from another table to maintain referential integrity. Triggers and declarative referential integrity (DRI) are two means of implementing cascading deletions. DRI or triggers usually are used to delete detail data (such as invoice items) when the master record (invoice) is deleted. Jet 2+ and SQL Server 7.0+ provide cascading deletion as an optional element of its referential integrity features. See also DRI and referential integrity.

A process that automatically changes foreign key values to correspond to altered values of a primary key. DRI or triggers implement cascading updates, which are another optional referential integrity element.

The multidimensional equivalent of an ADO Recordset. See also ADO MD, CubeDef, OLAP, and PivotTable Service.

See hierarchical Recordset.

In Access, a reference to the table or query that serves as the record source for a subform, subreport, or subdatasheet. Also used in computer programming in general to describe an object that's related to but lower in hierarchical level than a parent object.

See CLSID.

The portion of an SQL statement beginning with a keyword that names a basic operation to be performed.

The device or application that receives data from or manipulates a server device or application. The data might be in the form of a file received from a network file server, an object from an ActiveX component or Automation server, or values from a DDE server assigned to client variables. See also Automation client.

A logical entity that represents a networked computer where an Access application interacts with a client/server database or a browser displays a Web page from a remote data source. The client tier often is called the presentation tier. See also middle tier and data source tier.

The abbreviation for Common Language Runtime, the unifying components of the .NET Framework to support intercommunication of components written in different programming languages, such as Visual Basic .NET, managed Visual C++, and Visual C#. Excel 2003 and Word 2003 support the CLR as a macro language, in addition to VBA; Microsoft chose not to support CLR programming for Access 2003 and Outlook 2003.

An identification tag that's associated with an Automation object created by a specific server. CLSID values appear in the Registry and must be unique for each ActiveX component or Automation server and for each type of object that the server can create. See also Registry.

An index in which the physical record order and index order of a table are the same. SQL Server offers the option of using a clustered (recommended) or nonclustered index on the primary key of a table.

The process of forcing a change from one data type to another, such as Integer to String. VBA supports type coercion; Visual Basic .NET coerces data types unless explicitly instructed not to by adding an Option Strict On instruction prior to the first line of code.

A group of objects of the same class that are contained within another object. Collections are named as the plural of their object class for example, the Parameters collection is a group of Parameter objects contained in a Command object.

An acronym for Component Object Model, the name of Microsoft's design strategy to implement ActiveX. Distributed COM (DCOM) allows networked and cross-platform implementation of ActiveX and Automation. See also DCOM.

A feature of Windows 2000 and later Windows server operating systems that integrates COM, DCOM, and elements of MSMQ into the operating system. COM+ adds event services, load balancing, asynchronous queuing services with MSMQ, and an in-memory database for the MTS catalog. See also COM, DCOM, MSMQ, and MTS.

Explanatory material within source code not designed to be interpreted or compiled into the final application. In VBA, comments are usually preceded by an apostrophe (') but can also be created by preceding them with the Rem keyword.

A standardized dialog, provided by Windows, that can be created by a Windows API function call to functions contained in Cmdlg32.dll and its successors. Common dialogs include File Open, File Save, Print and Printer Setup, ColorPalette, Font, and Search and Replace. Access 2003 provides the Application.FileDialog object as a simpler alternative to the File Open and File Save common dialogs.

See operator.

To create an executable or object (machine-language) file from source (readable) code. In Access, compile means to create pseudocode (tokenized code), not native code, from the VBA source code you write in the code-editing window. The .NET Framework's CLR compiles source code to an intermediate language (IL) which executes on a specified processor class, such as Intel's x86 or Itanium processors.

See COM.

A key or index based on the values in two or more columns. See also index and key or key field.

In computer programming, a set of instructions or statements that requires more than one keyword or group of related keywords to complete. Select Case...Case...End Select is an example of a compound statement in VBA.

Combining two expressions, usually strings, to form a longer expression. The concatenation operator is & in Jet SQL and VBA, although VBA also permits and Transact-SQL requires the + symbol to be used to concatenate strings.

The condition when more than one user has access to a specific set of records or files at the same time. Also used to describe the capability of a database management system to handle simultaneous and potentially conflicting update queries against a single set of tables.

An object or application that can create or manipulate compound documents or host ActiveX controls.

An acronym for Common Object Request Broker Architecture, the primary competitor to Microsoft's COM- and DCOM-based technologies. See also COM, COM+, and DCOM.

A subquery that can't be evaluated independently. Subqueries depend on an outer query for their result. See also subquery and nested query.

A special field data type of Access 1.x and 2.0, and Jet 2.0 tables that numbers each new record consecutively; called an AutoNumber field in Jet 3+. See also AutoNumber.

An ADO MD object that provides the metadata for multidimensional data, such as that provided by Microsoft SQL Server Analysis or PivotTable Services. See also ADO MD, Cellset, OLAP, and Pivot Table Service.

The database opened in Access by choosing File, Open Database (or the equivalent) that contains the objects of an Access application.

The record in a Recordset object whose values are available to bound forms, reports, and controls. The current record supplies values of the record's data cells to control objects that are bound to the table's fields. The current record is specified by a record pointer.

An abbreviation for data access pages, a Web-based technology introduced in Access 2000 for generating data-bound Dynamic HTML pages for use on intranets. DAP use the Data Source Control (DSC) for data binding and other ActiveX Controls, such as the Office Web Components (OWC), for displaying and manipulating data. See also DHTML, DSC, and OWC.

The original container for all database objects in Access, often abbreviated DAO. The top member of the Jet DAO hierarchy is the DBEngine object, which contains Workspace, User, and Group objects in collections. Database objects are contained in Workspace objects. ADO 2.7 is the preferred alternative to DAO 3.6 in Access 2003, although ADO 2.1 2.6 are supported for backward compatibility. See also ADO.

See DAP.

Connecting two or more data-related objects, usually a data consumer to a data provider, to pass a Recordset or other data object between objects. Access 2003 has the capability to bind a variety of OLE DB data providers to OLE DB data consumers via ADO. See also ADO, data consumer, data object, and data provider.

An OLE DB term for an object that presents and/or manipulates data. All Access 2003 data-bound controls are capable of being OLE DB data consumers.

The process of describing databases and database objects such as tables, indexes, views, procedures, rules, default values, triggers, and other characteristics. SQL's Data Definition Language (DDL) defines the components of SQL-compliant databases.

The result of the data definition process. Also used to describe a set of database system tables that contain the data definitions of database objects, often called metadata.

The value contained in a data cell, also called a data item or simply an element. A piece of data that describes a single property of a data entity, such as a person's first name, last name, Social Security number, age, sex, or hair color. In this case, the person is the data entity.

A distinguishable set of objects that is the subject of a data table and usually has at least one unique data element. A data entity might be a person (unique Social Security number), an invoice (unique invoice number), or a vehicle (unique vehicle ID number, because license plates aren't necessarily unique across state lines).

The maintenance of rules that prevent inadvertent or intentional modifications to the content of a database that would compromise its accuracy or reliability. See also domain integrity and referential integrity.

A component of a database. Data objects include tables, views, indexes, stored procedures, columns, rules, triggers, database diagrams, and defaults. See also application object.

An OLE DB term for an object that connects to a database or other source of persistent data and supplies data to a data consumer. The SQLOLEDB OLE DB data provider for SQL Server is an example of a native OLE DB provider. MSDASQL, the OLE DB data provider for ODBC, is a nonnative (indirect) data provider.

The process of creating a hierarchical (also called chaptered) Recordset object using SHAPE syntax. See also hierarchical Recordset and SHAPE statements.

The feature that allows more than one user to access information stored in a database from the same or a different application. Multiuser Access applications that use Jet databases employ data sharing.

A database or other form of persistent (file) data storage. Data source commonly is used to describe an ODBC data source name (DSN). In Access 2003, a data source is a named OLE DB data provider or service provider.

See DSC.

A logical entity that represents a server running a client/server RDBMS, such as SQL Server, also called the data services tier. See also client tier, middle tier, and three-tier.

The description of how the computer is to interpret a particular item of data. Data types are generally divided into three families: strings that usually have text or readable content, numeric data, and objects. The types of numeric data supported vary with the compiler or interpreter. Most programming languages support a user-defined record or structure data type that can contain multiple data types within it. Field data types, which define the data types of values in Jet and SQL Server database tables, are distinguished from VBA data types in this book.

A set of related data tables and other database objects, such as a data dictionary or database diagram, that are organized as a group.

The individual(s) responsible for the administrative functions of client/server databases. The database administrator (DBA) has privileges (permissions) for all commands that might be executed by the RDBMS and is ordinarily responsible, directly or indirectly, for maintaining system security, including access by users to the RDBMS itself and performing backup and restoration functions.

A file in which databases and related information, such as transaction logs, are stored. Database devices usually have physical names (such as a filename) and a logical name (the parameter of the USE statement). In SQL Server 6.5 and earlier, database devices use the .dat file extension. SQL Server 7.0+ dispenses with database devices and stores databases and logs in conventional operating system files with .mdf and .ldf extensions, respectively.

The user who originally created a database. The database owner has control over all the objects in the database but can delegate control to other users. Access calls the database owner the creator. The database owner is identified by the prefix dbo in SQL Server.

A function that provides date and time information or manipulates date and time values.

An acronym for Distributed Common Object Model. Allows communication and manipulation of objects over a network connection. Windows NT 4.0 was the first Microsoft operating system to support DCOM (formerly called NetworkOLE). Microsoft's goal for its .NET technologies is to replace DCCOM with XML Web services. See also COM, COM+, and XML Web services.

An abbreviation for dynamic data exchange. DDE is a method that early versions of Windows and OS/2 use to transfer data between different applications. Automation implemented by ActiveX components provides a more robust method for communication between applications or components of applications. DDE is obsolete.

A condition that occurs when two users with a lock on one data item attempt to lock the other's data item. Most RDBMSs detect this condition, prevent its occurrence, and advise both users of the potential deadlock situation.

The act of removing errors in the source code for an application.

A statement that creates a user-defined data type, names a variable, creates a symbolic constant, or registers the prototypes of functions incorporated within dynamic link libraries.

A section of a VBA module reserved for statements containing declarations, such as Public or Private variables.

See DRI.

In text and not as a keyword, to create a user-defined data holder for a variable or constant. As a VBA keyword, to register a function contained in a dynamic link library in the declarations section of a module.

A value assigned or an option chosen when no value is specified by the user or assigned by a program statement.

The logical name of the database assigned to a user when logging in to the database application.

A condition in which master data in a table (such as invoices) is associated with detail data in a subsidiary table (invoice items). In this case, invoice items are dependent on invoices.

The member of a Jet replica set that allows changes in the design of objects, such as tables. The design-master replica usually (but not necessarily) is the .mdb file that is updated by briefcase replicas of the file. See also Briefcase replication.

One of two modes of operation of Access, also called design time or Design view. Design mode lets you create and modify database objects and write VBA code. The other mode is Run mode, also called runtime (when the application is executing).

An abbreviation for Dynamic HTML, an extension of HTML 4.0 that permits client-side scripting to modify the appearance and/or content of a Web page without requiring repeated round trips to the Web server. Early Web browsers implemented DHTML differently, so DHTML was suitable only for intranets on which all clients run a single browser. Most current browsers have very similar DHTML interpreters, which makes DHTML practical for Internet use.

A Recordset object that is cached on the client and doesn't have an active connection to its source database. After editing the data in an updatable disconnected Recordset, reconnecting to the server updates the database. Use of disconnected Recordsets, which requires VBA code, minimizes the number of simultaneous connections to the database. Access 2003's ADP support disconnected Recordsets. See also Recordset.

A database, usually of the client/server type, that's located on more than one database server, often at widely separated locations. Synchronization of data contained in distributed databases is most commonly accomplished by the two-phase commit or replication methods. See also replication and two-phase commit.

See DTC.

An abbreviation for dynamic link library, a file containing a collection of Windows functions designed to perform a specific class of operations. Most DLLs carry the .dll extension, but some Windows DLLs, such as Gdi32.exe, use the .exe extension. Applications call (invoke) functions within DLLs, as necessary, to perform the desired operation.

The file format for creating persistent OLE objects, now called ActiveX documents. ADP use docfiles to store application objects.Docfiles include file property values derived from the File menu's Properties command. See also ActiveX documents and ADP.

A set of functions, identical to the SQL aggregate functions, such as Sum or Max that you can incorporate within Jet queries. See also aggregate functions.

The process of assuring that values added to fields of a table comply with a set of rules for reasonableness and other constraints. For example, domain integrity is violated if you enter a ship date value that's earlier than an order date. Jet maintains domain integrity by field-level and table-level validation rules. See also business rules.

An abbreviation for declarative referential integrity, a set of SQL-92 SQL keywords, including CONSTRAINT, FOREIGN KEY, REFERENCES, and CASCADE, which, when included in the CREATE TABLE statement, enforce referential integrity rules for the table. Jet enforces referential integrity by an internal programming mechanism. SQL Server 7.0+ supports DRI; earlier versions used triggers to handle referential integrity violations. See also referential integrity and trigger.

An abbreviation for Data Source Control, one of the four Office Web Components (OWC). The DSC provides data binding for Data access pages. See also DAP and OWC.

An abbreviation for Microsoft's Distributed Transaction Coordinator, a feature of SQL Server required to support distributed transactions and Microsoft Transaction Server (MTS). See also distributed database and MTS.

See DDE.

See DLL.

A set of rows and columns in your computer's memory that represent the values in an attached table, a table with a filter applied, or a query result set. You can update the values of the fields of the underlying table(s) by changing the values of the data cells of an updatable dynaset object. In Jet 2+, Dynaset is a type of Recordset object. See also Recordset.

The official name for Netscape's JavaScript, now that standardization of JavaScript is under the aegis of the European Computer Manufacturers Association (ECMA). Microsoft's version of ECMAScript is JScript.

A condition of a VBA Variant variable that has been declared but hasn't been assigned a value. Empty is not the same as the Null value, nor is it equal to the empty or zero-length string (""). Assigning an empty Variant to a String variable, however, results in a zero-length string.

The capability of a control object to respond to user actions such as a mouse click, expressed as the True or False value of the Enabled property of the control.

A JOIN in which the values in the columns being joined are compared for equality. Only those rows with matching column values appear in the query result set. Access queries default to equi-joins also called inner joins. See also outer join, and self-join.

A procedure by which errors generated during the execution of an application are rerouted to a designated group of code lines (called an error handler) that performs a predefined operation, such as ignoring the error. If errors aren't trapped in VBA, the standard modal message dialog with the text message for the error that occurred appears.

The occurrence of an action taken by the user and recognized by one of the object's event properties, such as VBA's Click and DblClick event handlers for most controls. Events are usually related to mouse movements and keyboard actions; however, events also can be generated by code with the Timer control object and during manipulation of database objects. ADO Connection, Command, and Recordset objects trigger many data-related events.

A lock that prevents others from locking data items until the exclusive lock is cleared. Exclusive locks are placed on data items by update operations, such as SQL's INSERT, UPDATE, and DELETE. Jet and SQL Server 6.5 use page locking. SQL Server 6.5 provides row locking for INSERT operations, and SQL Server 7.0+ provides both INSERT and UPDATE row locking.

The second element of a number expressed in scientific notation, the power of 10 by which the first element, the mantissa, is multiplied to obtain the actual number. For +1.23E3, the exponent is 3, so you multiply 1.23 by 1,000 (10 to the third power) to obtain the result 1,230.

A combination of variable names, values, functions, and operators that return a result, usually assigned to a variable name. Result = 1 + 1 is an expression that returns 2 to the variable named Result. DiffVar = LargeVar SmallVar returns the difference between the two variables to DiffVar. Functions can be used in expressions, and the expression can return the value determined by the function to the same variable as that of the argument.strVar = Mid$(strVar, 2, 3) replaces the value of strVar with three of its characters, starting at the second character.

A new feature of SQL Server 2000, which supports properties previously restricted to Jet databases, such as lookup fields, subdatasheets, master-child table relationships, text for data validation messages, data-entry masks, and column formatting.

The table of a multidimensional database, also called a measures table, that stores numeric data (metrics). The facts table is related to the dimension tables. See also ADO MD, metrics, PivotTable Service, star schema, and snowflake schema.

Synonym for a column that contains attribute values. Also, a single item of information in a record or row.

The rule for relational databases requiring that a table that has been divided into multiple tables must be capable of being reconstructed to its exact original structure by one or more JOIN statements.

The rule for relational databases which dictates that tables must be flat. Flat tables can contain only one data value set per row. Members of the data value set, called data cells, are contained in one column of the row and must have only one value.

A variable, usually Boolean(True/False), which is used to determine the status of a particular condition within an application. The term set is often used to indicate turning a flag from False to True, and reset for the reverse.

In general usage, conditional expressions that control the execution sequence of instructions or statements in the source code of an application. If...Then...End If is a flow-control statement.

A column or combination of columns whose value must match a primary key in another table when joined with it. Foreign keys need not be unique for each record or row. See also primary key.

In this book, an Access Form object contains the control objects that appear on its surface and the code associated with the events, methods, and properties applicable to the form and its control objects.

Variables that are declared in the Declarations section of an Access form. These variables are said to have form-level scope and aren't visible to procedures outside the Form object in which the variables are declared.

The rule for relational databases which requires that only related data entities be included in a single table and that tables cannot contain data related to more than one data entity when many-to-one relationships exist among the entities.

When used with database management systems, an application used to access and view database records, as well as add to, edit, or delete them.

A subprogram called from within an expression in which a value is computed and returned to the program that called it through its name. Functions are classified as internal to the application language when their names are keywords. You can create your own user-defined functions in VBA by adding code between Function FunctionName...End Function statements.

A new SQL Server 2000 feature, which substitutes for views and stored procedures that return a single Recordset. Unlike views, you can specify the name of a user-defined function as the data source for a SELECT query. Functions enable ADP to execute the equivalent of nested Jet queries (a query against a SELECT query's Recordset). Functions also can return numeric and character (scalar) values.

Pertaining to the program as a whole. Global variables and constants are accessible to, and global variables can be modified by, code at the form, module, and procedure level. VBA uses the reserved word Public to create or refer to global variables.

See GUID.

In reports, one or more records that are collected into a single category, usually for the purpose of totaling. Database security systems use the term group to identify a collection of database users with common permissions. See also permissions.

An acronym for globally-unique identifier, pronounced "goo-id." GUIDs consist of 32 hexadecimal characters surrounded by French braces, as in {00000535-0000-0010-8000-00AA006D2EA4}, which the operating system creates from a combination of numeric values, including PC-specific values. COM makes extensive use of GUIDs to identify objects, interfaces, and other COM elements. Jet 4.0 and SQL Server 7.0+ have a GUID data type, primarily used to uniquely identify rows in a table for replication purposes.

An unsigned Long integer assigned by Windows to uniquely identify an instance (occurrence) of a module (application, hModule), task (hTask), window (hWnd), or device context (hDC) of a graphic object. Handles in 32-bit Windows applications are 32-bit unsigned long integers (dw, or double words). Also used to identify the sizing elements of control objects in Design mode. See also sizing handle.

A Recordset that contains detail records in the form of a Variant array. Hierarchical Recordsets are more efficient than conventional Recordsets for displaying one-to-many query result sets, because cells of the one side aren't repeated. The Pivot Table component is capable of displaying hierarchical Recordsets in Access's PivotTable view and DAP. See also Cellset, CubeDef, MDX, OLAP, and PivotTable Service.

Any computer on a network running an Internet Protocol (IP). See also IP and IP address.

An abbreviation for Hypertext Transport Protocol, the transport protocol used by the World Wide Web and private intranets.

A synonym for name or symbol, usually applied to variable and constant names.

For arrays, the position of the particular element with respect to others, usually beginning with 0 as the first element. In the context of database files or tables, index refers to a lookup table, usually in the form of a file or component of a file, that relates the value of a field in the indexed file to its record or page number and location in the page (if pages are used).

A new Microsoft form design and development tool codenamed XDocs during its early beta period for displaying and manipulating XML data. InfoPath uses XML schemas to validate data entry and XSLT to create form-based views of the data. The primary data sources for InfoPath forms are XML document files and XML Web services. Alternatively, InfoPath can use ADO to connect to Jet and SQL Server databases directly. The official name of InfoPath 1.0 is Microsoft Office InfoPath 2003. InfoPath can substitute for Access 2003's ADP for many applications. See also ADP, ADO, XML Schema, XML Web services, and XSLT.

In programming, setting all variables to their default values and resetting the point of execution to the first executable line of code. Initialization is accomplished automatically in VBA when you start an application.

Synonym for subquery. See also subquery.

A term applied to Automation servers, also called ActiveX DLLs, that operate within the same process space (memory allocation) of the Automation client. In-process servers commonly are called InProc servers.

A named SQL Server running on a Windows 2000/NT server. The default instance is the server's computer name. All versions of SQL Server 2000 except MSDE and the Personal Edition permit running multiple, independent instances of SQL Server on a single server. Instances other than the default instance are called named instances.

A whole number. In most programming languages, an integer is a data type that occupies two bytes (16 bits). Integers can have signs (as in the VBA Integer data type), taking on values from 32,768 to +32,767, or be unsigned. In the latter case, integers can represent numbers up to 65,535. SQL Server's integer datatype is a 32-bit bit value, which corresponds to VBA's Long datatype.

The group of data elements included in both tables that participate in a JOIN operation.

A private network that uses Internet protocols and common Internet applications (such as Web browsers) to emulate the public Internet. Intranets on LANs and high-speed WANs provide increased privacy and improved performance compared with today's Internet.

To cause execution of a block of code, particularly a procedure or subprocedure. Invoke also indicates application of a method to an object.

An abbreviation for Internet Protocol, the basic network transmission protocol of the Internet.

The 32-bit hexadecimal address of a host, gateway, or router on an IP network. For convenience, IP addresses are specified as the decimal value of the four address bytes, separated by periods, as in 124.33.15.1. Addresses are classified as types A, B, and C, depending on the subnet mask applied. See also subnet mask.

An abbreviation for Java Database Connector, despite Sun Microsystems' insistence that JDBC "doesn't stand for anything." JDBC is Java's purportedly platform-agnostic version of ODBC, which it closely resembles.

Microsoft's name for the database engine native to Access and Visual Basic. The name Jet came from the acronym for Joint Engine Technology, an early predecessor of Jet 4.0 used by Access 2003.

The dialect of ANSI SQL used by the Data Access Object and by all versions of Microsoft Access. For the most part, Access SQL complies with ANSI SQL-92. Jet SQL offers additional features, such as the capability to include user-defined functions within queries. See also Transact-SQL.

A basic operation, initiated by the SQL JOIN statement, which links the rows or records of two or more tables by one or more columns in each table. See also equi-join, outer join, and self-join.

A field that identifies a record by its value, also called a primary key [field]. Access automatically creates an index on primary key fields, which must consist of unique values. See also primary key and foreign key.

A value of a key field included in an index.

A word that has specific meaning to the interpreter or compiler in use and causes predefined events to occur when encountered in source code. Keywords and reserved words are not exactly the same. You can use keywords as variable, procedure, or function names, but you can't use reserved words as variable or constant names. Using keywords for this purpose, however, isn't a good programming practice.

In VBA programming, a name given to a target line in the source code at which execution results on the prior execution of a GoTo LabelName instruction. A label also is an Access control object that displays, but can't update, character values.

An acronym for local area network, a system comprising multiple computers that are physically interconnected through network adapter cards and cabling. LANs allow one computer to share specified resources, such as disk drives, printers, and modems, with other computers on the LAN.

The lowest level of an index. Indexes derive the names of their elements from the objects found on trees, such as trunks, limbs, and leaves.

A collection of functions, compiled as a group and accessible to applications by calling the function name and any required arguments. DLLs are one type of library; those used by compilers to provide built-in functions are another type.

An Access database that's automatically attached to Access when you launch it. Access library databases usually have the extension .mda; compiled libraries, which hide the original source code, use the extension .mde. Attachment of library databases to Access is controlled by Registry entries.

A table that's not stored in the currently open Access database (native table), but which you can manipulate as though it were a native table. Linked tables were called attached tables in Access 1.x and 2.0, and Jet 2.0.

A request for an exclusive lock on a data item that's repeatedly denied because of shared locks imposed by other users.

The scope of a variable declared within a procedure, rather than at the form, module, or global level. Local variables are visible (defined) only within the procedure in which they were declared. VBA uses the prefix Private to define functions, subprocedures, and variable of local scope.

See LAN.

A restriction of access to a table, portion of a table, or data item imposed to maintain data integrity of a database. Locks can be shared, in which case more than one user can access the locked element(s), or exclusive, in which the user with the exclusive lock prevents other users from creating simultaneous shared or exclusive locks on the element(s). Jet classifies locks as optimistic (a lock applied only when physically changing table data) and pessimistic (a lock applied for the duration of the user's editing operation.) Access 2003 uses page locks (8KB of the .mdb file), which can lock several adjacent records; earlier versions of Access used 2KB pages. Some RDBMSs provide row locks that lock only a single record. SQL Server 6.5 uses row locking for INSERT operations and page locking for UPDATE and DELETE operations. SQL Server 7.0+ offers row locking for all three operations.

A synonym for Boolean, a data type that can have true or false values only. Logical is also used to define a class of operators whose result is only True or False. VBA includes a Boolean data type.

A compound program flow-control structure that causes statements contained between the instructions that designate the beginning and end of the structure to be repeatedly executed until a given condition is satisfied. When the condition is satisfied, program execution continues at the source code line after the loop termination statement.

The first element of a number expressed in scientific notation that's multiplied by the power of 10 given in the exponent to obtain the actual number. For +1.23E3, the exponent is 3, so you multiply the mantissa, 1.23, by 1,000 (10 to the third power) to obtain the result: 1,230.

A database that controls user access to other databases, usually in a client/server system.

A table containing data on which detail data in another table depends. Master tables have a primary key that's matched to a foreign key in a detail table and often have a one-to-many relationship with detail tables. Master tables usually are called base tables.

An abbreviation for Microsoft Data Access Components, a collection of the files required to implement Microsoft's Universal Data Access strategy. MDAC includes ActiveX Data Objects (ADO), Remote Data Services (RDS), OLE DB, and ODBC. Office 2003 installs MDAC version 2.7, plus support for Jet databases. See also ADO, ODBC, OLE DB, RDS, and Universal Data Access.

An acronym for Multidimensional Expressions, an SQL-like language for creating and manipulating multidimensional data (cubes) created by Microsoft SQL Server Analytical Services. See also ADO MD, Cellset, CubeDef, OLAP, and PivotTable Service.

A Jet field data type that can store text with a length of up to about 64,000 bytes. (The length of Jet's Text field data type is limited to 255 bytes.)

Data that describes the structure, organization, and/or location of data. Metadata commonly is called "data about data."

One characteristic of an object and a classification of keywords in VBA. Methods are the procedures that apply to an Access object. Methods that apply to a class of objects are inherited by other objects of the same class and can be modified to suit the requirements of the object by a characteristic of an object.

Numeric data, also called measures, contained within a facts table of a multidimensional database. See also facts table.

The desktop version of SQL Server 7.0 included with Access 2000, often called MSDE 1.0. See MSDE.

See MSDE.

See MTS.

A logical entity that connects a data source tier to a client tier and implements business rules or performs other data-related services. See also business rule, client tier, data source tier, and three-tier.

An acronym for Multipurpose Internet Mail Extensions, an Internet standard that lets binary data be published and read on the Internet or intranets. The header of a file containing binary data exposes the MIME type of the data. Recordsets transported by RDS use a special MIME data type called the Advanced Data TableGram protocol (ADTG). See also ADTG and RDS.

A cliché used in software and hardware advertising to describe the need to use the promoted product if you want to create a reliable database system.

An acronym for Microsoft Office Developer Edition, which includes a royalty-free license to distribute Msaccess.exe for runtime use, the runtime version of Microsoft Chart, additional ActiveX controls, and other distributable components of Access 97, 2000, and 2002. The Access 2003 Developer Extensions, which are part of the Visual Studio Tools (VST) for Microsoft Office System 2003, replace MOD. See also Access 2003 Developer Extensions and VST.

A window or dialog that must be closed before users can take further action within the application.

A window or dialog that users can close or minimize without taking any other action; the opposite of modal.

A block of code, consisting of one or more procedures, for which the source code is stored in a single location (a form or report class module, or public Module object in Access). In a compiled language, a code module is compiled to a single object file.

Variables and constants that are declared in the Declarations section of a VBA module, such as an Access Module object. These variables have module-level scope and are visible (defined) to all procedures contained within the module, unless declared Public, in which case the variables are visible to all procedures.

An acronym for Microsoft [SQL Server] Desktop Edition, a special version of Microsoft SQL Server 2000 that is included with Office 2003 but not installed by default. ADP use MSDE or other SQL Server versions as their only data source; DAP offer the option of connecting to MSDE. See also ADP and DAP.

An acronym for Microsoft Message Queue Server, a middle-tier component (similar to Microsoft Transaction Server) that uses messaging techniques to permit execution of transactions over unreliable network connections. See also middle tier.

An acronym for Microsoft Transaction Server, a component-based transaction monitor (TM) and object request broker (ORB) for developing, deploying, and managing the middle tier of component-based applications. Access 2003 applications can connect to MTS objects using VBA code. MTS is an integral component of COM+. See also COM+, middle tier, ORB, three-tier, and TM.

See MDX.

Concurrent use of a single computer by more than one user, usually through by way remote terminals. UNIX is inherently a multiuser operating system. Jet uses the term multiuser to refer to front-end database applications that share a common back-end .mdb file on a network file server.

A method of interprocess communication, originally developed for OS/2, that provides a secure channel for network communication. Named Pipes is one of the methods of connecting to an SQL Server database, but TCP/IP connections are more common.

An SQL JOIN operation in which the values of the columns engaged in the join are compared, with all columns of each table in the join that don't duplicate other columns being included in the result. Same as an equi-join except that the joined columns aren't duplicated in the result. See also equi-join.

An SQL SELECT statement that contains subqueries. See also subquery. An Access query that uses one or more queries rather than tables as it data source is called a nested query also.

A combination of a carriage return, the Enter key (CR or Chr(13)), and line feed (LF or Chr (10)) used to terminate a line of text onscreen or within a text file. Other characters or combinations can be substituted for the CR/LF pair to indicate the type of newline character (soft, hard, deletable, and so on). The VBA newline constant is vbCrLf.

An index that stores key values and pointers to data based on these values. In this case, the leaf level points to data pages rather than to the data itself, as is the case for a clustered index. Equivalent to SET INDEX TO field_name in xBase. See also clustered index.

A set of five rules, the first three originally defined by Dr. E.F. Cobb, that are used to design relational databases. Five normal forms are generally accepted in the creation of relational databases. See also first normal form, second normal form, third normal form, fourth normal form, and fifth normal form.

Creation of a database according to the five generally accepted rules of normal forms. See also normal forms.

A JOIN statement that specifies that the columns engaged in the join don't equal one another. In Access, you must specify a not-equal join by using the SQL WHERE field1 <> field2 clause.

A variable of no value or of unknown value. The default values 0 for numeric variables and an empty string ("") for string variables aren't the same as the Null value. The NULL value in SQL statements specifies a data cell with no value assigned to the cell.

In programming, elements that combine data (properties) and behavior (methods) in a single container of code called an object. Objects inherit their properties and methods from the classes above them in the hierarchy and can modify the properties and methods to suit their own purposes. The code container can be part of the language itself, or you can define your own objects in source code.

A file with the extension .olb that contains information on the objects, properties, and methods exposed by an .exe or .dll file of the same filename that supports Automation. See also type library.

Permissions granted by the database administrator for others to view and modify the values of database objects, including data in tables.

See ORB.

An abbreviation for the Microsoft Open Database Connectivity API, a set of functions that provide access to client/server RDBMSs, desktop database files, text files, and Excel worksheet files through ODBC drivers. ODBC most commonly is used to connect to client/server databases, such as Microsoft SQL Server, Sybase, IBM DB2/Informix, and Oracle. Microsoft intends for OLE DB to replace ODBC and won't provide significant enhancements for ODBC. See ADO and OLE DB.

A feature of Access 97 and the Jet 3.5+ database engine that lets you use ODBC to access client/server databases without needing to load all of Jet 3.6. ODBCDirect conserves client resources if you need to connect only to SQL Server or another client/server RDBMS. ADO 2.1+ provides client/server features equivalent to ODBCDirect, so ODBCDirect is obsolete.

The number of bytes from a reference point, usually the beginning of a file, to the particular byte of interest. The first byte in a file, when offset is used to specify location, is always 0.

See OWC.

An acronym for online analytical processing, a technology that operates on nonrelational, multidimensional databases (data cubes). Microsoft SQL Server Analytical Services, a component of SQL Server 2000, enables the creation, manipulation, and distribution of multidimensional data. Analytical Services were called OLAP Services in SQL Server 7.0. See also ADO MD, PivotTable Service, snowflake schema, and star schema.

A Microsoft framework for providing a uniform interface to data from various sources, including text files and mainframe databases. OLE DB replaces ODBC as a means of database access, but includes an ODBC provider that takes the place of the ODBC driver manager. ADO is an Automation wrapper for OLE DB. See also ADO and ODBC.

See OLAP.

An abbreviation for online transaction processing. OLTP most commonly refers to database applications that update multiple tables, such as order-entry and reservation systems, which use transaction processing to assure data integrity. See also transaction.

One variable or constant on which an operator acts. In 1 + 2 = 3, 1 and 2 are operands, + and = are the operators. See also operator.

A keyword or reserved symbol that, in its unary form, acts on a single variable, or otherwise acts on two variables, to give a result. Operators can be of the conventional mathematics type such as +(add), (subtract), /(divide), and *(multiply), as well as logical, such as And or Not. The unary minus ( ), when applied to a single variable in a statement such as intVar = intVar, inverts the sign of intVar from to + or from + to .

A method of locking a record or page of a table that makes the assumption that the probability of other users locking the same record or page is low. With optimistic locking, the record or page is locked only when the data is updated, not during the editing process (LockType property set to adLockOptimistic). See also pessimistic locking.

An acronym for object request broker, a server-based application that provides a means for client applications to locate and instantiate middle-tier objects in three-tier applications. See also middle tier, MTS, and three-tier.

An SQL JOIN operation in which all rows of the joined tables are returned, whether or not a match is made between columns. SQL database managers that don't support the OUTER JOIN reserved words use the *= (LEFT JOIN) operator to specify that all the rows in the preceding table return and the =* (RIGHT JOIN) to return all the rows in the succeeding table.

A synonym for the primary query in a statement that includes a subquery. See also subquery.

The abbreviation for Microsoft Office Web Components, which consist of the following four ActiveX Controls: Data Source (DSC), PivotChart, PivotTable, and Spreadsheet. The DSC is used only with DAP; the remaining three controls can be embedded in forms. Office 2003 installs OWC versions 10 and 11; upgrading ADP created in Access 2000 (OWC 9.0) and 2002 (OWC 10.0) changes the version to 11.0). OWC have special licensing restrictions for users of Access 2003 runtime applications who don't have Office 2003 licenses. See also Access 2003 Developer Extensions, DAP, DSC, PivotChart, PivotTable, and Spreadsheet Control.

In tables of client/server RDBMSs, such as Microsoft SQL Server 7.0+, and Jet 3.6+ databases, a 8KB block that contains records of tables. Client/server and Jet databases lock pages, whereas other desktop databases usually lock individual records. Most RDBMSs require page locking when variable-length records are used in tables.

The equivalent of an argument, but associated with the procedure that receives the value of an argument from the calling function. The terms parameter and argument, however, are often used interchangeably. An ADO Parameter object provides or returns a value to or from a query or a stored procedure.

Authority given by the system administrator, database administrator, or database owner to perform operations on a network or on data objects in a database. Permissions also are called user rights.

An object that's stored in the form of a file or an element of a file, rather than only in memory. Jet Table and QueryDef objects are persistent because they're stored in .mdb files. Recordset objects, on the other hand, usually are stored in memory. Such objects are called temporal or impersistent objects. ADO 2.x lets you persist Recordset objects as files.

A method of locking a record or page of a table that makes the assumption that the probability of other users locking the same record or page is high. With pessimistic locking, the record or page is locked during the editing and updating process (LockType property set to adLockPessimistic). See also optimistic locking.

An ActiveX Control that displays charts or graphs derived from data displayed by a PivotTable control. Access 2003 adds a new PivotChart view for tables, queries, and forms. The PivotChart control is a member of the Office Web Components (OWC). See also OWC.

An ActiveX Control that displays data in spreadsheet format in which the user can expand or contract the level of detail displayed and interchange the axes of the data presentation. Access 2003 offers a PivotTable view of tables, queries, and forms. The PivotTable control is a member of the Office Web Components (OWC). See also OWC.

A Microsoft-trademarked desktop OLAP implementation that used ADO MD to operate on persistent (file-based) multidimensional data cubes created by a subset of Microsoft SQL Server Analytical Services. The PivotTable service delivers hierarchical Recordsets to PivotTables. See also ADO MD, Cellset, CubeDef, hierarchical Recordsets, and OLAP.

In typography, the unit of measurement of the vertical dimension of a font, about 1/72 of an inch. The point is also a unit of measurement in Windows, where it represents exactly 1/72 of a logical inch, or 20 twips. Unless otherwise specified, all distance measurements in VBA are in twips.

The sequence of execution of operators in statements that contain more than one operator.

The column or columns whose individual or combined values (in the case of a composite primary key) uniquely identify a row in a table.

A self-contained collection of source code statements, executable as an entity. All VBA procedures begin with the reserved word Sub or Function(which can be preceded by the Public, Private, Static, or Property reserved words) and terminate with End Sub or End Function.

An object exposed by an Automation server with a set of properties and methods applicable to the object. The application programming language of an Automation client application can manipulate the exposed object.

A projection identifies the desired subset of the columns contained in a table. You create a projection with a query that defines the fields of the table you want to display but without criteria that limit the records that are displayed.

One of two principal characteristics of objects (the other is methods). Properties define the manifestation of the object for example, its appearance. Properties might be defined for an object or for the class of objects to which the particular object belongs, in which case they are said to be inherited.

An object that supplies parameter marshaling and communication methods required by a client to instantiate an Automation component running in another execution environment, such as on a server. The proxy is located on the client PC and communicates with a corresponding stub on the server. The Office Web Services Toolkit's Web Service Reference tool automatically creates client proxies for XML Web services. See also three-tier, WSR, and XML Web services.

A search condition that data values must meet to be included in the result of the search.

To precede the name of a database object with the name of the database and the object's owner, or to precede the name of a file with its drive designator and the path to the directory in which the file is stored. The terms well-qualified path and well-formed path to a file appear often in documentation.

A request to retrieve data from a database with the SQL SELECT instruction (select query) or to modify data in the database, called an action query by Access.

A persistent Jet object that stores the Jet SQL statements that define a query. QueryDef objects are optimized, when applicable, by the Jet database engine's query optimizer and stored in a special format in the .mdb file.

An abbreviation for relational database management system. An RDBMS is an application that can create, organize, and edit databases; display data through user-selected views; and, in some cases, print formatted reports. Most RDBMSs include at least a macro language, and most provide a system programming language. Access, dBASE, Paradox, and FoxPro are desktop RDBMSs. SQL Server and Oracle are typical client/server RDBMSs.

An acronym for Remote Data Service, a lightweight version of ADO 2.x that provides transport for Recordset objects via DCOM or HTTP over intranets.

A single element of a relational database table that contains a cell for each field defined for the table. A record is the logical equivalent of a row of a spreadsheet. This book uses record when referring to table data and row for query result sets.

A temporary local image of a table or a query result set stored in the PC's memory or virtual memory. Recordset objects are the primary means for manipulating data with VBA.

In VBA, the incorporation of pointers to specific sets of programmable objects exposed by Automation servers in a type library and manipulated by VBA code in the Automation client. You create a VBA reference to a set of objects exposed by an Automation component in the References dialog that's accessible by choosing Tools, References when a code module is the active Access object. After you declare a reference to the set of objects, the VBA interpreter checks the syntax of your code against the syntax specified for the referenced object. You also can use predefined intrinsic constants for the referenced objects in your VBA code. See also type library.

Rules governing the relationships between primary keys and foreign keys of tables within a relational database that determine data consistency. Referential integrity requires the values of every foreign key in every table be matched by the value of a primary key in another table. Jet 2+ includes features for maintaining referential integrity, such as cascading updates and cascading deletions. SQL Server 7.0+ uses triggers or SQL's declarative referential integrity (DRI) keywords to maintain referential integrity. See also DRI and trigger.

To redisplay records in Access's Datasheet views or in a form or report so as to reflect changes others in a multiuser environment have made to the records.

A database that contains information required for the operation of 32-bit Windows The Registry also includes user information, such as user IDs, encrypted passwords, and permissions. Windows 98 and Windows XP/2003+/NT include Regedit.exe for editing the Registry. ActiveX components add entries to the Registry to specify the location of their .exe files. Automation servers add Registry entries for each object they expose.

Synonym for a table or a data table in an RDBMS.

See RDBMS.

Operators such as >, <, <>, and = that compare the values of two operands and return True or False depending on the values compared. They are sometimes called comparison operators.

A substitute for Access 97's Jet 3.6 Data Access Object that provides a more direct connection to the ODBC API. ADO 2.1+ replaces RDO, which is now obsolete.

See RDS.

The process of duplicating database objects (usually tables) in more than one location, including a method of periodically rationalizing (synchronizing) updates to the objects. Database replication is an alternative to the two-phase commit process. Microsoft SQL Server 7.0+ supports replication of databases across multiple Windows 2000+/NT servers. See also Briefcase replication and two-phase commit.

Microsoft's XML representation of the design of Access forms and reports that is used as an intermediary when saving Access 2003 forms and reports as XML files or converting forms and reports to DAP.

A word that's reserved for specific use by the programming language. You can't assign a reserved word as the name of a constant, variable, function, or subprocedure. Although the terms reserved word and keyword often are used interchangeably, they don't describe an identical set of words. VBA reserved words are set in bold monospace type in this book. See also keyword.

A query statement that defines a subset of the rows of a table based on the value of one or more of its columns. A restriction more commonly is called a criterion.

In transaction processing, the cancellation of a proposed transaction that modifies one or more tables and undoes changes, if any, made by the transaction before a COMMIT or COMMIT TRANSACTION SQL statement.

A synonym for procedure.

A set of related columns that describe a specific data entity. Row is a synonym for record. This book uses row when referring to query result sets and record for data in tables.

See aggregate functions.

An OLE DB term for a set of rows returned by a fetch with a block cursor. ADO creates Recordsets from rowsets.

A specification that determines the data type and data value that can be entered in a column of a table. Rules are classified as validation rules and business rules. See also business rules.

The mode when Access 2003 is executing your application. Run mode is called runtime by Microsoft; however, the term runtime normally is used to describe the distributable version of Access (runtime Access) applications. See also Access 2003 Developer Extensions.

In programming, the extent of visibility (definition) of a variable. VBA has global (Public, visible to all objects and procedures in the application), form/report (Private, visible to all objects and procedures within a single form or report), module (visible to all procedures in a single module file), and local (Dim, visible only within the procedure in which declared) scope. The scope of a variable depends on where it's declared. See also form level, global, local, and module level.

The rule for relational databases requiring that columns that aren't key fields each be related to the key field that is, a row might not contain values in data cells that don't pertain to the value of the key field. In an invoice item table, for instance, the columns of each row must pertain solely to the value of the invoice number key field.

To locate a specific byte, record, or chunk within a disk file. The Seek method of Access VBA can be used only with DAO Recordset objects of the Table type and requires that the table be indexed. ADO 2.1+ has a Seek method; previous versions of ADO didn't offer Seek.

The list of column names, separated by commas, that specify the columns to be included in the result of a SELECT statement.

An SQL JOIN operation used to compare values within the columns of one table. Self-joins join a table with itself, requiring that the table be assigned two different names, one of which must be an alias.

A reserved symbol used to distinguish one item from another, as exemplified by the use of the exclamation point (!, bang character) in Access to separate the name of an object class from a specific object of the class, and an object contained within a specified object. The period separator (., dot) separates the names of objects and their methods or properties.

A file in which one record follows another in the sequence applicable to the application. Text files, for the most part, are sequential.

An OLE DB term for an object that is both a data consumer and a data provider to another data consumer. OLE DB service providers include query engines and other intermediaries, such as the Remote Provider for enabling ADO 2.0+ data sources to use Remote Data Service. See also RDS.

In DAO, an instance of the Jet database engine for a single user represented by the Workspace object. You can establish multiple sessions that become members of the Workspaces collection. With ADO, a Connection object represents a session. In RDBMS terminology, the period between the time that a user opens a connection to a database and the time that the connection to the database is closed.

An SQL-like language for defining parent-child relationships within hierarchical Recordsets. See also hierarchical Recordsets.

A lock created by read-only operations that doesn't allow the user who creates the shared lock to modify the data. Other users can place shared locks on data so they can read it, but none can apply an exclusive lock on the data while any shared locks are in effect.

An acronym for security ID, a numeric value that identifies a logged-in user who has been authenticated by Windows NT or a user group. Access workgroup files use SIDs to authenticate users of secured Jet databases.

A debugging process by which the source code is executed one line at a time to allow you to inspect the value of variables, find infinite loops, or remove other types of bugs.

The small black rectangles on the perimeter of control objects that appear on the surface of the form or report in Design mode when the object is selected. You drag the handles of the rectangles to shrink or enlarge the size of control objects.

An alternative to the star schema for multidimensional data. Snowflake schema store dimension definitions in a set of hierarchical tables, rather than the star schema's individual tables. See also ADO MD, facts table, PivotTable Service, and star schema.

Originally an acronym for Simple Object Access Protocol, an XML-based messaging protocol for exchanging information between applications, usually over a network. SOAP is one of the primary enablers of XML Web services. SOAP has three parts: an envelope that describes and incorporates the message's content; rules for encoding and decoding the content; and methods for processing XML messages or to represent remote procedure calls (RPCs) and responses to RPCs. Office 2003 members and SQL Server 2000 support SOAP 1.1, which isn't a W3C standard. The May 8, 2000 SOAP 1.1 note is at http://www.w3.org/TR/2000/NOTE-SOAP-20000508/. The latest version of the W3C' SOAP 1.2 standard is at http://www.w3.org/TR/soap12-part0/. See also WSDL and XML Web services.

A set of components or libraries designed to simplify the process of encoding conventional data or objects as SOAP messages. Office 2003 installs elements of the Microsoft SOAP Toolkit 3.0 to enable writing VBA code that consumes XML Web services. SOAP Toolkit 3.0 also provides the Internet Services API (ISAPI) extensions needed to host (provide) Web services and filters to listen for incoming SOAP messages. See also XML Web services.

An ActiveX Control that emulates an Excel worksheet. The Office 2003 version of the control can be bound to data. The Spreadsheet control is one of the Office Web Components (OWC). See also OWC.

An acronym, pronounced "S-Q-L", for Structured Query Language, a language developed by IBM Corporation for processing data contained in mainframe computer databases. (Sequel is the name of a language, similar to SQL, developed by IBM but no longer in use.) SQL has now been institutionalized by the creation of an ANSI standard for the language.

See aggregate functions.

The most common schema (database design) for multidimensional data. Multiple base tables storing dimension definitions form the points of a star. The body of the star is the dependent facts table. See also ADO MD, facts table, PivotTable Service, and snowflake schema.

A syntactically acceptable (to the interpreter or compiler of the chosen language) combination of instructions or keywords and symbols, constants, and variables. A VBA statement must appear on a single line or use the line-continuation pair (a space followed by an underscore) to permit multiple-line statements.

When referring to a variable, a variable that retains its last value until another is assigned, even though the procedure in which it is defined has completed execution. All global variables are static. VBA variables declared as Static are similar to global variables, but their visibility is limited to their declared scope. The term is also used to distinguish between statically linked (conventional) executable files and those that use DLLs.

A set of SQL statements (and with those RDBMSs that support them, flow-control statements) that are stored under a procedure name so that the statements can be executed as a group by the database server. Some RDBMSs, such as Microsoft SQL Server, precompile stored procedures so that they execute more rapidly.

A data type used to contain textual material, such as alphabetic characters and punctuation symbols. Numbers can be included in or constitute the value of string variables, but can't be manipulated by mathematical operators.

Two or more keywords used together to create an instruction, which is usually conditional in nature. In C, C#, and C++ programming, a user-defined data type. See also compound.

See SQL.

A procedure or user-defined function that, in VBA, consists only of Sub SubName...End Sub or Function FnName... End Function lines with no intervening code. Access automatically creates stubs for subprocedures for event-handling code stored in Form and Report objects. Stubs block out the procedures required by the application that can be called by the main program. The intervening code statements are filled in during the programming process.

An Access form contained within another form, which commonly has a link to the data source for the parent form.

A local bit mask (set of flags) that specifies which bits of the IP address specify a particular IP network or a host within a subnetwork. An IP address of 128.66.12.1 with a subnet mask of 255.255.255.0 specifies host 1 on subnet 128.66.12.0. The subnet mask determines the maximum number of hosts on a subnetwork.

A procedure called by a procedure other than the main procedure (WinMain in Windows). In Access, all procedures except functions are subprocedures because Msaccess.exe implements the WinMain function.

An SQL SELECT statement that's included (nested) within another SELECT, INSERT, UPDATE, or DELETE statement or nested within another subquery.

An Access report contained within another report.

The set of rules governing the expression of a language, often called grammar. As with English, Spanish, Esperanto, or Swahili, programming languages each have their own syntax. Some languages allow much more latitude (irregular forms) in their syntax. VBA has a relatively rigid syntax, whereas C provides more flexibility at the expense of complexity. The syntax of SQL is defined by ANSI, but most RDBMS vendors add proprietary extensions to SQL. See also ANSI.

The individual(s) responsible for the administrative functions for all applications on a LAN or users of a UNIX cluster or network, usually including supervision of all databases on servers attached to the LAN. If the system administrator's (sa's) responsibility is limited to databases, the term database administrator (DBA) is ordinarily assigned.

Databases that control access to databases on a server or across a LAN. Microsoft SQL Server has four system databases: the master database, which controls user databases; tempdb, which holds temporary tables; msdbdata for data relating to management tasks, and model, which is used as the skeleton to create new user databases. Any database that's not a user database is a system database.

Functions that return data about the database (metadata) rather than from the content of the database.

An object defined by Access rather than by the user. Examples of Access system objects are the Application, Screen and Debug objects.

A data dictionary table that maintains information on users of the database manager and each database under the control by the system. Jet system tables carry the prefix MSys and are hidden.

The order in which the focus is assigned to multiple control objects within a form or dialog with successive pressing of the Tab key.

A database object consisting of a group of records (rows) divided into fields (columns) that contain data or Null values. A table, which also is called a relation, is treated as a database object.

A disk file containing characters with values ordinarily ranging from Chr (1) through Chr (127) in which lines of text are separated from one another with newline pairs (Chr (13) & Chr(10)).

An SQL JOIN operation that uses comparison or relational operators in the JOIN statement. See also operator.

The rule for relational databases which imposes the requirement that a column that's not a key column can't depend on another column that's not a key column. The third normal form is generally considered the most important because it's the first in the series that isn't intuitive.

The architecture of a database application, usually involving a client/server RDBSM, where the front-end application is separated from the back-end RDBMS by a middle-tier application. In Access applications, the middle tier usually is implemented as an ActiveX (Automation) component, which implements the database connection, enforces business rules, and handles transfer of data to and from databases of the RDBMS. See also business rules.

The date and time data attributes applied to a disk file when created or edited. SQL Server supports the timestamp field data type to resolve concurrency issues when updating tables.

A native Access form property that's invisible in Run mode and used to trigger a Timer event at preselected intervals.

An abbreviation for transaction monitor, an application that manages database transactions, usually between more than one database, to assure data consistency during INSERT and UPDATE operations. See also MTS.

A property of an object, such as a check box, that alternates its state when repeatedly clicked or activated by a shortcut key combination.

A group of processing steps that are treated as a single activity to perform a desired result. A transaction might entail all the steps necessary to modify the values in or add records to each table involved when a new invoice is created. RDBMSs that can process transactions must include the capability to cancel the transaction by a rollback instruction or to cause it to become a permanent part of the tables with the COMMIT or COMMIT TRANSACTION statement. See also rollback.

A synonym for transaction manager. See also TM.

A superset of ANSI SQL-92 used by Microsoft SQL Server. Transact-SQL (T-SQL) includes flow-control instructions and the capability to define and use stored procedures that include conditional execution and looping.

A stored procedure that executes automatically when a user or query executes an instruction that might affect the referential integrity of a database. Triggers usually occur before the execution of INSERT, DELETE, or UPDATE statements so that the effect of the statement on referential integrity can be examined by a stored procedure before execution. See also stored procedure.

The smallest unit of measurement in Windows and the default unit of measurement of VBA. The twip is 1/20 of a point, or 1/1440 of a logical inch.

A process applicable to updates to multiple (distributed) databases that prevents a transaction from completing until all the distributed databases acknowledge that the transaction can be completed. The replication process has supplanted two-phase commit in most of today's distributed client/server RDBMSs. See also replication.

See data type.

A file with the extension .tlb that provides information about the types of objects exposed by an ActiveX component or Automation server. See also object library.

See operator.

An abbreviation for Uniform Naming Convention, the method of identifying the location of files on a remote server. UNC names begin with \\. All 32-bit Windows applications support UNC.

A replacement for the 7- or 8-bit ASCII and ANSI representations of characters with a 16-bit model that allows a wider variety of characters to be used. Windows Me/98 and Windows XP/2000+/NT support Unicode. Access 95 and later support Unicode data.

An index in which no two key fields or combinations of key fields on which the index is created can have the same value.

Microsoft's all-encompassing database strategy based on COM, DCOM, OLE DB, ADO, MTS, Internet Information Server, ASP, and other proprietary Windows technologies. See also ADO, ASP, COM, DCOM, MTS, and OLE DB.

A permanent change to data values in one or more data tables. An update occurs when the INSERT, DELETE, UPDATE, or TRUNCATE TABLE SQL commands are executed.

A data type, also called a record, that's specified in your VBA source code by a Type...End Type declaration statement in the Declarations section of a module. The elements of the user-defined record type can be any data type valid for the language and can include other user-defined types.

A group of instructions combined under a single name and executed as a block when the name is invoked in a statement executed by the user.

The process of determining whether an update to a value in a table's data cell is within a preestablished range or is a member of a set of allowable values. Validation rules establish the range or set of allowable values. Access 2+ supports validation rules at the field and table levels.

The name given to a symbol that represents or substitutes for a number (numeric), letter, or combination of letters (string).

An abbreviation for Visual Basic for Applications, the official name of which is Visual Basic, Applications Edition. VBA is Microsoft's common application programming (macro) language for members of Microsoft Office and Visual Basic 6.0. Each application has its own "flavor" of VBA as a result of automatically created references to the application's object hierarchy in VBA code.

An abbreviation for Visual Data Tools, which comprises the Query Designer and Database Designer for SQL Server. VDT (commonly called the da Vinci toolset) lets you create views, modify data structures, and add tables to Microsoft SQL Server databases. Access Data Projects use a subset of the VDT to create new tables, modify table structure, and add data to tables.

The method by which data is presented for review by users, usually onscreen, or supplied to intermediary applications. Views can be created from subsets of columns from one or more tables by implementing the SQL CREATE VIEW instruction. A Jet select query is equivalent to an SQL Server view.

See VBA.

See VDT.

An abbreviation for Visual Studio Tools for Microsoft Office System 2003. VST includes the Access 2003 Developer Extensions and the SQL Server 2000 Developer Edition, which adds SQL Server 2000 management tools SQL Server Enterprise Manager, Query Analyzer, Books Online, and the other database management components missing from MSDE 2000. VST also provides Visual Studio .NET templates for extending Microsoft Word and Excel 2003 macros with .NET managed code. See also Access 2003 Developer Extensions.

An abbreviation for the World Wide Web Consortium, which "develops interoperable technologies (specifications, guidelines, software, and tools) to lead the Web to its full potential." W3C's home page is at http://www.w3.org/.

A vendor-supported consortium (WS-I.org) dedicated to developing recommendations to make Web services created by different SOAP toolkits and hosted by common application servers interoperate with one another. Go to http://www.ws-i.org for additional information about WS-I's activities.

See XML Web services.

See WSR.

A character that substitutes for and allows a match by any character or set of characters in its place. The DOS ? and * wildcards are used in Jet expressions; SQL uses _ and % as wildcards.

An acronym (pronounced "wizdle") for Web Service Description Language, an XML dialect that describes the capabilities, data structure, and location of an XML Web service. Like SOAP 1.1, WSDL 1.1 isn't a W3C standard; the WSDL 1.1 W3C Note of March 15, 2001 is at http://www.w3c.org/TR/wsdl. The current version of the W3C specification for WSDL 1.2 is at http://www.w3.org/TR/wsdl12/.

An abbreviation for the Web Services Reference 2.0 tool, a COM add-in for the VBA Editor that's installed by the Office Web Services Toolkit. WSR uses an XML Web service's WSDL 1.1 file to generate the VBA code for an XML Web service proxy. WSR simplifies the VBA code for enabling an Access application to consume an XML Web service. See SOAP toolkit, WSR, and XML Web services.

Any language interpreter or compiler or a database manager built on the dBASE III+ model and incorporating all dBASE III+ commands and functions. Microsoft's Visual FoxPro and Computer Associates' CA-Clipper are xBase dialects.

The codename for Microsoft InfoPath during its beta period. See InfoPath.

An abbreviation for XML Data Reduced, Microsoft's proprietary (non-standard) implementation of an XML-based schema definition of data structures for interpreting classes of data-related XML documents. ADO Recordsets saved to XML files use the XDR schema. W3C's XML Schema 1.0 specification has replaced XDR, which is obsolete except for some early XML features of SQL Server 2000. See also XML Schema.

A W3C standard for expressing HTML 4.0 as an XML application. Extensible HyperText Markup Language requires the HTML content to be a well-formed XML document with all HTML tag names in lower case. Valid XHTML documents must pass validation tests against an XML schema or Document Type Definition (DTD). The XHTML 1.0 standard is at http://www.w3.org/TR/xhtml1/.

A W3C standard for XML Information Set, a set of definitions for 11 different types of information items and their properties that can make up a conforming XML document. All but three information items (Document, Element, and Namespace) are optional. Adherence to XML Infoset definitions is especially important for authoring data-related XML documents that interoperate with multiple XML parsers and schema validators. XML documents exported by Access conform to the XML Infoset definitions. W3C's XML Information Set recommendation is at http://www.w3.org/TR/xml-infoset/.

An abbreviation for Extensible Markup Language, a derivative of SGML (Standardized General Markup Language), that permits definition of custom markup tags. XML is especially useful for displaying and manipulating data when using the Internet HTTP protocol. XML documents carry an .xml extension. Data-related variations of XML include XML Query and XML Schema. The World Wide Web Consortium (W3C, http://www.w3.org/) maintains the XML standards, called recommendations. The second edition of the XML 1.0 standard (October 6, 2000) is at http://www.w3.org/TR/REC-xml.

A standardized XML document type (xsd XML namespace) for describing the structure of documents and data. Most XML schema documents have an .xsd extension. XML Schema was a W3C candidate recommendation when this books was written. Access 2003's XML import/export features use the XSD namespace; some SQL Server 2000 XML features use XDR. A primer for XML Schema, which uses a purchase order as the model, is available at http://www.w3.org/TR/xmlschema-0/. See also XDR.

Software components that reside on Web servers and use SOAP as the protocol and XML as the message format to communicate with one another usually by HTTP or secure HTTP (HTTPS) through corporate and personal firewalls. Microsoft uses the XML prefix to distinguish SOAP-based Web services from conventional Web-based services, such as search engines, portals, and e-commerce. W3C's Web Services Activity (http://www.w3.org/2002/ws/) coordinates development of basic XML Web services specifications. See also SOAP, WSDL, and XML.

An abbreviation for Extensible Stylesheet Language, which specifies how the contents of an XML document are formatted for presentation to the user with XML Formatting Objects. XSL documents use the .xsl extension. The October 15 2001 XSL 1.0 recommendation is at http://www.w3.org/TR/xsl/.

An abbreviation for XSL Transformations, part of the XSL standard but maintained by a different W3C working group (WG). XSLT is a language for transforming an XML document into other XML documents or XHTML pages. XSLT documents also use the .xsl extension. Access 2003 makes extensive use of XSLT in conjunction with the use of ReportML for exporting Access application objects to XML/XSL files and DAP. The W3C recommendation for XSLT 1.0 (November 16, 1999) is at http://www.w3.org/TR/xslt/. A working draft of XSLT 1.1 and a requirements document for XSLT 2.0 also are available from the W3C Web site. See also ReportML.

A field of a table whose allowable values are Yes (True) or No (False). Yes/No fields were called logical or Boolean fields by Access 95 and earlier.