Share this Page URL

Chapter 9. Extending Your Use of Queries > Creating SQL-Specific Queries - Pg. 325

Extending Your Use of Queries 325 Remember that the AS keyword creates an alias for a field, so the query in this example uses Name as a first field in both tables. If you use the ORDER BY clause to sort the records in a union query, put it at the end of the query. If you're sorting by a field that is named differently in the tables you're uniting, sort by the field name from the table listed first. SQL Pass-Through Query You use a pass-through query when you need to connect to a different data source and work directly with the data it stores. A pass-through query acts on data sources that are compatible with Open Data-base Connectivity (ODBC), a widely used data access protocol. In most cases, you can import or link to the external data you need. The SQL statements you use to create a pass-through query depend on the data source you connect to. You specify the connection information as a property of the query or supply that information each time the query is run. To create a pass-through query, start a new query in Design view. On the Query menu, click SQL- Specific, Pass-Through. After entering the SQL statements that define the query, right-click the title bar of the SQL Pass-Through Query window, and click Properties. Use the ODBC Connect Str property to set up the connection string to the external database. Data-Definition Queries A data-definition query lets you create or modify objects in a database by using SQL statements. These queries contain data-definition language (DDL) statements such as CREATE TABLE, ALTER TABLE, and so on. You're more likely to use DDL statements in Visual Basic, when you work with database objects in your code. However, you can use DAO or ADO in Visual Basic to do much of what a data-definition query can do, or you can perform the steps yourself in Access. Here are a few examples of data-definition queries. The first example creates a table named Sup- pliers. The table has four fields (SupplierID, Name, Address, and City). CREATE TABLE Suppliers (SupplierID Integer, Name Text(50) , Address Text(50), City Text(50), CON STRAINT Index1 PRIMARY KEY (SupplierID)); Notice that in the DDL statement, you indicate the data type for the field. You can also indicate the field size, as we've done for the three text fields. The CONSTRAINT keyword designates SupplierID as the table's primary key. To add a field to the Suppliers table we just created, you would use a statement such as the following: ALTER TABLE Suppliers ADD Country Text(50); With the ALTER TABLE statement, you can also delete a field, change a field's data type or size, or add or delete a constraint. The DROP clause lets you delete a field or an index from a table. To delete a field, you would use a statement such as this: ALTER TABLE Suppliers DROP Country; Finally, the CREATE INDEX statement lets you create an index for a table. CREATE INDEX has several variations. In its simplest form, you can use a statement such as this: CREATE INDEX SupplierName ON Suppliers (Name); To add more than one field to this index, separate the field names with a comma. You can add UNIQUE to CREATE INDEX (as in CREATE UNIQUE INDEX) to create an index that doesn't allow duplicate values. You can also use a WITH clause to exclude Null values in a field. The following statement would create an index and require that a value be entered in the field: