Формирование строки SQL-запроса в VBA

Опубликовано: 15 авг 03
Рейтинг:

Добавить в закладки и поделиться

Оценить и прокомменитировать
Распечатать статью

Автор: Sfagnum
Прислал: Sfagnum

Аксесс позволяет создавать программно строки SQL-запросов, а также строки, служащие значениями свойств форм, отчетов и т.д., параметров функций и т.д., которые тоже должны соответствовать синтаксису SQL. При этом надо помнить, что входящие в такую строку константы должны быть тоже оформлены по правилам SQL, а именно:

в числах десятичным разделителем должна быть точка,

строки должны быть заключены в кавычки или апострофы (а кавычки или апострофы соответственно внутри строки удвоены),

даты должны быть заключены в решетки и написаны по американскому формату (mm/dd/yyyy) с дробной чертой в качестве разделителя.

Если это нарушить, то возможны следующие эффекты:

число вида 10,5 с запятой вместо точки будет воспринято как список из двух чисел 10 и 5, что приведет к какому-нибудь несоответствию в количестве полей,

строка вида Vasya без кавычек и апострофов будет воспринята как имя поля, если такое поле есть, или как имя параметра, который тут же будет запрошен,

дата вида 1/2/2010 или 1-2-2010 без решеток будет воспринята как арифметическое выражение (с делением и вычитанием соответственно),

дата вида 1.2.2010 будет воспринята как дробное число с двумя десятичными точками и приведет к ошибке,

дата с решетками, но не по американскому формату будет воспринята как дата, но другая (день и месяц будут переставлены).

Ниже в каждом разделе приведен пример строки SQL, которая должна получиться при ее программном создании, а затем код VBA, который ее создает. Еще ниже дана очень полезная рекомендация.

1. Использование чисел

SQL

SELECT *

FROM Table

WHERE (((Table.Quanty)=12.197));

VBA v1

Dim q As Single

q = 12.197

strSQL = "SELECT * " _

  & "FROM Table " _

  & "WHERE (((Table.Quanty)=" & q & "));"

VBA v2

Dim q As String

q = "12,197"

strSQL = "SELECT * " _

  & "FROM Table " _

  & "WHERE (((Table.Quanty)=" & Str(q) & "));"

Примечание:

VBA v1 - для целых чисел. Для дробных чисел это частный случай, только когда системным разделителем является точка.

VBA v2 - более правильный вариант, т.к. в классическом программировании допускается соединять только строки со строками, тогда как VBA v1 использует неявное преобразование типов, хотя нареканий на этот способ для целых чисел не было. (Пример приведён для случая, когда системным разделителем является запятая.)

Иногда также используют для преобразования функцию CStr(), но она не всегда применима, т.к. возвращает число в виде строки, где оно записывается через системный разделитель, тогда как SQL в качестве разделителя воспринимает только точку.

NB! При использовании нетипичных системных разделителей вышеприведённые примеры могут не работать, в этих случаях надо программно заменять системный разделитель на точку. Ниже приводится функция, возвращающая системный разделитель.

Function GetDecimalSeparator() As String

  GetDecimalSeparator = Format(0#, ".")

End Function

Также следует учесть, что в этом случае некоторые стандартные действия Access могут не работать.

2. Использование строк

SQL

SELECT *

FROM Table

WHERE (((Table.Name)="All"));

VBA v1

Dim q As String

q = "All"

strSQL = "SELECT * " _

  & "FROM Table " _

  & "WHERE (((Table.Quanty)=""" & DoubleQuote(q) & """));"

VBA v2

Dim q As String

q = "All"

strSQL = "SELECT * " _

  & "FROM Table " _

  & "WHERE (((Table.Quanty)='" & DoubleApostrophe(q) & "'));"

Примечание:

VBA v1: DoubleQuote() - функция, удваивающая кавычки.

Пример:
условие выборки:

  a"a's

SQL:

  WHERE field="a""a's"

VBA:

  strWhere = "WHERE field=""" & "a""""a's" & """"

VBA v2:: DoubleApostrophe() - функция, удваивающая апострофы.

Пример:
условие выборки:

  a"a's

SQL:

  WHERE field='a"a''s'

VBA:

  strWhere = "WHERE field='" & "a""a''s" & "'"

Упомянутые выше функции DoubleQuote и DoubleApostrophe - это НЕ встроенные функции Аксесса, а пользовательские функции, реализация которых оставляется на усмотрение программиста. В частности, в Аксессе версий 2000 и выше можно для этой цели использовать встроенную функцию Replace, а в 97 и ниже - вот такую функцию:

Public Function Replace97(StrMain As String, StrFind As String, StrZam As String) As String

On Error GoTo err

    Dim pos As Long

    If StrFind = "" Then GoTo err

    If StrMain = "" Then Replace97 = StrZam: Exit Function

    Do Until InStr(1, StrMain, StrFind) = 0

        pos = InStr(1, StrMain, StrFind)

        StrMain = mid(StrMain, 1, pos - 1) & StrZam & mid(StrMain, pos + Len(StrFind), Len(StrMain))

    Loop

    Replace97 = StrMain

    Exit Function

err:

    Replace97 = StrMain

End Function

3. Использование дат

SQL

SELECT *

FROM Table

WHERE (((Table.TimeOpen)=#3/31/2003 11:17:19#));

VBA

Dim q As Date

q = Now

strSQL = "SELECT * " _

  & "FROM Table " _

  & "WHERE (((Table.TimeOpen)=#" & Format(q, "mm\/dd\/yy hh\:mm\:ss") & "#));"

Примечание:

Microsoft JET SQL оперирует датами в американском формате, т.е. именно в вышеуказанном виде Месяц/День/Год.

Не пропустите символы # (он обрамляет всю константу типа дата-время) и \ (он предохраняет / и : от их замены в соответствии с региональными настройками, что имеет обыкновение делать функция Format и что мешает правильной работе команды SQL).

Рекомендуется почитать здесь: http://www.sql.ru/faq/faq_topic.aspx?fid=103 о способах хранения даты/времени.

NB! Не стоит пользоваться преобразованием даты в Integer (или Long), т.к. в Access'е и в SQL Server'е одной и той же дате соответствуют разные числа и при сравнении можно получить неожиданный результат.

А теперь очень полезная рекомендация

Составив такой стринг, содержащий команду SQL, и отправив его на выполнение, вы можете получить ошибку. В таком случае распечатайте этот стринг в отладочное окно и посмотрите на него глазами. Возможно, ошибка сразу бросится в глаза. Если не бросится, создайте новый запрос в конструкторе запросов, переключите в режим SQL, вставьте туда текст запроса и запустите на выполнение. Если ошибка есть, то она будет показана более явным образом.