Databases - Queries in Code

Other sections (will) discuss how to use the integrated query generator and how to use SQL. This page is concerned with how to run a query from within your code - such as when a button is pressed.

There are 2 basic types of queries

MS Access 97 | MS Access 2000/2002/XP | Delphi

MS Access 97

In code, Tables and query results are accessed via a recordset. You can then locate the First, Last, Next, Previous, etc. records. Individual fields are referenced via The following example creates a recordset based on the query string.

CurrentDB is a reference to the current database.

Because of the aggregate function Max(), the following example produces a recordset with only one record. Notice that in order to reference the result of an aggregate function, the result MUST be given a new identifier.

The following shows how to open a query without using a QueryDef. The following method also works.

Queries Which Do Not Return Recordsets

Create a new Table
  CurrentDB.Execute("select * into xx from yy"), dbFailOnEror

Append to an existing table
  CurrentDB.Execute("Insert Into xx select * from yy"), dbFailOnEror

MS Access 2000/2002/XP

MS Access 2000 and MS Access 2002 (part of MS Office XP pro) do not allow the MS Access 97 code to work. Specifically, CurrentDb and some of the other commands won't work. Instead, you need something like this. This syntax works for queries that don't return a record set.

Delphi 5.0

The syntax depends on which of several different sets of components you use to make the connection.

Author: Robert Clemenzi -
URL: http:// / user / clemenzi / technical / Databases / QueriesInCode.html