Lesson 4: Creating a SQL Pass-Through Query

Microsoft Access allows you to create queries using SQL statements. Usually when you create a query, Access processes the SQL statement itself. When you create a pass-through query, Access passes the SQL statement through to the ODBC Driver unchanged. From there, the ODBC Driver passes the query to the Pervasive ODBC Interface, which translates the ODBC request into a Pervasive engine request.

This lesson creates a simple query that displays data from the Course table in the Pervasive sample database.

To create a SQL pass-through query:

In the Demodata:Database window, go to the Queries tab and click New.

In the New Query dialog box, highlight Design View and click OK.

In the Show Table dialog box, click Close.

By default, Access expects you to choose an existing table or query on which to build the new query. However, because you are creating a SQL Pass Through query, you do not need to use this dialog box at all. Click Close.

In the Access main menu, choose Query > SQL Specific > Pass Through.

In the Access main menu, choose View > Properties.

In the Query Properties dialog box, add DSN=Demodata to the ODBC Connect Str field and close the dialog box.

This identifies the Demodata data source you created in Lesson 1. By setting the properties for this query, you have associated this query with the Demodata data source. If you had not set the properties, Access would prompt you for a data source name every time you ran the query.

In the SQL Pass-Through Query window, enter the following SQL statement:

SELECT * FROM Course 
 
Note that Access does not properly handle the typical Pervasive.SQL V8 statement separator characters at the end of the statement. These characters are semicolon (;), pound sign (#), backslash (\), and grave (`). Using any of these as a statement separator results in Status Code 501.

 
Note also that the SQL statements you create must adhere to Pervasive.SQL V8 syntax requirements. You can find basic syntax descriptions in the SQL Reference help file, SqlRef.hlp.

In the Access main menu, choose Run from the Query menu. Access returns the results of the query.

Close the SQL Pass-Through Query window and save the Query as CourseGrid.

You can use SQL Pass-Through Queries to accomplish some tasks that cannot be accomplished through the Table Design view in Access. For example, you can use a SQL Pass-Through Query to add a column to a table.

The following sections provide some example SQL statements. You can find more examples and guidance on formulating your own SQL statements in the SQL Language Reference. Note that if you use a cut-and-paste method to copy example SQL statements into the SQL Pass-Through Query window, you may need to retype some special characters (such as quote marks) to avoid receiving a syntax error.

Note also that if you allow Access to build a SQL statement for you (such as when building a query in Design View), the resulting statement conforms to Jet SQL syntax, not Pervasive.SQL V8 syntax. Consequently, an Access-generated SQL statement may receive a Pervasive.SQL V8 syntax error. This happens because, by default, Access assumes that you will use the Jet engine to process your SQL statement. However, when you use a SQL Pass-Through Query, the statement bypasses the Jet engine and goes to the Pervasive.SQL V8 engine untouched.

Finally, note that the following statements do not return records. If you are using the default query properties, you will receive the following warning message:

The message does not affect your results in any way, but for convenience, you can avoid receiving this message. Open the query in design view, then click the Properties button on the toolbar. Set the Returns Records field of the query properties to No, as shown:

ALTER TABLE Statement

The following statement adds a column to the Department table for storing an emergency contact telephone number.

ALTER TABLE Department ADD Emergency_Phone CHAR(20)

To see this update to the Department table through Access, you must relink to it.

INSERT Statement

The following statement inserts a row into the Course table.

INSERT INTO Course(Name, Description, Credit_Hours, 
Dept_Name)
VALUES (`CHE 308', `Organic Chemistry II', 4, 
'Chemistry')

UPDATE Statement

The following statement changes the credit hours for ECO 305 in the Course table from 3 to 4.

UPDATE Course SET Credit_Hours = 4 WHERE Name = `ECO 305'