Microsoft Office 2000/Visual Basic Programmer's Guide

Running a Temporary Query

ADO provides a variety of ways to run a query without using a stored query. Queries of all types are specified by using the Structured Query Language (SQL). Queries can be of two basic types: queries that return a set of records, and queries that don't return a set records. Queries that don't return a set of records can be further divided into two types: Data Definition Language (DDL) queries, which create database objects or alter the structure of a database, and Data Manipulation Language (DML) queries, which perform bulk operations on a set of records, such as adding, updating, or deleting a set of records.

For information about how to run a temporary query that returns records, see "Working with Records" later in this chapter.

To run a temporary query that doesn't return records, such as DDL or DML SQL statements, you can use the Execute method of either the ADO Command object or the ADO Connection object. The primary differences between these two methods are as follows:

• To use a Command object to execute a query, you set the CommandText property of the object to the SQL statement you want to run, and then set the ActiveConnection property of the object to a connection string or an open Connection object that specifies the data source you want to run against. Once this is done, to run the command you use the Execute method. If you want to run the same command against a different data source, you can set the Command object's ActiveConnection property to Nothing, and then reset the ActiveConnection property to a new data source.
  
  
• To use a Connection object to execute a query, you open the Connection object, and then run the Execute method by passing in the SQL statement you want to run as the CommandText argument. As long as you don't close the connection, you can use the Execute method again to run additional SQL statements against the connection.

Also, for both methods, the CommandText property or the CommandText argument can refer to the name of a stored query, a stored procedure, or a table. For information about how to do that, see ADO Help.

The following procedure shows how to use the Command object to run an SQL statement.

Sub RunSQLCommand(strDBPath As String, _
                  strSQL As String)
   Dim cmd        As ADODB.Command
   Dim strConnect As String

   strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath
   
   Set cmd = New ADODB.Command
   With cmd
      .CommandText = strSQL
      .ActiveConnection = strConnect
      .Execute
   End With

   Set cmd = Nothing
End Sub

For example, the new IDENTITY command in Jet SQL allows you to specify a starting value and an increment value when creating an AutoNumber field (which you can't do while using the Access 2000 user interface). You could use the following line of code with the RunSQLCommand procedure to create a table with an AutoNumber field that starts with a value of 10 for the first record and increments 5 for each new record:

RunSQLCommand "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
   "CREATE TABLE MyTable(MyCounter IDENTITY (10, 5), FirstName CHAR, LastName CHAR)"

The RunSQLCommand procedure can be found in the CreateDatabase module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM. The CreateDatabase module also contains the RunSQLConnection procedure that demonstrates how to execute an SQL statement by using the Execute method of a Connection object.

For more information about the new Jet SQL commands, see the "Overview of What's New" topic in the Jetsql40.chm Help file. By default, this Help file is installed in the C:\Program Files\Microsoft Office\Office\1033 folder.