Syntax

SQLRequest(connection$,query$,array [,[output$] [,[prompt][,isColumnNames]]])

Description

Opens a connection, runs a query, and returns the results as an array.

Comments

The SQLRequest function takes the following parameters:

Parameter

Description

connection

String specifying the connection information required to connect to the data source.

query

String specifying the query to execute. The syntax of this string must strictly follow the syntax of the ODBC driver.

array

Array of variants to be filled with the results of the query.

The array parameter must be dynamic: it will be resized to hold the exact number of records and fields.

output

Optional String to receive the completed connection string as returned by the driver.

prompt

Optional Integer specifying the behavior of the driver's dialog box.

isColumnNames

Optional Boolean specifying whether the column names are returned as the first row of results. The default is False.

The Basic Control Engine generates a runtime error if SQLRequest fails. Additional error information can then be retrieved using the SQLError function.

The SQLRequest function performs one of the following actions, depending on the type of query being performed:

Type of Query

Action

SELECT

The SQLRequest function fills array with the results of the query, returning a Long containing the number of results placed in the array. The array is filled as follows (assuming an x by y query):

  (record 1,field 1)
  (record 1,field 2)
    :
  (record 1,field y)
  (record 2,field 1)
  (record 2,field 2)
    :
  (record 2,field y)
    :
    :
  (record x,field 1)
  (record x,field 2)
    :
  (record x,field y)

INSERT, DELETE, UPDATE

The SQLRequest function erases array and returns a Long containing the number of affected rows.

Example

This example opens a data source, runs a select query on it, and then displays all the data found in the result set.

Sub Main()
  Dim a() As Variant
  l& = SQLRequest("dsn=SAMPLE;","Select * From c:\sample.dbf",a,,3,True)
  For x = 0 To Ubound(a)
    For y = 0 To l - 1
      MsgBox a(x,y)
    Next y
  Next x
End Sub

S