Querying external databases

You can query external databases to access information in an ODBC data source and database files created using Paradox, Reflex, or dBASE (versions II, III, and IV) applications. Querying external databases lets you extract information from the database without first having to translate the file.

When you query external databases, you must create a criteria table specifying the type of information for which you are searching and output cells to hold the results of the search. For information about criteria tables and output cells, see “Preparing queries for searching notebook databases.”

You can also Query By Example (QBE), which, in database management systems, refers to a method of forming queries in which the database program displays a blank record with a space for each field. You can enter conditions for each field to be included in the query. For example, to find all records where the age field is greater than 65, you would type >65 in the age field.

Once you create a .qbe file, you can run the QBE query from Quattro Pro. A QBE query in a notebook selection always starts with the word Query and ends with the word EndQuery. The best way to learn the QBE syntax is to create queries in Paradox, import them into Quattro Pro, and then experiment by revising them.

An alias is a shortcut name that takes the place of a long path name. When using external database tables, it is convenient to assign aliases for your private and working directories. A private directory is a location for your temporary answer tables and other files that are not shared. A working directory is where a database application or a feature looks for external database tables.

To query external databases

Click Tools

Data tools

Notebook query.

In the Database cells box, type the path of the external database file using linking syntax.

You do not need to type the path if the database path is already in your working directory.

Select the criteria table using the Range picker

Select the row of the output cells which contains the field names using the Range picker

Click Extract.

•

The format of linking syntax is [Database]A1..A2, where [Database] is the name of the database, including the path; for example, C:\PARADOX\SAMPLE.DB. The cells specified after the filename can be any valid area with at least two rows. A1..A2 acts as a hook into the database. It remains A1..A2, no matter the size of the database area.

•

If your criteria contain formulas with cell references to data elsewhere, those cell references must be absolute. For example, for a table with Name and Age as column titles, you could type +~age>25 to identify all entries where age>25. To reference the cell C1 that contained the value 25, typing +AGE>C1 would not work; instead, you would need to type +~AGE>$A:$C$1 as the criteria, using absolute references.

To use Query By Example

Click Insert

External data

Table query.

Enable one of the following options:

•

Query in file — indicates that the query is located in a .qbe file

•

Query in selection — indicates that the query is located in a notebook selection

Type the location of the query in the Query file box.

Select the destination for the query results in the Destination box using the Range picker

•

The QBE query and the database table must be in your working directory.

•

You can make a query selection easy to read by setting the font for the QBE selection to a nonproportional font such as Courier.

To assign aliases for private and working directories

Click Insert

External data

Aliases.

Type the path for your private directory in the Private directory box.

Type the path for your working directory in the Working directory box.

•

You can also click Browse

to insert a path.

Wordperfect Quattro Pro