Microsoft Jet Database Engine Programmer's Guide - Introduction

Article
01/28/2010

The following is an exerpt from the Microsoft Jet Database Engine Programmer's Guide. Microsoft TechNet also includes Chapters 1, 2 and 3 of the guide. If you are interested in obtaining the entire book, please contact Microsoft Press or check your local technical bookstore.

Preface

Since its introduction in 1992, the Microsoft Jet database engine has been in a unique position. Because Microsoft Jet is not a stand-alone product — you cannot buy it at your local software retailer — most developers who use it have learned about its functionality in a second-hand fashion from the documentation included in Microsoft Access, Microsoft Office, Microsoft Visual Basic®, Microsoft Visual C++®, or Microsoft Visual J++®.

The Microsoft Jet Database Engine Programmer's Guide is the definitive reference for all users of Microsoft Jet, from developers programming in applications such as Microsoft Access and Microsoft Excel, to those working in a dedicated programming environment such as Visual Basic, Visual C++, or Visual J++. This guide offers comprehensive coverage of all aspects of Microsoft Jet, including the structure of the engine, the services it provides, and complete examples showing how to use its functionality in your development projects.

Who This Guide Is For

Because many applications use Microsoft Jet, this guide is directed toward a large audience of developers and programmers. If you are leafing through this guide, chances are you already have at least a rough idea of what Microsoft Jet is and where it is used. As of this writing, the following applications use various versions of Microsoft Jet.

Application	Microsoft Jet version
Microsoft Access 1.0	1.0
Microsoft Access 1.1	1.1
Microsoft Access 2.0	2.0 and 2.5
Microsoft Access 95	3.0
Microsoft Access 97	3.5
Visual Basic 3.0	1.1 (or 2.0 using the Visual Basic compatibility layer)
Visual Basic 4.0 (16-bit)	2.5
Visual Basic 4.0 (32-bit)	3.0
Visual Basic 5.0	3.5
Microsoft Excel 95	3.0
Microsoft Excel 97, Microsoft PowerPoint® 97, and Microsoft Word 97	3.5
Microsoft Project 4.1 and Microsoft Project 95	3.0
Microsoft Foundation Classes (MFC) versions 4.x	3.0
MFC version 5.0	3.5
Microsoft Internet Information Server 3.0	3.5

Although this guide will be most helpful if you are primarily developing database applications in Microsoft Access, Visual Basic, or Visual C++, it also provides information that you can apply when developing applications that require database access from Microsoft Excel 97 and other applications that support Visual Basic for Applications (VBA) version 5.0.

What This Guide Covers

This guide covers Microsoft Jet version 3.5. All examples throughout this guide are compatible with this version. Special notes are placed throughout that describe differences between version 3.5 and earlier versions.

What You Should Already Know

This guide is written to enhance your existing database knowledge with information specific to Microsoft Jet. It's assumed you're comfortable with programming in a procedural language, are familiar with the concepts of relational databases and queries, and know something of Structured Query Language (SQL).

Most of the code examples are written in VBA 5.0, which is supported by Visual Basic 5.0, all Microsoft Office 97 Professional Edition applications except Microsoft Outlook™, and other VBA 5.0 host applications, such as Visio Technical 4.5. Although most code examples are given in VBA 5.0, they can be easily ported to other languages, such as C++. Chapter 11, "Programming with DAO in C++ and Java," provides Data Access Objects (DAO) code examples for Visual C++ and Visual J++.

How to Use This Guide

The Microsoft Jet Database Engine Programmer's Guide contains all the information necessary to use Microsoft Jet in your development efforts. Whether you use Microsoft Access, Visual Basic, or any other environment that hosts Microsoft Jet, you will learn the details of this complex and powerful system.

The guide is divided into thirteen chapters, with five appendixes. Although much of the topic matter is not linear in nature, careful attention has been paid to the order of the chapters. Information presented in later chapters builds on material in previous chapters. Feel free to skip to specific chapters, but you should read this introduction, and Chapter 1, "An Overview of Microsoft Jet," as a prerequisite for later chapters.

Each chapter follows the same basic structure. An introduction summarizes the topic matter to be covered and includes a brief outline of the chapter contents. The main body of the chapter is then presented. Each chapter, where applicable, ends with an optimizations section that describes how to improve performance in the areas covered in the chapter.

The following section outlines each of the chapters.

Chapter 1 Microsoft Jet - How to Use This Guide

This chapter introduces you to Microsoft Jet with a brief history of its development. It then covers the structure of Microsoft Jet, the components used, the services provided, and the layout of the database file.

Chapter 2 Introducing Data Access Objects

Data Access Objects (DAO) provide a powerful framework for calling Microsoft Jet functions from your programming code. Coverage includes the DAO object hierarchy, a description of each of the object types, a primer on using DAO in your applications, an overview of features new to DAO 3.0 and 3.5, a discussion of compatibility issues, and some tips on optimizing DAO code.

Chapter 3 Data Definition and Integrity

Microsoft Jet provides powerful facilities for defining data and maintaining data integrity. It provides two interfaces for data definition, DAO and SQL, and offers a rich array of data integrity tools. This chapter shows you how to create tables, fields, queries, and indexes with both DAO and SQL. You also learn how to set up validation rules and referential integrity constraints.

Chapter 4 Queries

Microsoft Jet has one of the most powerful query processors available in a desktop database. This chapter is the complete reference to the query services that Microsoft Jet provides. An overview of the query engine explains the query processor and the operations it performs. You learn about the different types of queries, how to create and save queries, and how to optimize query performance.

Chapter 5 Working with Records and Fields

Manipulating data is the most fundamental operation in a database. For Microsoft Jet, the DAO layer provides programmable data manipulation with the Recordset and Field objects. This chapter provides a comprehensive look at the properties and methods of the Recordset object, along with several of the data-manipulation properties and methods of the Field object. You also learn how to use transactions to maintain the integrity of your data.

Chapter 6 Creating Multiuser Applications

Microsoft Jet provides services for database applications shared among multiple users. This chapter explores strategies for controlling multiuser access to data by locking records and handling resource contention. It discusses how an application can handle locking conflicts in a multiuser environment. It also compares how the engine works in file-server and client/server systems, and provides a brief introduction on how Microsoft Jet databases can be shared on the World Wide Web.

Chapter 7 Database Replication

Introduced with Microsoft Jet 3.0, database replication provides powerful distributed computing capabilities. By using database replication, programmers and database administrators can reproduce a database so that each user can simultaneously work on his or her own copy, or replica, of the database. Once created, the replicas can be located on different computers, in different offices, or even in different countries, and updates to the design and data in replicas can be synchronized with one another. This chapter describes the major uses for — and components of — database replication, how to program replication into your applications, and important issues affecting the synchronization of data updates and design changes. It also discusses two new replication features added in Microsoft Jet 3.5: creating and maintaining partial replicas (replicas that contain a subset of the data contained in a master database), and synchronizing replicas located on Internet or intranet servers.

Chapter 8 Accessing External Data

One great strength of Microsoft Jet is its ability to seamlessly connect to data in foreign formats. This chapter shows you how to use Microsoft Jet to connect to other desktop databases such as FoxPro®, dBASE, and Paradox; to spreadsheets such as Microsoft Excel and Lotus 1-2-3; to character-delimited and fixed-length text files; to Microsoft Exchange and Outlook folders and address books; and to tables and lists contained in HTML files. You will learn how to link to each external data source Microsoft Jet can connect to and how to directly open data sources. How to create tables in foreign formats from Microsoft Access by using the TransferDatabase method is also discussed. Performance guidelines are also provided.

Chapter 9 Developing Client/Server Applications

Client/server computing has become the accepted way of upsizing database applications. This chapter introduces you to the concepts of client/server development using Microsoft Jet. The relationship between Microsoft Jet and the Open Database Connectivity (ODBC) standard is discussed, and examples are given for connecting to Microsoft SQL Server™ as a back-end database by using both Microsoft Jet and the new ODBCDirect technology introduced in Microsoft DAO 3.5. This chapter also discusses techniques for creating client/server applications and offers tips for optimizing their performance.

Chapter 10 Managing Security

Microsoft Jet provides a sophisticated security environment that controls access to object definitions and data. This chapter discusses the Microsoft Jet security model and how to implement it successfully. A discussion of workgroups and permissions explains the model, and complete code examples for programmatically creating and maintaining security settings augment the text. This chapter also discusses how to password-protect a database in order to control opening a database, and how to secure Visual Basic code in a Microsoft Access database by saving the database as an .mde file.

Chapter 11 Programming with DAO in C++ and Java

This chapter describes how to programmatically access Microsoft Jet by using the DAO C++ classes, also known as dbDAO. The dbDAO classes expose the same Data Access Objects and functionality as Visual Basic, and use similar syntax. By handling such tasks as managing object-instance lifetime, these classes make programming in C++ as convenient as programming in Visual Basic. This chapter also discusses how to programmatically access Microsoft Jet databases with Java by using the DAO Automation interfaces in Visual J++.

Chapter 12 OBDC Desktop Database Drivers

ODBC drivers are used by ODBC-enabled applications to access data created in any of a variety of database management systems (DBMSs). The Microsoft ODBC Desktop Database Drivers use the Microsoft Jet database engine to access data in six desktop DBMSs. This chapter discusses the architecture of the drivers (and their use of the Microsoft Jet engine), Microsoft Jet features exposed by the drivers, implementation details, and performance considerations.

Chapter 13 Optimizing Performance

This chapter covers database repair and compacting, along with an in-depth technical discussion of how Microsoft Jet 3.5 has been optimized for performance. It also includes a discussion of how to tune Microsoft Jet engine performance by using settings in the Windows® registry or by using the SetOption method. Information about unsupported tuning functions rounds out the chapter.

Appendix A Specifications

This appendix lists the specifications of Microsoft Jet data types, showing table and field types, query types, and data limits.

Appendix B SQL Reference

Using Backus-Naur Form (BNF), this appendix provides a syntactical description of the Microsoft Jet implementation of SQL.

Appendix C Registry Settings

This appendix describes each of the settings that Microsoft Jet 3.5 installs in the Windows registry.

Appendix D Error Reference

This appendix lists the error number, error message, and class of each Microsoft Jet error.

Appendix E MICROSOFT Jet Components

This appendix list the file names and descriptions of the components installed for Microsoft Jet 3.5.

Typographic Conventions

This guide provides comprehensive examples for many of the topics discussed. The following typographic conventions are used:

Example of convention	Description
Sub, If, ChDir, Print, Time$, RecordsAffected, Recordset	Words in bold with initial letter capitalized indicate language-specific keywords with special meaning to VBA. Objects, methods, statements, functions, and properties appear in bold with the initial letter capitalized. Concatenated names may contain other capital letters.
expr, path	In text, italic letters are used for defined terms, usually the first time they occur in the guide. Italics are also used occasionally for emphasis. In syntax, italics indicate placeholders for information you supply.
[expressionlist]	In syntax, items in square brackets are optional.
{While\|Until}	In syntax, braces and a vertical bar indicate a choice between two or more items. You must choose one of the items unless all of the items are enclosed in square brackets.
Dim rstCust As Recordset
A monospace font indicates code.	Set rstCust = dbCurrent. _ OpenRecordset("Customers", _dbOpenDynaset)
If you are using Access Basic, Visual Basic 3.0, or VBA 3.0, the line-continuation character ( _ ) indicates that code continued from one line to the next in the guide should be typed as one line in the editor by omitting the line-continuation character and the preceding space.If you are using Visual Basic 4.0 or VBA 4.0 and later, you can continue a line of code in the editor by using the line-continuation character.	ALT+F1, ENTER Small capital letters are used for the names of keys and key sequences, such as ENTER and CTRL+R. A plus sign (+) between key names indicates a combination of keys.

Code Examples and Programming Style

This guide contains numerous code examples that you can use to experiment with the concepts covered. Unless otherwise noted, the code is compatible with VBA 5.0 as it is implemented in Visual Basic 5.0, Microsoft Access 97, Microsoft Excel 97, Microsoft PowerPoint 97, Microsoft Word 97, and other applications that host VBA 5.0.

This guide uses the following conventions in the sample code:

Keywords appear with an initial letter capitalized; concatenated words may contain other capital letters. Constants and variables appear in mixed case:
```
If rstCustomerList.RecordCount = 0 Then strText = "Empty" Public Const dbOpenRecordset = 2
```
In full procedures, all variables are declared locally or are listed with an initialization procedure that declares public variables. All code has been tested with the VBA Option Explicit statement to ensure that there are no undeclared variables.

An apostrophe (') introduces a comment:

' This is a comment. Dim intNew As Integer ' This is also a comment.

Control-flow blocks and statements in Sub and Function procedures are indented:

Sub MyCode () Dim intX As Integer, intP As Integer, intQ As Integer  If intX > 0 Then intP = intQ End If End Sub

Naming conventions are used to make it easier to identify objects and variables in the code examples. The following tables list some of the conventions.

Naming Conventions for Database Objects and Variables

Object	Prefix	Example
Container	con	conForms
Database	dbs	dbsCustomers
Document	doc	docReport
Dynaset	dyn	dynOrders
Error	err	errCurrent
Field	fld	fldLastName
Group	gru	gruUsers
Index	idx	idxPrimaryKey
Parameter	prm	prmBeginDate
Property	prp	prpValue
QueryDef	qdf	qdfShowCustomers
Recordset	rst	rstCustomers
Relation	rel	relOrdersItems
Snapshot	snp	snpReadOnly
Table	tbl	tblOrders
TableDef	tdf	tdfCustomers
User	usr	usrMary
Workspace	wrk	wrkPrimary

Naming Conventions for Visual Basic Variables

Data type	Prefix	Example
Byte	byt	bytAttributes
Currency	cur	curDollars
Date	dte	dteToday
Double	dbl	dblPi
Boolean	f	fInitialized
Integer	int	intCount
Long	lng	lngAttributes
Single	sng	sngDays
String	str	strLastName
Type (user-defined)	typ	typPartRecord
Variant	var	varTemp

See Also For more information about naming conventions, see "The Leszynski Naming Conventions for Microsoft Jet" Help file in the Papers folder on the companion CD-ROM.

Using the Companion CD-ROM

Inside the back cover of this guide you can find the companion CD-ROM for the Microsoft Jet Database Engine Programmer's Guide. The CD-ROM contains online versions of all the code examples in this guide, nine white papers giving additional information about the Microsoft Jet database engine, several important utilities, and the Microsoft Access 97 Upsizing Tools.

Using the Code Examples

This guide contains over 200 code examples that show how to correctly use the Microsoft Jet database engine. To help you learn about these features and incorporate them into your applications, the companion CD-ROM contains an electronic version of each code example used in the printed guide.

The code examples are stored in the JetBook\Samples subfolder on the companion CD-ROM. All of the code examples for Microsoft Access 97 are stored as modules in a Microsoft Access database named JetSamples.mdb. All of the code examples for Visual Basic 5.0 and other applications that support VBA 5.0 are stored as modules (.bas files) that are referenced in a Visual Basic project named JetSamples.vbp. Both JetSamples.mdb and JetSamples.vbp use tables and queries in NorthwindTables.mdb, also located in the JetBook\Samples subfolder. Additionally, some of the code examples for Chapter 8, "Accessing External Data," use sample data files in other formats, such as the Paradox sample data file, Author.db. These sample data files are located in subfolders in the JetBook\Samples folder and must be present in order for these code examples to work properly.

All of the code examples are designed to be executed from your hard disk. Use the Windows Explorer to copy the complete directory structure to the root directory of your C drive. Because files stored on a CD-ROM must have their read-only file attribute set, you may need to clear the read-only file attribute after copying the files. To make this process easier, you can run the function named RemoveReadOnlyAttr from the FileAttributes module in JetSamples.mdb, or from FileAttributes.bas. For more information about specific file names or additional requirements for the code examples in each chapter, see the introduction in that chapter.

To use the Visual Basic code modules referenced in JetSamples.vbp in Visual Basic 5.0, you can open JetSamples.vbp directly. To use these modules from a VBA 5.0 host application other than Microsoft Access 97, such as Microsoft Word 97, you must import the module(s) into the Visual Basic Editor. Additionally, you must set a reference to the Microsoft DAO 3.5 object library (see below).

Important Don't copy and paste code examples from JetSamples.mdb into Visual Basic 5.0 or other VBA 5.0 host applications. Many code examples in JetSamples.mdb include code that uses Microsoft Access-specific Visual Basic keywords and techniques that will fail if run from other applications. The modules referenced in JetSamples.vbp are designed to run in Visual Basic 5.0 and other VBA 5.0 host applications.

To import a .bas file into the Visual Basic Editor

Start the VBA 5.0 host application and open the document you want to work with.
Open the Visual Basic Editor. To do this in Microsoft Excel, Microsoft PowerPoint, or Microsoft Word, point to Macro on the Tools menu, and then click Visual Basic Editor. A different procedure may be required in other VBA 5.0 host applications. Check your product documentation for details.
On the File menu, click Import File.
In the Import File dialog box, navigate to the folder where you installed the code samples, select the module (.bas) you want to work with, and then click Open.
To view the code in the module, expand the Modules folder in the Project Explorer, and then double-click the module you imported.

To set a reference to the Microsoft DAO 3.5 Object Library

Start the VBA 5.0 host application, open the document you want to work with, and then open the Visual Basic Editor.
On the Tools menu, click References.
In the Available References box, select Microsoft DAO 3.5 Object Library, and then click OK.

Note The reference for the Microsoft DAO 3.5 object library is available only if you selected the Data Access Objects For Visual Basic option under the Data Access option when you ran the Setup program for Microsoft Access 97, Microsoft Excel 97, Microsoft Office 97, or Microsoft Office 97 Professional Edition. If the object library is not available, re-run the Setup program and select this option. The Data Access Objects For Visual Basic option is not available in the Setup program for the stand-alone versions of Microsoft PowerPoint 97 or Microsoft Word 97.

White Papers

Contractors and Microsoft staff have prepared a number of white papers that describe specific aspects of Microsoft Jet and the applications that use it. The following white papers pertaining to the Microsoft Jet database engine are included on the companion CD-ROM in the Papers folder (the file names are indicated in parentheses):

Collins, Kevin. "Understanding Microsoft Jet Locking." (JetLock.doc)
Collins, Kevin. "Microsoft Jet 3.5 Performance Overview and Optimization Techniques." (V35Perf.doc)
Collins, Kevin. "Microsoft Jet 3.0 Performance Overview." (V3Perf.doc)
Gilman, Joel. "Setting Connection String Parameters in DAO." (DAOConnectString.doc)
Kruglick, Emily and Tucker, Peter. "Data Access Objects (DAO) 3.5 and ODBCDirect." (ODBCDir.doc)
Leszynski, Stan. "The Leszynski Naming Conventions for Microsoft Jet." (LNC95Jet.hlp)
Poll, Tony. "Database Replication in Microsoft Jet." (Replication Manager Whitepaper.doc)
Roberts, Mark. "Accessing Microsoft Exchange and Microsoft Outlook Data Using Visual Basic." (Accessing Exchange And Outlook Data.doc)
Scott, Beth. "Accessing External Data with Microsoft Access." (Accessing External Data With Microsoft Access.doc)

Important The information contained in these white papers represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, the statements in these papers should not be interpreted as commitments on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

Utilities

The companion CD-ROM contains several helpful utilities for understanding database locking. The utilities in the Utilities folder on the companion CD-ROM are explained in the white paper, "Understanding Microsoft Jet Locking."

The Microsoft Access 97 Upsizing Utilities - Using the Companion CD-ROM

The Upsizing folder on the companion CD-ROM contains the Setup program for the Microsoft Access 97 Upsizing Tools. You can use the Microsoft Access 97 Upsizing Tools to re-create the structure of a Microsoft Access 97 database in Microsoft SQL Server. For more information about the Upsizing Tools, see "Converting a Microsoft Access Application Using the Upsizing Wizard" in Chapter 9, "Developing Client/Server Applications."

Wizards

Several wizards are included on the companion CD-ROM. Two wizards for linking and importing data from Microsoft Exchange and Microsoft Outlook are included in the ExchWizard folder. To install these wizards, along with the Microsoft Exchange ISAM driver, run the Wzmapi80.exe file in the folder.

Two wizards for use with replication are also included. The Partial Replica Wizard, which makes it easy to create partial replicas, is located in the PrtlReplWiz folder. The Replica to Regular Database Wizard, located in the ReplWizard folder, makes it easy to convert a Design Master to its original, nonreplicated state.

See Also For more information about using these wizards, see the Microsoft Access Developer Forum Web site at https://www.microsoft.com/accessdev/.

Using the Companion DAO Hierarchy Maps

Inside the back cover of this guide you can also find two maps (printed back to back) showing the hierarchy of DAO objects, properties, and methods. These maps are valuable learning tools and present the complete family of objects for each of the two kinds of Workspace objects supported by Microsoft Jet 3.5. You may find that posting the maps on a wall near your workstation provides a faster reference tool than using Help to look up the properties and methods available for a particular object.