SQL Server Для обозначения начала и конца строки используются пары одинарных кавычек. Вставка строки «Mrs Brown's Boys» в таблицу базы данных завершится неудачей, поскольку три одинарные кавычки подразумевают две строки, одна из которых неполная. Для заполнения апострофа после имени Брауна требуется экранирующий символ. В этой статье рассматривается использование специально разработанной функции VBA для решения этой проблемы.
В этой статье предполагается, что у читателя отображается лента «Разработчик» и он знаком с редактором VBA. Если нет, погуглите «Excel Developer Tab» или «Excel Code Window».
Термин «база данных» здесь применяется к базам данных «промышленного уровня», таким как SQL Server и Oracle.
Пример рабочей тетради, используемой в этом упражнении, можно найти здесь.
Строка SQL
Включение апострофов (или одинарных кавычек) в SQL-запрос приводит к следующей ошибке, возвращаемой менеджером базы данных (в данном случае для имени O'Dowd):
Для экранирования необходим символ — двойной апостроф вместо одинарного. Таким образом, O”Dowd допустимо для базы данных. O'Dowd — нет.
Функция
В тех случаях, когда поля ввода могут содержать апостроф, можно создать пользовательскую функцию, которая будет срабатывать перед обновлением, заменяя одинарную кавычку двойной.
- Откройте новую книгу;
- Назовите первый лист «Обновление» и заполните, как указано ниже, используя собственное имя базы данных и т. д. Эти поля будут использоваться для построения строки подключения к SQL Server.
- Откройте окно кода и вставьте модуль. Используйте пункты меню > Инструменты > Ссылки, чтобы добавить ссылки на библиотеки ADO.
Скопируйте приведенный ниже код в модуль. Это подключение к базе данных.
Public connDB As New ADODB.Connection Public rs As New ADODB.Recordset Public strSQL As String Public strCriteria As String Sub ConnectDatabase() Если connDB.State = 1 Then connDB.Close On Error GoTo ErrConnect Dim strServer, strDBase, strUser, strPWD As String strServer = Sheets("Update").Range("B2") strDBase = Sheets("Update").Range("B3") strUser = Sheets("Update").Range("B4") strPWD = Sheets(" Обновить").Range("B5") Если strPWD > "" Тогда strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & ";Время ожидания подключения=30;" Else strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" & strDBase 'Аутентификация Windows End If connDB.ConnectionTimeout = 30 connDB.Open strConnectionstring Exit Sub ErrConnect: MsgBox Err.Description End Sub
- Добавьте функцию в модуль:
Функция fRemoveApostrophe(strWord As String) Dim n As Integer Dim x As Integer x = 0 For n = 0 To 100 x = InStr(x + 2, strWord, "'") 'Найти позиции апострофов If x = 0 Then Exit For If x > 0 Then strWord = Left(strWord, x - 1) & Chr(39) & Chr(39) & Right(strWord, Len(strWord) - (x)) End If Next n fRemoveApostrophe = strWord End Function
- Игнорировать функцию.
Sub IgnoreFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B10") strSQL = "Insert into tblCrewMember (LastName) values ('" & strCriteria & "')" MsgBox strSQL & ". This SQL entry will fail; note the three apostrophles." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Используйте функцию
Sub UseFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B15") strCriteria = fRemoveApostrophe(strCriteria) strSQL = "Insert into tblCrewMember (LastName) values ('" & strCriteria & "')" MsgBox strSQL & ". This SQL entry will succeed, and appear in datatable as O'Dowd." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Завершить Обновление ПО Рабочий лист выглядит следующим образом, начиная с ячейки A8:
- Назначение кнопок макросам ИгнорФункция и ИспользованиеФункция соответственно
Результаты
Окно сообщения покажет результаты; в этом упражнении база данных физически не обновляется, но, если вы хотите это сделать, убедитесь, что имена полей совместимы с вашей базой данных, и добавьте оператор VBA conndb.execute(strSQL)
Восстановление после сбоев Excel
Excel часто зависает, когда на компьютере заканчиваются ресурсы. Во время выполнения этого упражнения электронная таблица Excel, ещё не сохранённая, зависла. Окно кода работало лишь частично, что позволило закрыть всю рабочую книгу целиком. Как оказалось, рабочая книга открылась нормально, и содержимое и код были в полном объёме. Если бы временные и исходные файлы (что случается слишком часто) были повреждены, работу пришлось бы переделывать без инструмента для решения этой проблемы. xlsx урон. В данном случае это не имело большого значения, но могло стать потенциальной катастрофой для больших книг.
Об авторе:
Феликс Хукер — эксперт по восстановлению данных в DataNumen, Inc., которая является мировым лидером в области технологий восстановления данных, включая восстановить rar и программные продукты для восстановления sql. Для получения дополнительной информации посетите www.datanumen.com