Создание строк SQL в Access/VBA

Ответ 1

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

Переменные 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

Обратите внимание, что Mid lines удаляет ведущий ",". lngTrans - значение autonumber 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, lngRecordsAffected, adCmdText. Вы можете удалить строки docmd.setwarnings.

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

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

Ответ 2

    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

Ответ 3

Добавив к тому, что сказал @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

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

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

  Public Sub AppendQueryString(strInput As String, _
       ByVal strAppend As String, Optional ByVal strOperator As String = "&")
    strAppend = StringReplace(strAppend, "&", "&")
    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*"

Вариации на этом могут быть более полезными для вас, т.е. вы можете захотеть иметь необязательный оператор конкатенации (чтобы вы могли иметь OR), но я, скорее всего, сделаю это, построив последовательность строк 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()).

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

Ответ 5

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

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

Ответ 6

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

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

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

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

Ответ 7

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

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