SQL Queries

This section describes the syntax of the SQL queries accepted by the OpenRecordset method of the Recordset class. These queries select and/or sort records from a table matching a certain number of criteria.

It is not possible to run queries on several tables at the same time. If you want to implement operations that join several tables, you must write a loop to step through all the records in the tables and carry out manual comparisons.

The general syntax for a query is as follows:

Condition1 [ { AND | OR | XOR } Condition2 [... ]] [ ORDER BY Sort1 { ASC | DESC } [, Sort2 { ASC | DESC } ]... ]

The Condition1, Condition2, parameters etc. allow the selection of records to be included in the recordset. Each condition can take one of two forms:

Field1 { = | <> | < | <= | > | >= | Like } Constant
Field1 { = | <> | < | <= | > | >= } Field2

The string constant must be placed between simple quotes. It is not possible to use expressions inside a condition. The types of data must be compatible: it is not possible to compare a Boolean fields with an Integer field, for example. It is not possible to carry out comparison on fields of the type Bitmap and StreamMemory.

For example, if your project contains a table clsBooks with the fields Title, Author , Date and Pages , the following queries are valid:

Dim tbl As New tblBooks
tbl.OpenRecordset "Author = 'Salinger'"
tbl.OpenRecordset "Author = 'Kerouac' ORDER BY Title ASC"

A major pitfall is related to dates. Dates are always stored in a database using the build-in Date type while they have to be specified as strings in SQL queries. The OpenRecordset method internally calls the CDate function to convert dates in queries into a value suitable for record filtering, but this is an issue because CDate relies on user preference and localisation. For example, the following query will return books published before 1st june 2000 on US systems but before 6th january 2000 of french systems:

tbl.OpenRecordset "Date < '06/01/2000'" ' WRONG !

To avoid this problem, you have to specify dates using the unambiguous ISO 8601 format. For example:

tbl.OpenRecordset "Date < '2000-06-01T00:00:00'" ' OK

If necessary, you can convert a Date value to a properly formatted string using the Format function. For example, provided d is a variable containing a date:

tbl.OpenRecordset "Date < '" & Format(d,"iso") & "'"

The Like operator applies only to character strings, and allows to perform simplified pattern matching in a field. The right hand side operand must be a literal character string which specify the pattern to search. The following table illustrates the pattern syntax:

Pattern	Description

*	Matches any string.
abc	Matches only the exact string 'abc'
abc*	Matches any string beginning with 'abc'
*abc	Matches any string ending with 'abc'
abc	Matches any string containing 'abc'

For example, the following query will return all the books whose title starts with letter A, sorted alphabetically on the name of the author:

tbl.OpenRecordset "Title Like 'A*' ORDER BY Author ASC"

For technical reasons related to the structure of the databases in memory, accessing the built-in UniqueID, Category, Dirty and Secret fields is notably faster than accessing other fields. In the same way, accessing fields of fixed size (Byte, Integer, etc...) is faster than accessing fields of variable size (String). Consequently it is preferable to structure your application so that the most frequent selection operations are performed on built-in or fixed-size fields.

The optional parameters Sort1, Sort2 , etc... specify the name of the fields on which to carry out the sorting of the records in the recordset. Keywords ASC and DESC make it possible to specify, for each field, if the sorting should be ascending or descending. It is not possible to specify a clause ORDER BY without any condition. If you wish to open a sorted recordset containing all the records of the database, try to specify a condition that is always true, for example:

tbl.OpenRecordset "Category>=0 ORDER BY Title ASC"

A sorted recordset can contain only 16384 records, whilst a non sorted recordset can contain up to 65534 records. In addition, opening a sorted recordset can take a lot longer than opening a non sorted recordset, especially if the sort is on String fields.