Свойство QueryDef.SQL (DAO)
Область применения: Access 2013, Office 2013
Задает или возвращает инструкцию SQL, которая определяет запрос, выполняемый объектом QueryDef.
Синтаксис
выражение .SQL
выражение: переменная, представляющая объект QueryDef.
Примечания
Свойство SQL содержит инструкцию SQL, которая определяет, как записи выбираются, группируются и упорядочиваются при выполнении запроса. С помощью запроса можно выбрать записи, включаемые в объект Recordset. Вы также можете определять запросы действий для изменения данных без возврата записей.
Используемый в запросе синтаксис SQL должен соответствовать диалекту SQL обработчика запросов, который зависит от типа рабочей области. В рабочей области Microsoft Access используется диалект Microsoft Access SQL, если только вы не создаете SQL-запрос к серверу. В таком случае следует использовать диалект сервера.
Если инструкция SQL включает параметры для запроса, то их следует задать перед выполнением. Пока вы не сбросите параметры, при каждом выполнении запроса будут применяться одни и те же их значения.
В рабочей области Microsoft Access использование объекта QueryDef — это предпочтительный способ выполнения сквозных операций SQL с источниками данных ODBC, подключенными к ядру СУБД Microsoft Access. Указав в свойстве Connect объекта QueryDef источник данных ODBC, вы можете использовать операции SQL не для баз данных Microsoft Access в запросе, передаваемом на внешний сервер. Например, вы можете использовать инструкции TRANSACT SQL (с базами данных Microsoft SQL Server или Sybase SQL Server), которые ядро СУБД Microsoft Access иначе не могло бы обрабатывать.
Примечание.
Если свойству присвоено значение строки, объединенной с нецелочисленным значением, а параметры системы содержат десятичные символы, не используемые в США, например запятую (пример: strSQL = "PRICE > " & lngPrice, and lngPrice = 125,50), то при попытке использовать объект QueryDef в базе данных ядра СУБД Microsoft Access. Это связано с тем, что при объединении число будет преобразовано в строку при помощи используемого по умолчанию в вашей системе десятичного символа, а Microsoft Access SQL поддерживает только десятичные символы, используемые в США.
Пример
В этом примере демонстрируется использование свойства SQL. Для этого задается и меняется значение свойства SQL временного объекта QueryDef, а затем сравниваются результаты. Функция 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, чтобы создать копию объекта QueryDef из имеющегося объекта Recordset, а затем эта копия меняется путем добавления предложения к свойству 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, чтобы отправить запрос к таблице названий в примере базы данных Pubs из Microsoft SQL Server и получить название самой продаваемой книги, а также его идентификатор. Затем этот пример отправляет запрос к таблице авторов и предлагает пользователю отправить премиальный чек каждому автору с учетом доли его лицензионных выплат (общая сумма премии составляет 1000 долл. США, а каждый автор должен получить определенный процент от этой суммы).
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