Добавление новой записи с помощью VBA

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

Один из моих коллег сказал мне, что мне нужно было выполнить SQL-запрос с VBA. Что я до сих пор таков:

    Dim rsDessin As DAO.Recordset
    Dim strContrat As String
    Dim strProjet As String
    Dim strDessin As String
    Dim sqlquery As String

  'I think these next 3 lines are unimportant. I set a first query to get information I need from another table

    strDessin = Me.Combo_Dessin
    strProjet = Me.Combo_Projet
    sqlquery = "SELECT [Projet HNA] FROM [Projets] WHERE [Projet AHNS] = '" & strProjet & "'"

    Set rsDessin = CurrentDb.OpenRecordset(sqlquery)

    If Not rsDessin.RecordCount > 0 Then 'If not present I want to add it 
        strContrat = rsDessin![Projet HNA]
        sqlquery = "INSERT INTO Feuilles ([AHNS], [Contrat], [No Projet]) VALUES (strDessin, strContrat, strDessin)"
        'Not sure what to do with this query or how to make sure it worked.
    End If

 'Checking my variables
    Debug.Print strProjet
    Debug.Print strContrat
    Debug.Print strDessin

 'By here I'd like to have inserted my new record.

    rsDessin.Close
    Set rsDessin = Nothing

Я также читал в Интернете, что я мог бы получить аналогичный результат с чем-то вроде этого:

Set R = CurrentDb.OpenRecordset("SELECT * FROM [Dessins]")
R.AddNew
R![Contrat] = strContrat
R![Projet] = strProjet
R![AHNS] = strDessin
R.Update
R.Close
Set R = Nothing
DoCmd.Close

Один путь лучше другого? В случае, когда мой запрос INSERT INTO лучше, что мне следует сделать для его выполнения?

Ответы

Ответ 1

Вы спрашиваете, что предпочтительнее при вставке записи: использовать инструкцию SQL, выданную объекту Database, или использовать методы объекта Recordset.

Для одной записи это не имеет значения. Однако вы можете написать инструкцию INSERT следующим образом:

CurrentDb.Execute "INSERT INTO Feuilles ([AHNS], [Contrat], [No Projet]) VALUES (" & strDessin & ", " & strContrat & ", " & strDessin & ")", dbFailOnError

(Вы должны использовать опцию dbFailOnError, чтобы поймать определенные ошибки, поскольку HansUp указывает на этот ответ.)

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

Dim sql = _
    "INSERT INTO DestinationTable (Field1, Field2, Field3) " & _
    "SELECT Field1, Field2, Field3 " & _
    "FROM SourceTable"
CurrentDb.Execute sql

чем эквивалент, используя объект Recordset:

Dim rsSource As DAO.Recordset, rsDestination As DAO.Recordset
Set rsSource = CurrentDb.OpenRecordset("SourceTable")
Set rsDestination = CurrentDb.OpenRecordset("DestinationTable")
Do Until rs.EOF
    rsDestination.AddNew
    rsDestination!Field1 = rsSource!Field1
    rsDestination!Field2 = rsSource!Field2
    rsDestination!Field3 = rsSource!Field3
    rsDestination.Update
    rs.MoveNext
Loop

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

  • Вы ограничены синтаксисом SQL и функциями.

    Это частично смягчается в Access, потому что операторы SQL могут использовать многие встроенные функции VBA или функции, которые вы определяете.

  • Операторы SQL предназначены для работы с блоками строк. Локальная строка сложнее выразить только с помощью Iif, Выберите или Switch функции; и логика, которая зависит от текущего состояния (например, вставить каждую другую запись), сложнее или невозможна с использованием чистого SQL. Это можно легко сделать с помощью метода меток Recordset.

    Это тоже можно включить с помощью комбинации VBA и SQL, если у вас есть функции, которые сохраняются в переменных уровня модуля. Одно предостережение: вам нужно reset состояние каждый раз перед выпуском инструкции SQL. См. здесь для примера.

Ответ 2

Одна часть * вашего вопроса задала вопрос о INSERT vs. Recordset.AddNew, чтобы добавить одну строку. Я предлагаю этот подход к набору записей:

Dim db As DAO.Database
Dim R As DAO.Recordset
Set db = CurrentDb
Set R = db.OpenRecordset("Dessins", dbOpenTable, dbAppendOnly)
With R
    .AddNew
    !Contrat = rsDessin![Projet HNA].Value
    !Projet = Me.Combo_Projet.Value
    !AHNS = Me.Combo_Dessin.Value
    .Update
    .Close
End With

* Вы также спросили, как выполнить INSERT. Используйте метод DAO.Database.Execute, который Zev рекомендовал и включил опцию dbFailOnError. Это добавит ясности в отношении некоторых сбоев вставки. Например, ошибка нарушения ключа может привести к тому, что ваш INSERT завершится с ошибкой. Но в том числе dbFailOnError гарантирует, что вы сразу получите уведомление о проблеме. Поэтому всегда включайте этот параметр... кроме случаев, когда вы действительно хотите, чтобы INSERT терпело неудачу. (Для меня это никогда.)