SQL Server використовує пари одинарних лапок для позначення початку та кінця рядка. Вставка «Mrs Brown's Boys» у таблицю бази даних не вдасться, оскільки три одинарні лапки означають два рядки, один з яких є неповним. Для апострофа після Brown потрібен escape-символ. У цій статті розглядається використання налаштованої функції VBA для вирішення цієї аномалії.
У цій статті передбачається, що на пристрої для читання відображається стрічка розробника та він знайомий з редактором VBA. Якщо ні, будь ласка, перегляньте Google “Вкладка розробника Excel” або “Вікно коду Excel”.
Термін "база даних" тут застосовується до таких баз даних "промислової потужності", як SQL Server та Оракл.
Можна знайти приклад робочої книги, використаної в цій вправі тут.
Рядок 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 () If connDB.State = 1 Потім connDB.Close On Error GoTo ErrConnect Dim strServer, strDBase, strUser, strPWD, strPser strServer = Аркуші ("Оновлення"). Діапазон ("B2") strDBase = Аркуші ("Оновлення"). Діапазон ("B3") strUser = Аркуші ("Оновлення"). Діапазон ("B4") strPWD = Аркуші (" Оновлення "). Діапазон (" B5 ") Якщо strPWD>" "Тоді strConnectionstring =" DRIVER = {SQL Server}; Server = "& strServer &"; База даних = "& strDBase &"; Uid = "& strUser &"; Pwd = "& strPWD &"; Час очікування підключення = 30; "Інше strConnectionstring =" DRIVER = {SQL Server}; SERVER = "& strServer &"; Trusted_Connection = так; DATABASE = "& strDBase 'Завершення автентифікації Windows Якщо connDB.ConnectionTimeout = 30 connDB.Open strConnectionstring Вийти з Sub ErrConnect: MsgBox Err.Description End Sub 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 = "Вставити в tblCrewMember (LastName) значення ('" & strCriteria & "')" MsgBox strSQL & ". Цей запис SQL завершиться невдачею; зверніть увагу на три апострофи." 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
- Завершити Оновити робочий аркуш наступним чином, починаючи з комірки A8:
- Призначте кнопки макросам Ігнорувати функцію та Використовуйте функцію повторно
Результати
У вікні повідомлення відображатимуться результати; жодна база даних фізично не оновлюється в цій вправі, але, якщо ви хочете це зробити, переконайтеся, що імена полів сумісні з вашою базою даних, і додайте використання оператора VBA conndb.execute (strSQL)
Відновлення після аварій Excel
Excel схильний до аварійного завершення роботи, коли на комп'ютері закінчуються ресурси. Під час написання цієї вправи електронна таблиця Excel, яка ще не була збережена, зависла. Вікно коду частково реагувало, що дозволяло закрити книгу повністю. Як виявилося, книга знову відкрилася нормально з повним вмістом і кодом. Якби тимчасові та вихідні файли були (занадто часто) пошкоджені, роботу довелося б переробляти за відсутності інструменту для вирішення проблеми. xlsx пошкодження. Це мало мало значення в цьому випадку, але могло б стати потенційною катастрофою для великих книг.
Вступ автора:
Фелікс Хукер - фахівець з відновлення даних у DataNumen, Inc., яка є світовим лідером у галузі технологій відновлення даних, в тому числі відновити rar та програмні продукти для відновлення sql. Для отримання додаткової інформації відвідайте WWW.datanumen.com