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

Action Queries - Append, Delete, Update, Create ...
These do not return records
Select Queries - Select * from xx;
These return a result set. Use First, Last, Next, Previous, etc. to access specific records.

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

Recordset_X!FieldName
Recordset_x![FieldName]

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.

Dim SQL_String As String
Dim Temp_dbs   As Database
Dim Temp_qdf   As QueryDef
Dim Temp_rst   As Recordset

SQL_String   = "Select Max([SomeField]) as SomeName from SomeTable;"
Set Temp_dbs = CurrentDb
Set Temp_qdf = Temp_dbs.CreateQueryDef("", SQL_String)
Set Temp_rst = Temp_qdf.OpenRecordset(dbOpenSnapshot)  

xx_UIField.SetFocus  ' This is required before writing to a UI object
xx_UIField.Text = Temp_rst![SomeName] + 5

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

Set dbs = CurrentDb
SQL_Str = "SELECT * FROM Orders WHERE [StateField] = 'VA'"
Set Some_Recordset = dbs.OpenRecordset(SQL_Str)

The following method also works.

Set Some_Recordset = CurrentDb.OpenRecordset( _
     "SELECT * FROM Orders WHERE [StateField] = 'VA'")

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.

Dim cnn As ADODB.Connection
Dim rst1 As New ADODB.Recordset

 Set cnn = CurrentProject.Connection
 Query = "Select * from Alias_Details_tbl where Purpose='" & Alias_UICombo & "'"
 rst1.Open Query, cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

 Do Until rst1.EOF
 
    ' Place your code here
   str1 = rst1!FieldName
 Loop

 rst1.Close

This syntax works for queries that don't return a record set.

DoCmd.RunSQL "Delete * from Aliases_tbl"

Delphi 5.0

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

General queries for components based on TDataSet
Ka Dao
Interbase/Firebird

Author: Robert Clemenzi - clemenzi@cpcug.org