Построение строк SQL в Access/VBA

Question

Построение строк SQL в Access/VBA

Иногда мне приходилось создавать строку SQL в VBA и выполнять ее с Docmd.RunSql(), Я всегда строил эти строки путем объединения переменных в строку, например:

Dim mysqlstring as String
mysqlstring = "INSERT INTO MyTable (Field1, Field2, Field3 ...) VALUES ("
mysqlstring = mysqlstring + Me.TextMyField1 + ", " 'parameter comments
mysqlstring = mysqlstring + Me.TextMyField2 + ", " 
mysqlstring = mysqlstring + Me.TextMyField3 + ", " 
...
mysqlstring = mysqlstring + ");"
Docmd.RunSql mysqlstring

В VBA, похоже, нет оператора унарной конкатенации (например, + =), и хотя это не выглядит идеально, по крайней мере, я могу комментировать каждый из моих параметров и изменять их независимо. Это облегчает чтение и изменение, чем одна строка монстров. Но это все еще кажется ужасным способом построения строк SQL. У меня есть один с около 50 параметров на работе, поэтому 50 строк mysqlstring = mysqlstring +..., Не милый.

Кстати, это исключает использование продолжений строки для форматирования строки, поскольку существует ограничение на количество продолжений строки, которые вы можете использовать для одной строки (подсказка: менее 50). Кроме того, VBA не позволяет оставлять комментарии после продолжения строки, grr!

До недавнего времени я думал, что это единственный способ создать эти строки. Но недавно я увидел другую схему, в которой параметры в строке вводились, как этот вопрос (VB.NET), на который я отправил ответ, и задавался вопросом, существует ли эквивалент Parameters.AddWithValue() для VBA, или если это даже лучше, чем подход конкатенации строк. Поэтому я решил, что это заслуживает отдельного вопроса. Может быть, я чего-то здесь упускаю.

Могут ли некоторые из экспертов по Access уточнить, каковы лучшие практики для построения строк SQL в Access/VBA.

15

sql vba ms-access

Источник

user173197 26 ноя '09 в 07:41

7 ответов

Решение

В дополнение к тому, что сказал @astander, вы можете создать querydef (с параметрами) и сохранить его как часть базы данных.

например

Parameters dtBegin DateTime, dtEnd DateTime;
INSERT into myTable (datebegin, dateend) values (dtBegin, dtEnd)

Предположим, вы сохранили его с именем myTableInsertВы можете написать код, как показано ниже

dim qd as QueryDef
set qd = CurrentDB.QueryDefs("myTableInsert")
qd.Parameters("dtBegin").Value = myTextFieldHavingBeginDate
qd.Parameters("dtEnd").Value = myTextFieldHavingEndDate    
qd.Execute

Примечание. Я не проверял этот фрагмент кода. Но я предполагаю, что так и должно быть.
Надеюсь, что это даст вам достаточно информации, чтобы начать.

4

Источник

user23574 26 ноя '09 в 08:29

    Private Sub Command0_Click()
Dim rec As Recordset2
Dim sql As String
Dim queryD As QueryDef

    'create a temp query def.
    Set queryD = CurrentDb.CreateQueryDef("", "SELECT * FROM [Table] WHERE Val = @Val")
    'set param vals
    queryD.Parameters("@Val").Value = "T"
    'execute query def
    Set rec = queryD.OpenRecordset
End Sub

4

Источник

user144424 26 ноя '09 в 08:17

Как уже говорили другие, вероятно, лучше использовать параметры в первую очередь. Тем не мение,...

Я тоже пропустил оператор конкатенации, привыкший к.= В PHP. В некоторых случаях я написал для этого функцию, хотя и не относящуюся к конкатенации строк SQL. Вот код для одного я использую для создания строки запроса для HTTP GET:

  Public Sub AppendQueryString(strInput As String, _
       ByVal strAppend As String, Optional ByVal strOperator As String = "&")
    strAppend = StringReplace(strAppend, "&", "&amp;")
    strInput = strInput & strOperator & strAppend
  End Sub

И пример того, где я это назвал:

  AppendQueryString strOutput, "InventoryID=" & frm!InventoryID, vbNullstring
  AppendQueryString strOutput, "Author=" & URLEncode(frm!Author)

...и так далее.

Теперь для конструирования предложений SQL WHERE вы можете рассмотреть что-то вроде этого как обертку вокруг Application.BuildCriteria:

  Public Sub ConcatenateWhere(ByRef strWhere As String, _
      strField As String, intDataType As Integer, ByVal varValue As Variant)
    If Len(strWhere) > 0 Then
       strWhere = strWhere & " AND "
    End If
    strWhere = strWhere & Application.BuildCriteria(strField, _
       intDataType, varValue)
  End Sub

Затем вы бы назвали это как:

  Dim strWhere As String

  ConcatenateWhere strWhere,"tblInventory.InventoryID", dbLong, 10036
  ConcatenateWhere strWhere,"tblInventory.OtherAuthors", dbText, "*Einstein*"
  Debug.Print strWhere
  strSQL = "SELECT tblInventory.* FROM tblInventory"
  strSQL = strSQL & " WHERE " & strWhere

... и Debug.Print выведет эту строку:

  tblInventory.InventoryID=10036 AND tblInventory.OtherAuthors Like "*Einstein*"

Вариации этого могут быть более полезными для вас, т. Е. Вы можете захотеть иметь необязательный оператор конкатенации (чтобы вы могли иметь ИЛИ), но я бы, вероятно, сделал это путем построения последовательности строк WHERE и конкатенации их с помощью строки ИЛИ путем в строке кода, так как вы, вероятно, захотите аккуратно поместить скобки, чтобы убедиться, что приоритет AND/OR выполнен правильно.

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

Тем не менее, вы можете использовать что-то вроде этого:

  Public Sub ConcatenateValues(ByRef strValues As String, _
      intDatatype As Integer, varValue As Variant)
    Dim strValue As String

    If Len(strValues) > 0 Then
       strValues = strValues & ", "
    End If
    Select Case intDatatype
      Case dbChar, dbMemo, dbText
        ' you might want to change this to escape internal double/single quotes
        strValue = Chr(34) & varValue & Chr(34)
      Case dbDate, dbTime
        strValue = "#" & varValue & "#"
      Case dbGUID
        ' this is only a guess
        strValues = Chr(34) & StringFromGUID(varValue) & Chr(34)
      Case dbBinary, dbLongBinary, dbVarBinary
        ' numeric?
      Case dbTimeStamp
        ' text? numeric?
      Case Else
        ' dbBigInt , dbBoolean, dbByte, dbCurrency, dbDecimal, 
        '   dbDouble, dbFloat, dbInteger, dbLong, dbNumeric, dbSingle
        strValue = varValue
    End Select
    strValues = strValues & strValue
  End Sub

... который объединит ваш список значений, а затем вы сможете объединить всю строку SQL (между символами предложения VALUES()).

Но, как говорили другие, в первую очередь, вероятно, лучше использовать параметры.

1

Источник

user9787 26 ноя '09 в 22:32

FWIW, я использую немного другой формат, используя символ разрыва строки Access "_". Я также использую оператор конкатенации "&". Основная причина заключается в удобочитаемости:

Dim db as Database: Set db = Current Db
Dim sql$
sql= "INSERT INTO MyTable (Field1, Field2, Field3 ...Fieldn) " & _
     "VALUES (" & _
     Me.TextMyField1 & _
     "," & Me.TextMyField2 & _
     "," & Me.TextMyField3 & _
     ...
     "," & Me.TextMyFieldn & _
     ");"
db.Execute s
Set db = nothing

1

Источник

user190925 28 ноя '09 в 00:00

Одна из вещей, которые я делал в прошлом, - это создание системы для синтаксического анализа кода SQL для поиска параметров и сохранения параметров в таблице. Я написал бы мои запросы MySQL вне Access. Тогда все, что мне нужно было сделать, это открыть файл из Access, и он будет готов к обновлению на лету каждый раз, когда я захочу его запустить.

Это был действительно сложный процесс, но я был бы рад выкопать код на следующей неделе, когда вернусь к работе, если вам интересно.

0

Источник

user166258 26 ноя '09 в 15:38

Я бы использовал описанный выше подход, с каждым параметром в отдельной строке, который легко и просто отлаживать и добавлять.

Однако, если вам действительно не нравится этот способ, вы можете посмотреть на запрос параметров. Немного менее гибкий, но в некоторых случаях немного быстрее.

Или другой способ - определить публичную функцию для вставки в эту таблицу и передать ей значения в качестве параметров.

Однако я бы придерживался того, что вы получили, но было бы хорошо, если бы VBA понял =+

0

Источник

user181711 26 ноя '09 в 08:18

Другие вопросы по тегам sql vba ms-access

user93528 26 ноя '09 в 19:18 2009-11-26 19:18 · Accepted Answer · 2009-11-26 19:18

У меня есть приложение с расписанием с достаточно сложной формой ввода несвязанных трудовых транзакций. Существует много данных проверки, расчета ставок и другого кода. Я решил использовать следующее для создания полей SQL Insert/Update.

Переменные strSQLInsert, strSQLValues, strSQLUpdate являются строками уровня формы.

Многие строки следующие:

Call CreateSQLString("[transJobCategoryBillingTypesID]", lngJobCategoryBillingTypesID)

с последующим:

If lngTransID = 0 Then
    strSQL = "INSERT into Transactions (" & Mid(strSQLInsert, 3) & ") VALUES (" & Mid(strSQLValues, 3) & ")"
Else
    strSQL = "UPDATE Transactions SET " & Mid(strSQLUpdate, 3) & " WHERE transID=" & lngTransID & ";"
End If

conn.Open
conn.Execute strSQL, lngRecordsAffected, adCmdText

Обратите внимание, что средние строки удаляют ведущие ", ". lngTrans - это значение автонумера primamy kay.

Sub CreateSQLString(strFieldName As String, varFieldValue As Variant, Optional blnZeroAsNull As Boolean)
'    Call CreateSQLString("[<fieldName>]", <fieldValue>)

Dim strFieldValue As String, OutputValue As Variant

    On Error GoTo tagError

    ' if 0 (zero) is supposed to be null
    If Not IsMissing(blnZeroAsNull) And blnZeroAsNull = True And varFieldValue = 0 Then
        OutputValue = "Null"
    ' if field is null, zero length or ''
    ElseIf IsNull(varFieldValue) Or Len(varFieldValue) = 0 Or varFieldValue = "''" Then
        OutputValue = "Null"
    Else
        OutputValue = varFieldValue
    End If

    ' Note that both Insert and update strings are updated as we may need the insert logic for inserting
    '    missing auto generated transactions when updating the main transaction
    ' This is an insert
    strSQLInsert = strSQLInsert & ", " & strFieldName
    strSQLValues = strSQLValues & ", " & OutputValue
    ' This is an update
    strSQLUpdate = strSQLUpdate & ", " & strFieldName & " = " & OutputValue

    On Error GoTo 0
    Exit Sub

tagError:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CreateSQLString of VBA Document Form_LabourEntry"
    Exit Sub
End Sub

Я вижу, что все другие постеры используют метод Execute. Проблема с DoCmd.RunSQL заключается в том, что он может игнорировать ошибки. Любое из следующего покажет любые сообщения об ошибках, полученные запросом. Если используется DAO, используйте Currentdb.Execute strSQL,dbfailonerror. Для ADO используйте CurrentProject.Connection.Execute strCommand, lngRecordsActed, adCmdText. Затем можно удалить строки docmd.setwarnings.

Если вы собираетесь использовать docmd.setwarnings, убедитесь, что вы поместили выражение True также в любой код обработки ошибок. В противном случае странные вещи могут случиться позже, особенно когда вы работаете над приложением. Например, вы больше не будете получать сообщение "Вы хотите сохранить свои изменения", если закроете объект. Это может означать, что нежелательные изменения, удаления или добавления будут сохранены на вашем MDB.

Также производительность может существенно отличаться между двумя методами. В одной публикации сообщалось, что currentdb.execute занял две секунды, а docmd.runsql - восемь секунд. Как всегда YMMV.