Sets or returns the SQL statement that defines the query executed by a
QueryDef object.
Syntax
expression .SQL
expression A variable that represents a QueryDef object.
Remarks
The
SQL property contains the SQL statement that determines how records are selected, grouped, and ordered when you execute the query. You can use the query to select records to include in a
Recordset object. You can also define action queries to modify data without returning records.
The SQL syntax used in a query must conform to the SQL dialect of the query engine, which is determined by the type of workspace. In a Microsoft Access workspace, use the Microsoft Access SQL dialect, unless you create an SQL pass-through query, in which case you should use the dialect of the server.
If the SQL statement includes parameters for the query, you must set these before execution. Until you reset the parameters, the same parameter values are applied each time you execute the query.
In a Microsoft Access workspace, using a
QueryDef object is the preferred way to perform SQL pass-through operations on Microsoft Access database engine-connected ODBC data sources. By setting the
QueryDef object's
Connect property to an ODBC data source, you can use non–Microsoft–Access–database SQL in the query to be passed to the external server. For example, you can use TRANSACT SQL statements (with Microsoft SQL Server or Sybase SQL Server databases), which the Microsoft Access database engine would otherwise not process.
Note
If you set the property to a string concatenated with a non-integer value, and the system parameters specify a non-U.S. decimal character such as a comma (for example, strSQL = "PRICE > " & lngPrice, and lngPrice = 125,50), an error will result when you try to execute the QueryDef object in a Microsoft Access database engine database. This is because during concatenation, the number will be converted to a string using your system's default decimal character, and Microsoft Access SQL only accepts U.S. decimal characters.
|
Example
This example demonstrates the SQL property by setting and changing the SQL property of a temporary QueryDef and comparing the results. The SQLOutput function is required for this procedure to run.
Sub SQLX()
Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim rstEmployees As Recordset
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set qdfTemp = dbsNorthwind.CreateQueryDef("")
' Open Recordset using temporary QueryDef object and
' print report.
SQLOutput "SELECT * FROM Employees " & _
"WHERE Country = 'USA' " & _
"ORDER BY LastName", qdfTemp
' Open Recordset using temporary QueryDef object and
' print report.
SQLOutput "SELECT * FROM Employees " & _
"WHERE Country = 'UK' " & _
"ORDER BY LastName", qdfTemp
dbsNorthwind.Close
End Sub
Function SQLOutput(strSQL As String, qdfTemp As QueryDef)
Dim rstEmployees As Recordset
' Set SQL property of temporary QueryDef object and open
' a Recordset.
qdfTemp.SQL = strSQL
Set rstEmployees = qdfTemp.OpenRecordset
Debug.Print strSQL
With rstEmployees
' Enumerate Recordset.
Do While Not .EOF
Debug.Print " " & !FirstName & " " & _
!LastName & ", " & !Country
.MoveNext
Loop
.Close
End With
End Function