Вопрос:
Это может быть очень простой вопрос, но мне трудно понять информацию, которую я нашел в Интернете до сих пор. Когда я пытаюсь запустить SQl из моего VBA, это дает мне несоответствие типа на моей strSQl.
У меня есть таблица [tbl_DatabaseUpdateLog] с 4 столбцами
UpdateID (Autonumber)
Начало (дата/время)
Конец (дата/время)
Продолжительность (дата/время)
Я должен регулярно извлекать информацию из баз данных компании для меньшего проекта анализа, и я хочу регистрировать, как часто и сколько времени требуется для выполнения этих запросов. Каждый раз при запуске обновления выполняется следующее:
Dim StartUpdate, EndUpdate, LengthUpdate, strSQl As Date StartUpdate = Time() Call UpdateAll ‘This calls the collection of queries and is irrelevant for my problem EndUpdate = Time() LengthUpdate = EndUpdate — StartUpdate Forms!frm_Timer.Caption = «Update Completed at » & EndUpdate & » (» & Format(LengthUpdate, «HH:MM:SS») & «)» DoCmd.SetWarnings (0) strSQl = «INSERT INTO tbl_DatabaseUpdateLog ( Start, [End], Duration ) » & _ «SELECT ‘» & StartUpdate & «‘ AS Started, ‘» & EndUpdate & «‘ AS Ended, ‘» & LengthUpdate & «‘ AS Lasted» DoCmd.RunSQL strSQl DoCmd.SetWarnings (-1) DoCmd.Close acForm, Me.Name
Я пробовал использовать # вокруг дат и использовать Now() вместо Time(), но я чувствую, что у меня отсутствует базовая концепция, которая поможет мне исправить эту проблему. Мне просто нужно Время дня и продолжительность (не столько сама дата), если это помогает.
Лучший ответ:
Когда я пытаюсь запустить SQl из моего VBA, это дает мне несоответствие типа на моем strSQl
Изучите эти 2 заявления…
Dim StartUpdate, EndUpdate, LengthUpdate, strSQl As Date strSQl = «INSERT INTO tbl_DatabaseUpdateLog ( Start, [End], Duration ) » & _ «SELECT ‘» & StartUpdate & «‘ AS Started, ‘» & EndUpdate & «‘ AS Ended, ‘» & LengthUpdate & «‘ AS Lasted»
Когда вы объявляете strSQl As Date, это означает, что strSQl может удерживать только значения даты/времени. Строка, такая как “INSERT INTO… whatever” не является значением Date/Time. Поэтому, когда вы strSQl сохранить такую строку в strSQl, Access жалуется, что типы данных несовместимы: “Тип несоответствия”.
Поскольку вы намерены strSQl хранить текст SQL-запроса, объявите его таким образом (вместо As Date): Dim strSQl As String
Это изменение приведет вас к ошибке. Для вашей более широкой цели используйте запрос параметров с DAO.
Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim StartUpdate As Date, EndUpdate As Date, LengthUpdate As Date Dim strSQl As String StartUpdate = Time() Call UpdateAll ‘This calls the collection of queries and is irrelevant for my problem EndUpdate = Time() LengthUpdate = EndUpdate — StartUpdate Forms!frm_Timer.Caption = «Update Completed at » & EndUpdate & » (» & Format(LengthUpdate, «HH:MM:SS») & «)» strSQl = «INSERT INTO tbl_DatabaseUpdateLog ([Start], [End], Duration) » & _ «VALUES ([p1], [p2], [p3]);» Set db = CurrentDb Set qdf = db.CreateQueryDef(vbNullString, strSQl) With qdf .Parameters(«p1»).Value = StartUpdate .Parameters(«p2»).Value = EndUpdate .Parameters(«p3»).Value = LengthUpdate End With qdf.Execute dbFailOnError DoCmd.Close acForm, Me.Name
Обратите внимание, что при таком подходе для INSERT формат даты даже не является проблемой. Поскольку StartUpdate, EndUpdate и LengthUpdate будут действительными значениями Дата/Время, вы можете просто передать их параметрам, не беспокоясь о формате и # разделителях.
Ответ №1
Я вижу, что вы используете INSERT INTO SELECT FROM (literal) когда простой INSERT INTO (columns) VALUES (values) будет проще. Вы также можете иметь несколько VALUES в одном операторе INSERT, который является опрятным.
Я также вижу, что вы храните денормализованные данные, ясно, что Duration можно получить из End — Start, поэтому вы можете отказаться от этого столбца.
Кроме того, избегайте венгерской нотации, в этом случае префикс имени таблицы с помощью tbl_. Это не 80-е годы. Не делай этого.
Содержание
- Ужасный короткий и простой ответ с использованием конкатенации строк:
- SQL Server
- Доступ /Jet
- Лучший ответ: используйте параметры
- SQL Server
- Доступ /Jet
Ужасный короткий и простой ответ с использованием конкатенации строк:
(Никогда не делайте этого)
SQL Server
Если вы используете SQL Server, вам нужно заключать даты в одинарные кавычки, и вам необходимо преобразовать их в однозначную строку формата даты, например, yyyy-MM-dd HH:mm:ss.
strSQL = «INSERT INTO neverDoThis VALUES ( ‘» & Format( startUpdate, «yyyy-MM-dd HH:mm:ss» & «‘ )»
Доступ /Jet
Если вы используете Access или Jet, вам нужно заключить даты в хэш-символы “#” и использовать формат “MM/dd/yyyy hh: mm: ss tt” (тупые американцы…).
strSQL = «INSERT INTO neverDoThis VALUES ( #» & Format( startUpdate, «MM/dd/yyyy hh:mm:ss tt» & «# )»
Здесь ссылка для функции ” Format: https://msdn.microsoft.com/en-us/library/office/gg251755.aspx?f=255&MSPPError=-2147217396
Лучший ответ: используйте параметры
(Делайте это вместо этого)
SQL Server
SQL Server использует именованные параметры, такие как @foo:
cmd.CommandText = «INSERT INTO databaseUpdateLog ( [Start], [End], [Duration] ) VALUES ( @start, @end, @duration )» cmd.Parameters.Append cmd.CreateParameter( «@start», , , startUpdate ) cmd.Parameters.Append cmd.CreateParameter( «@end», , , endUpdate ) cmd.Parameters.Append cmd.CreateParameter( «@duration», , , lengthUpdate )
Доступ /Jet
Доступ использует анонимные заполнители, к которым обращается заказ, который они добавили, используйте ‘?’:
cmd.CommandText = «INSERT INTO databaseUpdateLog ( [Start], [End], [Duration] ) VALUES ( ?, ?, ? )» cmd.Parameters.Append cmd.CreateParameter( «?», , , startUpdate ) cmd.Parameters.Append cmd.CreateParameter( «?», , , endUpdate ) cmd.Parameters.Append cmd.CreateParameter( «?», , , lengthUpdate )
Метод Append в VBA является Sub поэтому его вызов не использует скобки.
CreateParameter метод имеет 3 необязательные аргументы в середине: Type, Direction и Size, которые могут быть выведены из значений, поэтому они опущены, положив ,, ,.
Ответ №2
Более прямой метод использует собственный DAO:
Dim rs As DAO.Recordset Dim StartUpdate As Date Dim EndUpdate As Date Dim LengthUpdate As Date StartUpdate = Time Call UpdateAll ‘This calls the collection of queries and is irrelevant for my problem. EndUpdate = Time LengthUpdate = EndUpdate — StartUpdate Forms!frm_Timer.Caption = «Update Completed at » & EndUpdate & » (» & Format(LengthUpdate, «h:mm:ss») & «)» Set rs = CurrentDb.OpenRecordset(«Select Top 1 * From tbl_DatabaseUpdateLog») rs.AddNew rs!Start.Value = StartUpdate rs!End.Value = EndUpdate rs!Duration.Value = LengthUpdate rs.Update rs.Close Set rs = Nothing DoCmd.Close acForm, Me.Name Ответ №3
Чтобы вычислить только время суток, в VBA вы можете использовать это выражение:
CDate(Now — Date())
Включите результат в #, так как результат все еще является типом даты, хотя сохраняется только информация о времени.
Вы можете использовать функцию DateDiff для вычисления продолжительности, вам нужно выбрать соответствующие единицы. например:
DateDiff(DateInterval.Second, EndUpdate, StartUpdate)
В результате получается длинное целое число, поэтому вам не нужно заключать результат в ваш SQL.
BTW, параметризующий ваш SQL, рекомендуется, но не требуется.
