QueryDef.SQL 属性 (DAO)
适用于：Access 2013、Office 2013
设置或返回定义由 QueryDef 对象执行的查询的 SQL 语句。
语法
表达式 .SQL
表达式 一个表示 QueryDef 对象的变量。
说明
SQL 属性包含用于确定当您执行查询时如何对记录进行选择、分组和排序的 SQL 语句。 您可以使用查询选择要在 Recordset 对象中包括的记录。 还可以定义动作查询，以便在不返回记录的情况下修改数据。
查询中使用的 SQL 语法必须符合查询引擎的 SQL 特定语言，该语言是由工作区的类型决定的。 在 Microsoft Access 工作区中，除非创建的是 SQL 传递查询（在这种情况下应当使用服务器的特定语言），否则应使用 Microsoft Access SQL 特定语言。
如果 SQL 语句包含查询的参数，您必须在执行操作之前设置这些参数。 除非您重置了参数，否则每次执行查询时，都将应用相同的参数值。
在 Microsoft Access 工作区中，在对 Microsoft Access 数据库引擎连接的 ODBC 数据源执行 SQL 传递操作时，最好使用 QueryDef 对象。 通过将 QueryDef 对象的 Connect 属性设置为 ODBC 数据源，可以在要传递到外部服务器的查询中使用非 Microsoft Access 数据库 SQL。 例如，对于 Microsoft SQL Server 或 Sybase SQL Server 数据库，可以使用 TRANSACT SQL 语句，Microsoft Access 数据库引擎不对其进行处理。
注意
[!注释] 如果将该属性设置为连接了非整数值的字符串，并且系统参数指定了诸如逗号的非美国格式小数字符（例如 strSQL = "PRICE > " & lngPrice, and lngPrice = 125,50），那么当尝试在 Microsoft Access 数据库引擎数据库中执行 QueryDef 对象时会发生错误。 这是因为在连接过程中，需要使用系统的默认小数字符将数字转换为字符串，并且 Microsoft Access SQL 只接受美国格式的小数字符。
示例
以下示例通过设置并更改临时 QueryDef 的 SQL 属性并比较结果来演示 SQL 属性。 若要使该过程运行，需要使用 SQLOutput 函数。
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
以下示例使用 CopyQueryDef 方法从现有的 Recordset 创建 QueryDef 的副本，然后通过向 SQL 属性添加子句修改副本。 创建永久的 QueryDef 时，可以向 SQL 属性添加空格、分号或换行符；只有在删除这些额外的字符之后，才可以将任何新子句附加到 SQL 语句。
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
本示例演示 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
以下示例使用 CreateQueryDef 和 OpenRecordset 方法以及 SQL 属性，查询 Microsoft SQL Server 示例数据库 Pubs 中的书名表，并返回最畅销书籍的书名和书名标识符。 然后查询作者表，并指示用户根据每个作者的版税份额向其发送红利支票（总红利为 ￥1,000，每个作者应收到该金额的一定份额）。
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
以下示例演示如何创建参数查询。 名为 myQuery 的查询通过两个参数（命名为 Param1 和 Param2）创建。 要执行此操作，查询的 SQL 属性设置为定义参数的结构化查询语言 (SQL) 语句。
示例代码提供方：Microsoft Access 2010 程序员参考。
Sub CreateQueryWithParameters()
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("myQuery")
Application.RefreshDatabaseWindow
strSQL = "PARAMETERS Param1 TEXT, Param2 INT; "
strSQL = strSQL & "SELECT * FROM [Table1] "
strSQL = strSQL & "WHERE [Field1] = [Param1] AND [Field2] = [Param2];"
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing
Set dbs = Nothing
End Sub
以下示例演示如何替换保存的查询中的结构化查询语言 (SQL) 语句。
Dim qdf as QueryDef
Dim db as Database
Set db = CurrentDB
Set qdf = db.QueryDefs("YourQueryName")
qdf.SQL = ReplaceWhereClause(qdf.SQL, strYourNewWhereClause)
set qdf = Nothing
set db = Nothing
Public Function ReplaceWhereClause(strSQL As Variant, strNewWHERE As Variant)
On Error GoTo Error_Handler
‘This subroutine accepts a valid SQL string and Where clause, and
‘returns the same SQL statement with the original Where clause (if any)
‘replaced by the passed in Where clause.
‘
‘INPUT:
‘ strSQL valid SQL string to change
‘OUTPUT:
‘ strNewWHERE New WHERE clause to insert into SQL statement
‘
Dim strSELECT As String, strWhere As String
Dim strOrderBy As String, strGROUPBY As String, strHAVING As String
Call ParseSQL(strSQL, strSELECT, strWhere, strOrderBy, _
strGROUPBY, strHAVING)
ReplaceWhereClause = strSELECT &""& strNewWHERE &""_
& strGROUPBY &""& strHAVING &""& strOrderBy
Exit_Procedure:
Exit Function
Error_Handler:
MsgBox (Err.Number & ": " & Err.Description)
Resume Exit_Procedure
End Function