Строка работает ненормально в цикле For Each в коде VBA в MSAccess
Итак, я начал с вопрос и внес много изменений в код на основе предложений с этого и других сайтов, поэтому я решил, что должен создать новый вопрос.
Несмотря на то, что мой код короче и эффективнее, проблема остается; Я использую строку с именем strSQL, которая содержит оператор INSERT, который я хочу выполнить. У меня есть цикл FOR EACH, который проходит через каждый элемент управления в моей форме MSAcess (гарантирует, что они являются текстовым полем, раскрывающимся списком или флажком) и определяет, было ли изменено поле. Если он есть, он генерирует строку запроса для регистрации изменения и сохраняет ее в strSQL.
Проблема в том, что один и тот же запрос выполняется снова и снова. Я добавил оператор DEBUG.PRINT до и после строки, которая выполняет строку запроса, и отладчик показывает, что строка ИЗМЕНИЛАСЬ! Да, вы все правильно прочитали, это кажется невозможным, но я сделал скриншоты.
Во-первых, мой код:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim C As Control
For Each C In Controls
Select Case C.ControlType
Case acTextBox, acComboBox, acCheckBox
Dim strOriginalValue, strCurrentValue, strSQL As String
strOriginalValue = IIf(IsNull(C.OldValue), "", IIf(C.OldValue = vbTrue Or C.OldValue = vbFalse, IIf(C.OldValue = vbTrue, "Yes", "No"), C.OldValue))
strCurrentValue = IIf(IsNull(C.Value), "", IIf(C.Value = vbTrue Or C.Value = vbFalse, IIf(C.Value = vbTrue, "Yes", "No"), C.Value))
If strOriginalValue <> strCurrentValue Then
strSQL = "INSERT INTO fringefestival_changes (change_time,change_admin,action_taken,user_affected,year_affected,field_affected,type_affected,old_value,new_value) VALUES (NOW(),'" & ThisUserName() & "','Edit'," & [id] & ",0,'" & C.ControlSource & "','Administrator','" & Replace(strOriginalValue, "'", "") & "','" & Replace(strCurrentValue, "'", "") & "')"
Debug.Print "Before: " & strSQL
CurrentDb.Execute strSQL, dbFailOnError
Debug.Print "After: " & strSQL
End If
End Select
Next
End Sub
Вот результаты работы отладчика:
Before: INSERT INTO fringefestival_changes (change_time,change_admin,action_taken,user_affected,year_affected,field_affected,type_affected,old_value,new_value) VALUES (NOW(),'ajohnson','Edit',3,0,'indoor_performers_tab','Administrator','No','Yes')
After: INSERT INTO fringefestival_changes (change_time,change_admin,action_taken,user_affected,year_affected,field_affected,type_affected,old_value,new_value) VALUES (NOW(),'ajohnson','Edit',3,0,'indoor_performers_tab','Administrator','No','Yes')
Before: INSERT INTO fringefestival_changes (change_time,change_admin,action_taken,user_affected,year_affected,field_affected,type_affected,old_value,new_value) VALUES (NOW(),'ajohnson','Edit',3,0,'volunteers_tab','Administrator','No','Yes')
After: INSERT INTO fringefestival_changes (change_time,change_admin,action_taken,user_affected,year_affected,field_affected,type_affected,old_value,new_value) VALUES (NOW(),'ajohnson','Edit',3,0,'volunteers_tab','Administrator','No','Yes')
Во-вторых, мои скриншоты ... во-первых, отладчик: 1 & 2 и, во-вторых, результаты: здесь - обратите внимание, что отладчик слишком широкий, поэтому я сделал два снимка экрана, и в результатах выделена строка, так как это не имеет никакого отношения к этой проблеме .
Обратите внимание, что единственный столбец, который должен отличаться в приведенном мной примере, - это столбец field_affected.
Я в полной растерянности, я понятия не имею, почему он выводит правильную строку в отладчик и выполняет что-то еще.
Обновлено: Перед использованием CurrentDb.Execute я использовал DoCmd.RunSQL, но для этого мне потребовалось отключить, а затем снова включить предупреждения. Поскольку результат был одинаковым для обоих (одна и та же ошибка), я использовал однострочное решение вместо трехстрочного.
ОБНОВЛЕНИЕ: Привет Шахкалпеш за то, что он помог мне понять, что данные действительно вставляются правильно, и я вижу их в студии управления SQL Server, но MS Access все еще отображает их неправильно ... вопрос в том, почему?
ОКОНЧАТЕЛЬНОЕ РЕДАКТИРОВАНИЕ: исправлено добавлением в таблицу целочисленного столбца идентификатора / автоинкремента, я подозреваю, что отсутствие разных значений полей сбивало с толку MSAccess (хотя на самом деле для этого нет причин) - моральный дух здесь M $ глуп
PS Можно упростить: strOriginalValue = IIf (C.OldValue = vbTrue или C.OldValue = vbFalse, _ Format (C.OldValue, "Да / Нет"), Nz (C.OldValue, ""))
Пожалуйста, не используйте снимки экрана, пожалуйста, покажите в своем тексте разницу между до и после того, как это вас беспокоит - я могу видеть только вывод SQL в соответствии с запросом в коде.
Remou - результаты добавлены в виде текста
В чем разница, которая вас беспокоит? Я не вижу ничего плохого, SQL выполняется должным образом. Что вы хотели?
Должно быть выполнено 2 запроса; первый со значением "Indoor_performers_tab" в качестве значения "field_affected", а второй со значением "volunteers_tab" в качестве значения. Оба они отображаются как "closed_performers_tab". Обратите внимание, что в этом примере всего 2 запроса, если я сделаю 5 или более, он будет действовать так же.
Изменение для отображения ошибки: CurrentDb.Execute strSQL, dbFailOnError
Я все еще не вижу разницы.
Значит, вы не видели свои новые записи в таблице аудита? Да, у вас должен быть первичный ключ или уроды Access.
Я видел их, но если бы у меня были две практически идентичные строки, вставленные почти одновременно, они бы отображались 100 и идентичными в возвращаемых результатах.
Ответы 7
Привязана ли рассматриваемая форма к источнику данных? Это наиболее распространенный способ использования Access, и если бы он был у вас, вероятно, не нужно было бы обновлять через SQL.
Form_BeforeUpdate () обычно используется, когда форма привязана к источнику данных. Обычно вы не используете это событие, чтобы определить, изменились ли значения элементов управления и нуждаются ли они в обратной записи в БД ...
Да, я использую только MS Access в качестве интерфейса - все данные хранятся в базе данных SQL Server. Что бы вы посоветовали мне сделать в этой ситуации?
Также я использую ODBC для подключения к базе данных, если это еще не было очевидно ...
Если форма и ее элементы управления привязаны к источнику данных (который является запросом, основанным на ваших связанных таблицах), вам, вероятно, не нужно писать какой-либо код (если вы не хотите перехватывать и изменять значения перед записью), чтобы сохранить измененную информацию ...
Я полагаю, вы говорите, что мне следует использовать триггер. Первоначально я пошел по этому пути, но отказался от него, поскольку мне нужно регистрировать имя пользователя активного каталога лица, вносящего изменение (возвращаемое значение ThisUserName ()), и я не мог сделать это с помощью триггера.
Это административный модуль для местного фестиваля. Если в исполнителей, шоу и т. д. Вносятся изменения, главный администратор хотел бы увидеть, кто именно внес это изменение и что это было за изменение - также известные как старые / новые значения.
Если каждый пользователь подключается по своему принципу входа в Windows (вместо использования объединенной строки подключения), вы можете использовать триггер с TSQL SUSER_NAME (). Но если вы используете общее соединение, это явно не годится ...
Это нехорошо, поверьте, когда я говорю, что шел по тому же пути, что и вы сейчас :)
Итак, вы используете объединенное соединение?
На самом деле нет смысла не использовать несвязанные формы с источниками данных ODBC. Вы также можете программировать на простом VB, если вы не используете связанные формы / элементы управления.
Вы запускаете эти запросы к currentdb (я предполагаю, что это access-db), но на самом деле используете базу данных SQL-сервера, в которой вы хотите, чтобы изменения произошли?
Связаны ли эти таблицы на сервере sql с таблицами в access-db или какой метод вы используете?
Да, они связаны таблицами с базой данных SQL Server.
Вы уверены, что ссылки указывают на базу данных, которую вы думаете? Я много раз совершал ошибку (при переключении между разными серверами dev / deploy), когда смотрел в неправильную базу данных, думая, что я работаю с моей локальной БД, но на самом деле работаю против другой.
Нет, я уверен, что эта схема не только в одной базе данных, но даже если бы я подключился не к той, у меня все равно не было бы этой ошибки из-за этого.
Это "могло бы" проявить себя так, если "другая" база данных уже имеет только эти две записи и никогда не обновляется, показывает только их ... Но хорошо, если вы исключили это, тогда мы должны проверить другие возможности
Вы проверили таблицу на наличие неработающих триггеров? Это маловероятно, но некоторые очень-очень странные проблемы раньше были вызваны плохими / сломанными триггерами, и их трудно понять, прежде чем вы заметите триггер ...
Никаких триггеров для всей базы данных, но допустимое предложение.
Если вы можете перейти к конкретной таблице SQL Server (с помощью Query Analyzer или Enterprise Mgr) и посмотреть, работает ли она так, как вы ожидаете. Я сомневаюсь, что связанная таблица в VBA может показывать неправильное изображение.
Обновлено: если у вас есть SQL Profiler, посмотрите, что там выполняется, чтобы подтвердить свои сомнения в выполнении того же запроса.
Вау, ты абсолютно прав. Если я перейду прямо в SQL Server, данные верны. Я действительно громко рассмеялся, когда увидел это, теперь вопрос: почему это не отображается должным образом в MS Access?
Не уверен насчет этой части. Но я наблюдал это даже при работе в Access. И я подумал написать его после того, как ваш ответ о запросе правильно напечатан в окне отладки :)
Ах, вы тоже используете SQL Server. Мне очень повезло с использованием триггеров для аудита изменений данных в Access. Есть несколько причуд, но связывание таблиц через ODBC с использованием доверенной аутентификации делает это очень простым.
Итак, вы не можете или не хотите использовать триггеры. Когда я передаю SQL с Access, я обычно использую объекты ADO в своем коде VBA для такого рода вещей. Связанные таблицы с ODBC отлично подходят для связанных форм, но ничто не сравнится с ADO для прямого доступа к SQL Server.
Вам нужна дополнительная информация о связывании таблиц с использованием доверенной аутентификации или помощь в использовании ADO для работы с SQL Server?
Исправлено добавлением автоматически увеличивающегося целочисленного столбца первичного ключа в таблицу изменений.
Глупый доступ ...
Нет, глупый Эндрю Дж. Джонсон за то, что он не знает, как разработать приложение Access. Если вы задумались о том, как должен работать ODBC, вы бы поняли, что первичный ключ важен для всех таблиц и что поле отметки времени требуется для сохранения данных в связанной форме обновленными.
Глупый ODBC * - почему, по вашему мнению, разумно иметь все эти лишние ненужные заботы?
Я должен сказать, что обсуждение этого вопроса сводит меня с ума. При использовании таблиц, связанных с ODBC, вы начинаете со связанных форм. Просто нет причин использовать объединенные соединения. Если вы настроены враждебно к реализации Access, как это сделать, ПРЕКРАТИТЕ ИСПОЛЬЗОВАНИЕ ACCESS.
Пожалуйста, укажите, в чем именно разница между строками SQL до и после. У вас есть два подхода до и после, и они отличаются друг от друга, но, насколько я могу судить, предыдущие такие же, как и последующие.