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 An Overview of Microsoft Jet
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 ODBC 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.