Об’єкт ction у VBA

Об'єкт ADO.Connection у VBA, властивість ConnectionString, генерація рядка підключення, відкриття та закриття з'єднання з базою даних, об'єкт ADOError та колекція Errors

Створення об'єкта Connection робиться дуже просто. Наприклад, щоб підключитися до бази даних Northwind на сервері SQL Server з ім'ям LONDON, можна використовувати код виду

Dim cn As New ADODB.Connection

cn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _

В принципі, цього цілком достатньо, щоб підключитися до бази даних і створити об'єкт з'єднання, який потім можна буде використовувати. Однак у більшості користувачів виникає питання: а що таке написано у властивості ConnectionString і як значення цієї властивості можна написати самому?

Найпростіший варіант взагалі нічого самому не писати. Значення цієї властивості можна згенерувати в автоматичному режимі. Виглядає це дуже просто:

Створюємо будь-який порожній файл (наприклад, текстовий). Для цього потрібно просто клацнути правою кнопкою миші по порожньому місці у вікні провідника Windows і в контекстному меню вибратиNew (Новий) ->Text Document (Текстовий документ) - див. рис. 9.2

ction

Мал. 9.2 Створення порожнього текстового файлу у Windows Explorer

Перейменовуємо цей файл так, щоб він мав розширення UDL (від User Data Link). Після перейменування переконайтеся, що іконка змінилася (див. рис. 9.3).

Connection

Мал. 9.3 Тепер у нас є порожній файл 1.udl

Якщо вона залишилася такою самою, як була для текстового документа, це означає, що реальне розширення для цього файлу - .txt, а не .udl. У цьому випадку потрібно у вікні Windows Explorer у менюTools (Сервіс) вибратиFolderOptions (Параметрипапок), перейти на вкладкуView (Перегляд) і зняти прапорецьHide file extensionsfor known file types (Приховувати розширення для відомих типів файлів). Після того, як ви виконаєте цю операцію, потрібно буде перейменувати файл, щоб у нього було розширення *.udl.

Після того, як файл UDL буде створений, просто клацніть по ньому двічі мишею. Відкриється вікно із чотирма вкладками.

На першій вкладціProvider виберіть тип бази даних (наприклад,Microsoft OLE DB Provider for SQL Server ). Для підключення до бази даних Oracle виберітьMicrosoftOLEDBProviderforOracle. Для підключення до бази даних Access необхідно вибратиMicrosoft JET 4.0 OLE DB Provider. Про підключення до листа Excel як до бази даних ми поговоримо окремо.

Далі потрібно перейти на вкладку Connection. Для кожного типу баз даних ця вкладка виглядає по своєму. Наприклад, для SQL Server вона виглядає так, як представлено на рис. 9.4, а для Access - так, як на рис. 9.5.

ction

Мал. 9.4 Вікно властивостей з'єднання для підключення до SQL Server

ction

Мал. 9.5 Вікно властивостей з'єднання для підключення до бази даних Access

Якщо ви не знаєте, які параметри потрібно вводити в цьому вікні, їх доведеться запитати у вашого адміністратора бази даних. Після введення всіх параметрів рекомендується натиснути на кнопкуTest Connection, щоб перевірити можливість підключення до бази даних. Після цього потрібно натиснути кнопкуOK, щоб закрити вікно властивостей з'єднання.

Остання дія, яку нам потрібно зробити - клацнути правою кнопкою миші по створеному файлу, у контекстному меню вибратиOpenWith (Відкрити за допомогою) ->ChooseProgram (Вибрати програму) і в списку вибрати Notepad (Блокнот) і натиснути на кнопкуOK. Створений файл відкриється в блокноті. У менюFormat у блокноті зніміть прапорецьWordwrap (Перенесення рядками) і скопіюйте в буфер обміну останній рядок цього файлу (див. мал. 9. 6) .

Мал. 9.6 Копіюємо згенерований рядок підключення

Звичайно, ви можете написати connection string (українською — рядок підключення) і вручну. Це просто набір параметрів виду "властивість = значення", розділених крапкою з комою (для значення у рядку підключення лапки не використовуються). Що означає кожен із параметрів:

  • Provider – драйвер для підключення до джерела даних. До кожного типу джерела даних (SQL Server, Access, Oracle) він свій. Ми використовуємо драйвер OLE DB. Альтернатива йому застосування драйверів ODBC. Вони працюють повільніше і в основному використовуються для забезпечення зворотної сумісності, але іноді без них не обійтися (наприклад, тоді коли відповідного драйвера OLE DB просто немає). Ми будемо використовувати підключення ODBC до таблиці на аркуші Excel.

У принципі, у рядку підключення значення для Provider можна взагалі не вказувати. Але тоді доведеться визначити його як значення окремої властивості об'єкта Connection.

  • IntegratedSecurity — ця властивість використовується, оскільки ми використовуємо для підключення до SQL Server автентифікацію Windows. Якби ми використовували автентифікацію SQL Server, то ця властивість нам була б не потрібна, але замість нього нам потрібно було б використовувати дві інші властивості: User ID — ідентифікатор користувача та Password — пароль. Якщо ви далекі від світу баз даних, просто дізнайтеся необхідні значення у вашогоадміністратора.
  • DataSource – ім'я джерела даних. У нашому випадку це ім'я комп'ютера, на якому працює SQL Server. В інших випадках воно може бути ім'ям екземпляра Oracle або файлу бази даних Microsoft Access — все залежить від того, до якої бази даних ви підключаєтеся.
  • InitialСatalog - ім'я бази даних на цьому сервері. У нашому випадку – Northwind.

Коли ви генеруєте рядок підключення автоматично за допомогою файлу UDL, це може здатися довгим. Насправді це займає не більше хвилини. Крім того, ви гарантуєте, що в рядку підключення не буде помилок, і у вас є можливість перевірити підключення до бази даних прямо з властивостей файлу UDL (кнопкаTest Connection ).

Фактично все, що потрібно для відкриття з'єднання з базою даних, ми зробили: створили об'єкт Connection, налаштували йому властивість ConnectionString і викликали метод Open(). Однак для довідки (оскільки не всі знають англійську) наведемо інформацію про властивості та методи цього об'єкта.

  • Властивість Provider дозволяє визначити драйвер, який буде використано для підключення до бази даних. Ми з вами визначили такий драйвер усередині значення ConnectionString, але для цієї мети можна використовувати й окрему властивість. Значення властивостей Provider для підключення до різних джерел даних можуть мати такий вигляд:
  • "Microsoft.Jet.OLEDB.4.0" - для підключень до файлів Access та Excel та інших джерел даних на основі Jet;
  • "SQLOLEDB" - для підключень до SQL Server (як у прикладі)
  • "MSDAORA.1" - для підключень до сервера Oracle;
  • "ADsDSOObject" - для підключення до бази даних служби каталогів Windows.
  • Властивість ConnectionString - головна властивість об'єктаConnection. Воно визначає параметри підключення джерела. Як саме працювати з ним, ми розглянули вище.
  • Метод Open() дозволяє відкрити з'єднання з базою даних. Рядок підключення можна не налаштовувати окремо як властивість об'єкта Connection, а просто передавати його методу як параметр.
  • метод Close() дозволяє закрити з'єднання. Зверніть увагу, що об'єкт з'єднання при цьому з пам'яті не видаляється. Щоб повністю позбавитися цього об'єкта, можна використовувати код

    • Set cn = Nothing

    або просто Set cn = Nothing – розрив з'єднання відбудеться автоматично.

    Для цього об'єкта передбачено безліч інших властивостей та методів, проте тут вони не розглядатимуться (за додатковою інформацією можна звернутися до документації або навчальних курсів Microsoft). Єдина властивість, яку обов'язково необхідно розглянути, — це властивість Errors, яка повертає колекцію об'єктів Error — помилок. Помилки при встановленні або роботі з'єднання зустрічаються дуже часто (неправильно введено пароль або ім'я користувача, у користувача недостатньо прав на підключення, неможливо звернутися до комп'ютера по мережі тощо), тому рекомендується реалізовувати в програмі обробку помилок. Найпростіший варіант реалізації обробника помилок може мати такий вигляд:

    Dim cn As ADODB.Connection

    Set cn = CreateObject("ADODB.Connection")

    cn.ConnectionString = "User ID=SA;Password=password;Data Source = LONDON1;" _

    & "Initial Catalog = Northwind"

    On Error GoTo CnErrorHandler

    For Each ADOErr In cn.Errors

    На практиці перехоплюються помилки, характерні для цього підключення (немає файлу Access, помилка пароля або імені користувача при підключенні до SQL Server, немає прав,файл відкритий у винятковому режимі тощо). та користувачеві пропонується виправити помилку.

    Найважливіші властивості об'єкта ADOError:

    • Description – опис помилки. Зазвичай найважливіша інформація міститься саме у описі.
    • Number – номер помилки. За номером зручно проводити пошук у базі знань та в Інтернет.
    • Source – джерело помилки. Ця інформація корисна лише в тому випадку, якщо в колекції Errors можуть бути помилки з різних джерел.
    • SQLState і NativeError — інформація про помилку, що прийшла з SQL-сумісного джерела даних.