SQL Server использует пары одинарных кавычек для идентификации start и конец строки. Вставка "Mrs Brown's Boys" в таблицу базы данных завершится ошибкой, поскольку три одинарных кавычки подразумевают наличие двух строк, одна из которых неполная. Для ap требуется escape-символ.ostРоф после Брауна. В этой статье рассматривается использование настраиваемой функции VBA для устранения этой аномалии.
В этой статье предполагается, что у читателя отображается лента «Разработчик» и он знаком с редактором VBA. Если нет, погуглите «Excel Developer Tab» или «Excel Code Window».
Термин «база данных» здесь применяется к базам данных «промышленного уровня», таким как SQL Server и Oracle.
Пример рабочей тетради, используемой в этом упражнении, можно найти здесь.
Строка SQL
Включение приложенияostrophes (или одинарные кавычки) внутри оператора SQL обеспечивает следующую ошибку, возвращаемую менеджером базы данных (в данном случае для имени O'Dowd):
Требуется escape-символ, являющийся двойным apostrophe вместо одного. Таким образом, O"Dowd является приемлемым для базы данных. О'Дауд нет.
Функция
Где поля захвата могут предположительно содержать apostrophe, пользовательская функция может запускаться перед обновлением, заменяя одинарную кавычку на двойную.
- Откройте новую книгу;
- Назовите первый лист «Обновление» и заполните, как указано ниже, используя собственное имя базы данных и т. д. Эти поля будут использоваться для построения строки подключения к SQL Server.
- Откройте окно кода и вставьте модуль. Используйте пункты меню > Инструменты > Ссылки для ссылки на библиотеку ADO.rarх годов.
Скопируйте приведенный ниже код в модуль. Это подключение к базе данных.
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, "'") 'Найти позицию apostrophes Если x = 0 Then Exit For Если x > 0 Then strWord = Left(strWord, x - 1) & Chr(39) & Chr(39) & Right(strWord, Len(strWord) - (x)) End If Next п fRemoveApostrophe = strWord Конечная функция
- Игнорировать функцию.
Sub IgnoreFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B10") strSQL = "Вставить в tblCrewMember (LastName) значения ('" & strCriteria & "')" MsgBox strSQL & ". Эта запись SQL будет потерпеть неудачу; обратите внимание на три апostrophes." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Используйте функцию
Sub UseFunction() Call ConnectDatabase strCriteria = Sheets("Update").Range("B15") strCriteria = fRemoveApostrophe(strCriteria) strSQL = "Вставьте в tblCrewMember (LastName) значения ('" & strCriteria & "')" MsgBox strSQL & ". Эта запись SQL будет успешной и появится в таблице данных как O'Dowd." Debug.Print strSQL 'connDB.Execute (strSQL) End Sub
- Завершить Обновление ПО рабочий лист следующим образом, сtarзвон в камеру A8:
- Назначение кнопок макросам ИгнорФункция и ИспользованиеФункция соответственно
Результаты
Окно сообщения покажет результаты; в этом упражнении база данных физически не обновляется, но, если вы хотите это сделать, убедитесь, что имена полей совместимы с вашей базой данных, и добавьте оператор VBA conndb.execute(strSQL)
Восстановление после сбоев Excel
Excel склонен к сбою, когда на вашем компьютере заканчиваются ресурсы. Во время написания этого упражнения электронная таблица Excel, еще не сохраненная, зависла. Окно кода было частично адаптивным, что позволяло закрыть книгу целиком. Как оказалось, рабочая книга снова открылась нормально с полным содержимым и кодом. Если бы темпrary и исходные файлы были (слишком часто) повреждены, работу пришлось бы переделывать из-за отсутствия инструмента для устранения неполадок. xlsx урон. В данном случае это не имело большого значения, но могло стать потенциальной катастрофой для больших книг.
Об авторе:
Феликс Хукер — эксперт по восстановлению данных в DataNumen, Inc., которая является мировым лидером в области технологий восстановления данных, включая оправляться rar и программные продукты для восстановления sql. Для получения дополнительной информации посетите www.datanumen.com