Retrieving data from an external database

Querying a table requires ODBCConnection, ODBCQuery, and ODBCResultSet objects. You must explicitly associate the objects by setting the Connection property of ODBCQuery and the Query property of ODBCResultSet as follows:

  • queryObject.Connection = connectionObject associates a connection with a query.
  • resultSetObject.Query = queryObject associates a query with a result set.

If you don't need the ODBCQuery object, you can just set the Connection property of ODBCResultSet as follows:

  • resultSetObject.Connection = connectionObject associates a connection with a result set.

Specifying SQL statements

The SQL property of ODBCQuery specifies the SQL statement that your query executes. The SQL property can be any valid SQL syntax, including SELECT, CREATE TABLE, DROP TABLE, INSERT, UPDATE, and DELETE statements. For example, the following SQL statement selects all data in the STUDENTS table:

"SELECT * FROM STUDENTS"

This SQL statement selects all data in the STUDENTS table in ascending sequence by the LASTNAME field:

"SELECT * FROM STUDENTS ORDER BY LASTNAME"

This SQL statement selects only the LASTNAME and FIRSTNAME fields:

"SELECT LASTNAME, FIRSTNAME FROM STUDENTS ORDER BY LASTNAME"

Parameters

An SQL query can include parameters. The following query contains one parameter named studentNo:

qry.SQL = "SELECT * FROM STUDENTS WHERE STUDENT_NO = ?studentNo?"

You must assign the parameter a value before executing the query. The SetParameter method assigns a value to a parameter, given the name or number (starting at 1) of the parameter and the value:

Dim inputNo As String
inputNo = Inputbox$("Enter ID number", "Student ID?")
Call result.SetParameter("studentNo", inputNo)
REM Alternatively, the following could be used:
REM Call result.SetParameter(1, inputNo)

If the value of the parameter must be enclosed in single quotes, which is the case with a character value, supply the single quotes in the SetParameter method; not in the SQL statement.

Dim inputName as String
qry.SQL = "SELECT * FROM STUDENTS WHERE LASTNAME = _
?lastName?"
inputName = Inputbox$("Enter last name", _
"Student last name?")
Call result.SetParameter("studentName", "'" _
& inputName & "'")

The GetParameter method gets the current value of a parameter and the GetParameterName method gets the name of a parameter, given the name or number of the parameter. The NumParameters method returns the number of parameters in the current SQL statement, which enables you to use For loops to process parameters. The following code sets all the parameters in the current SQL statement using the parameter names to prompt (in this example, all the parameters require single quotes):

For i = 1 To result.NumParameters
  inputParameter = _
  Inputbox$(result.GetParameterName(i), _
  "Parameter " & i)
  Call result.SetParameter(i, "'" & inputParameter & "'")
Next

Executing SQL statements

The Execute and ExecProcedure methods of the ODBCResultSet object perform your query and retrieve the selected data. You can limit the attempt time for the query by setting the QueryExecuteTimeOut property of ODBCQuery.

The selected data becomes available as a result set that contains one row for every row selected from the table, and one column for every field selected from the table. To access the data, use the ODBCResultSet object.

Use the IsResultSetAvailable method of the ODBCResultSet objec to determine whether a query returned any data.

When you no longer need a result set, close it with the Close method of ODBCResultSet.