QueryDef.SQL Property

Access Developer Reference

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.

Bb208681.vs_note(en-us,office.12).gif 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.

Visual Basic for Applications
  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

This example uses the CopyQueryDef method to create a copy of a QueryDef from an existing Recordset and modifies the copy by adding a clause to the SQL property. When you create a permanent QueryDef, spaces, semicolons, or linefeeds may be added to the SQL property; these extra characters must be stripped before any new clauses can be attached to the SQL statement.

Visual Basic for Applications
  Function CopyQueryNew(rstTemp As Recordset, _
   strAdd As String) As QueryDef

   Dim strSQL As String
   Dim strRightSQL As String

   Set CopyQueryNew = rstTemp.CopyQueryDef
   With CopyQueryNew
      ' Strip extra characters.
      strSQL = .SQL
      strRightSQL = Right(strSQL, 1)
      Do While strRightSQL = " " Or strRightSQL = ";" Or _
            strRightSQL = Chr(10) Or strRightSQL = vbCr
         strSQL = Left(strSQL, Len(strSQL) - 1)
         strRightSQL = Right(strSQL, 1)
      Loop
      .SQL = strSQL & strAdd
   End With

End Function

This example shows a possible use of CopyQueryNew().

Sub CopyQueryDefX()

   Dim dbsNorthwind As Database
   Dim qdfEmployees As QueryDef
   Dim rstEmployees As Recordset
   Dim intCommand As Integer
   Dim strOrderBy As String
   Dim qdfCopy As QueryDef
   Dim rstCopy As Recordset

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set qdfEmployees = dbsNorthwind.CreateQueryDef( _
      "NewQueryDef", "SELECT FirstName, LastName, " & _
      "BirthDate FROM Employees")
   Set rstEmployees = qdfEmployees.OpenRecordset( _
      dbOpenForwardOnly)

   Do While True
      intCommand = Val(InputBox( _
         "Choose field on which to order a new " & _
         "Recordset:" & vbCr & "1 - FirstName" & vbCr & _
         "2 - LastName" & vbCr & "3 - BirthDate" & vbCr & _
         "[Cancel - exit]"))
      Select Case intCommand
         Case 1
            strOrderBy = " ORDER BY FirstName"
         Case 2
            strOrderBy = " ORDER BY LastName"
         Case 3
            strOrderBy = " ORDER BY BirthDate"
         Case Else
            Exit Do
      End Select
      Set qdfCopy = CopyQueryNew(rstEmployees, strOrderBy)
      Set rstCopy = qdfCopy.OpenRecordset(dbOpenSnapshot, _
         dbForwardOnly)
      With rstCopy
         Do While Not .EOF
            Debug.Print !LastName & ", " & !FirstName & _
               " - " & !BirthDate
            .MoveNext
         Loop
         .Close
      End With
      Exit Do
   Loop

   rstEmployees.Close
   ' Delete new QueryDef because this is a demonstration.
   dbsNorthwind.QueryDefs.Delete qdfEmployees.Name
   dbsNorthwind.Close

End Sub


This example uses the CreateQueryDef and OpenRecordset methods and the SQL property to query the table of titles in the Microsoft SQL Server sample database Pubs and return the title and title identifier of the best-selling book. The example then queries the table of authors and instructs the user to send a bonus check to each author based on his or her royalty share (the total bonus is $1,000 and each author should receive a percentage of that amount).

Visual Basic for Applications
  Sub ClientServerX2()

   Dim dbsCurrent As Database
   Dim qdfBestSellers As QueryDef
   Dim qdfBonusEarners As QueryDef
   Dim rstTopSeller As Recordset
   Dim rstBonusRecipients As Recordset
   Dim strAuthorList As String

   ' Open a database from which QueryDef objects can be 
   ' created.
   Set dbsCurrent = OpenDatabase("DB1.mdb")

   ' Create a temporary QueryDef object to retrieve
   ' data from a Microsoft SQL Server database.
   Set qdfBestSellers = dbsCurrent.CreateQueryDef("")
   With qdfBestSellers
      ' Note: The DSN referenced below must be configured to 
      '       use Microsoft Windows NT Authentication Mode to 
      '       authorize user access to the Microsoft SQL Server.
      .Connect = "ODBC;DATABASE=pubs;DSN=Publishers"
      .SQL = "SELECT title, title_id FROM titles " & _
         "ORDER BY ytd_sales DESC"
      Set rstTopSeller = .OpenRecordset()
      rstTopSeller.MoveFirst
   End With

   ' Create a temporary QueryDef to retrieve data from
   ' a Microsoft SQL Server database based on the results from
   ' the first query.
   Set qdfBonusEarners = dbsCurrent.CreateQueryDef("")
   With qdfBonusEarners
      ' Note: The DSN referenced below must be configured to 
      '       use Microsoft Windows NT Authentication Mode to 
      '       authorize user access to the Microsoft SQL Server.
      .Connect = "ODBC;DATABASE=pubs;DSN=Publishers"
      .SQL = "SELECT * FROM titleauthor " & _
         "WHERE title_id = '" & _
         rstTopSeller!title_id & "'"
      Set rstBonusRecipients = .OpenRecordset()
   End With

   ' Build the output string.
   With rstBonusRecipients
      Do While Not .EOF
         strAuthorList = strAuthorList & "  " & _
            !au_id & ":  $" & (10 * !royaltyper) & vbCr
         .MoveNext
      Loop
   End With

   ' Display results.
   MsgBox "Please send a check to the following " & _
      "authors in the amounts shown:" & vbCr & _
      strAuthorList & "for outstanding sales of " & _
      rstTopSeller!Title & "."

   rstTopSeller.Close
   dbsCurrent.Close

End Sub