Introduction This series of articles explained what is Microsoft® Jet SQL and explains how it is used in Access 2000 applications. This article is the second article in the foundation, intermediate and advanced. These articles step-by-step illustrate the syntax and some methods of using Jet SQL, and demonstrates the features of Jet SQL for Access 2000. Finally, all SQL references in this article are used for Microsoft Jet 4.0 data engines. Intermediate SQL illustrates some of the concepts of intermediate structure query languages, users can enhance the structure of the structure and objects of the database, and manipulate data contained in these mechanisms through many interesting and powerful ways. Combined with data access methods such as DAO and ADO, intermediate SQL can greatly improve the flexibility of user applications and their performance. Basic, intermediate and advanced SQL's different points in the foundation, intermediate and advanced SQL is not an easy thing. In many cases, it is just a simple decision. However, for this series of SQL articles in Access 2000, some aspects are considered: First, it is a complex level of the SQL statement itself. In the previous article, we strive to use those commonly used statements and are the simplest form. This article introduces more complex statements on the basis of the previous basic articles. Second is some new SQL statements, clauses, and keywords already included in Access 2000. Although the SQL statement included in the ACCESS will still be used herein, some SQL statements that appear in Access will appear, and in advance in advanced SQL. Finally, those SQL statements about security and multi-user solutions will be described in advanced articles, because these statements are often used in more complex applications by using intercess SQL, users can add more for your Access application more Strong flexibility and ability. Although simple and direct SQL statements can complete a lot of things, using more complex statements will extend to access and process information in the database. Using intermediate SQL will also make users better control your database usage and maintenance work enhancement SQL in Access 2000, in order to support Access's new features, in order to support ANSI-92, and allow in Access and Microsoft® Better compatibility between SQL ServerTM is included in SQL in the Microsoft Jet 4.0 data engine. The Jet Data Engine now has two format SQL syntax: one is support for the previously used SQL syntax, and the other is to support new SQL syntax specifications. It is important to note that the new syntax is only available when the user uses ActiveX® Data Objects (ADO) and Jet Ole DB Provider, and the current syntax currently passing through the Access SQL View or DAO. This article indicates that a particular SQL command can only be used through Jet OLE DB Provider and ADO. In the previous version of Access, the data access object (DAO) is the primary data access method. Now there is a change, although DAO is still supported, the new data access method is to use ADO. ADO is part of Microsoft's General Data Access Strategy, and its most basic assumption is that the data can be accessed regardless of the database, whether it is a database, directory structure, or a user-defined database. When discussing Microsoft Jet SQL, ADO is very important, as mentioned earlier, some new SQL statements are only available when using ADO and Jet Ole DB Provider. In the demonstration databases in this article and with this paper, all the code is useful to use ADO.
