This chapter discusses how to create, delete, and maintain tables, indexes, queries, relationships, and referential integrity rules with the Microsoft Jet database engine.
As discussed in Chapter 2, “Introducing Data Access Objects,” Microsoft Jet tables, queries, and other objects are exposed to programming languages through a hierarchical system of objects and collections of objects referred to as Data Access Objects (DAO). For example, a Database object contains a collection of TableDef objects, which further contain collections of Field objects, which in turn contain collections of Property objects, and so on. The DAO object model maps those objects onto a programming interface you can control by using Microsoft Visual Basic, Visual Basic for Applications, Microsoft Visual C++, or Microsoft Visual J++.
You can create tables and define relationships between tables entirely through the native DAO interface. Using DAO gives you a great deal of flexibility and control over how your database objects are created. With DAO, you write code to create a new object, assign values to that object’s properties to specify a data type, length, validation rule, and so on, and then append that object to a collection within the database’s object hierarchy.
Many people are familiar with the industry-standard Structured Query Language (SQL) and its data definition language (DDL) subset. Microsoft Jet supports an SQL dialect that is compliant with the SQL ANSI-89 standard, with a few enhancements. Many of the features of Microsoft Jet that can be manipulated through DAO can also be controlled with SQL DDL statements. This chapter shows both methods and explains the differences.
Creating and Modifying Table Indexes
Microsoft Jet QueryDef Objects
Referential Integrity and Relationships
You can use the code examples in this chapter to help you understand the concepts discussed, or you can modify them and use them in your own applications.
The code examples are located in the JetBook\Samples subfolder on the companion CD-ROM. The code examples for Microsoft Access 97 are in JetSamples.mdb, and the corresponding code examples for Microsoft Visual Basic version 5.0 and other applications that support Visual Basic for Applications are referenced in JetSamples.vbp. Both JetSamples.mdb and JetSamples.vbp use tables and queries in NorthwindTables.mdb, also located in the JetBook\Samples subfolder.
To use the code examples, copy the sample files to your hard disk. Be sure to copy NorthwindTables.mdb as well so that you can use the code examples to work with data.
See Also For more information about copying and using the code examples from the companion CD-ROM, see “Using the Companion CD-ROM” in the Preface.