Creating Modifying and Deleting Tables and Fields

Organize with Office 365 Course

Organize With Office 365

Get Instant Access

In Part II of this book, you learned about the ADO Object Model and tried out different methods that are available in Microsoft Office Access 2003 for creating and manipulating databases via programming code. In particular, you've learned how to create new databases from scratch, add tables and indexes, set up relationships between tables, secure a database with a password, define user and group security accounts, and handle object permissions. In addition to ADO, you can perform many of the mentioned database tasks by using the Data Definition Language (DDL), which is a component of the Structured Query Language (SQL).

SQL is a widely used language for data retrieval and manipulation in databases. The SQL specification (known as ANSI SQL-89) was first published in 1989 by the American National Standards Institute (ANSI). The ANSI SQL standard was revised in 1992 and this version is referred to as ANSI SQL-92 or SQL-2. This revised specification is supported by the major database vendors, many of whom have created their own extensions of the SQL language.

Microsoft Office Access 2003 supports both SQL specifications and refers to them as ANSI-SQL query modes. While the ANSI-89 SQL query mode (also called Microsoft Jet SQL and ANSI SQL) uses the traditional Jet SQL syntax, the ANSI-92 SQL mode uses syntax that is more compliant with SQL-92 and Microsoft SQL Server. For example, ANSI-92 uses the percent sign (%) and the underscore character (_) for its wildcards instead of the asterisk (*) and the question mark (?), which are commonly used in VBA. Microsoft Access Jet Engine does not implement the complete ANSI SQL-92 standard and provides its own Jet 4.0 ANSI SQL-92 extensions to support new features of Access. For more information about syntax differences see the topic "Comparison of Microsoft Jet SQL and ANSI SQL" in the Microsoft Office Access built-in help.

You can use the ANSI-92 syntax in your VBA procedures with the Microsoft OLE DB Provider for Jet or with the Data Definition Language, which we cover in this part of the book. ANSI-89 is the default setting for a new Microsoft Access database in Access 2002-2003 and 2000 file format.

Part III

Because the two ANSI SQL query modes are not compatible, you must decide which query mode you are going to use for the current database. This can easily be done in the Microsoft Access user interface as outlined in Hands-On 19-1.

© Hands-On 19-1: Setting the ANSI SQL Query Mode

1. Create a new Microsoft Access database named Test of Query Mode.mdb.

2. In the Database window, choose Tools | Options, and click the Tables/Queries tab (Figure 19-1).

3. In the SQL Server Compatible Syntax (ANSI 92) area, set the query mode to ANSI-92 SQL by clicking the This database check box. (You can set the query mode to ANSI-89 SQL by clearing the This database check box.)

4. Click the Apply button.

5. Click OK to exit the Options window. Microsoft Access displays a message as shown in Figure 19-2.

6. Click the Help button to read more information about the ANSI SQL query mode.

7. Return to the message and click OK. Microsoft Access database will close and reopen with the new settings in effect.

8. Close the Test of Query Mode database file.

Figure 19-1: Use the Options window to set the ANSI SQL query mode for the current database or all new databases.

Figure 19-1: Use the Options window to set the ANSI SQL query mode for the current database or all new databases.

Programming with the Jet Data Definition Language

Programming with the Jet Data Definition Language

Figure 19-2: When you change the query mode to ANSI-92, Microsoft Access displays an informational message alerting you to possible problems.

There are two areas of Microsoft Access Jet 4.0 Engine's version of SQL: Data Definition Language (DDL) offers a number of SQL statements to manage database security and to create and alter database components (such as tables, indexes, relationships, views, and stored procedures). These statements are: CREATE TABLE, DROP TABLE, ALTER TABLE, CREATE INDEX, DROP INDEX, CHECK CONSTRAINT, CREATE VIEW, DROP VIEW, CREATE PROCEDURE, DROP PROCEDURE, EXECUTE, ADD USER, ALTER DATABASE, ALTER USER, CREATE GROUP, CREATE USER, DROP GROUP, DROP USER, GRANT, and REVOKE.

Data Manipulation Language (DML) offers SQL statements that allow you to retrieve and manipulate data contained in the database tables as well as perform transactions. These statements are: SELECT, UNION, UPDATE, DELETE, INSERT INTO, SELECT INTO, TRANSFORM, PARAMETER, BEGIN TRANSACTION, COMMIT, and ROLLBACK.

This chapter and the remaining chapters of Part III focus on using the DDL language for creating and changing the underlying structure of a database. To get the most out of these chapters, you should be familiar with using ADO, which was discussed in Part II.

Was this article helpful?

0 0

Post a comment