Как подключить Excel в базу данных Access с помощью VBA
База данных Access — это система управления реляционными базами данных, которая эффективно сохраняет большой объем данных в организованном порядке. Где Excel — мощный инструмент для преобразования данных в значимую информацию. Однако Excel не может хранить слишком много данных. Но когда мы используем Excel и Access вместе, мощность этих инструментов возрастает в геометрической прогрессии. Итак, давайте узнаем, как подключить базу данных Access в качестве источника данных к Excel через VBA.
Подключение базы данных Access как источника данных Excel
1: Добавить ссылку на объект данных AcitveX
Мы будем использовать ADO для подключения для доступа к базе данных. Итак, сначала нам нужно добавить ссылку на объект ADO.
Добавьте модуль в свой проект VBA и щелкните инструменты. Здесь нажмите на ссылки.
Теперь найдите библиотеку объектов данных Microsoft ActiveX. Проверьте последнюю версию, которая у вас есть. У меня 6.1. Нажмите кнопку ОК, и готово. Теперь мы готовы создать ссылку на базу данных Access.
2. Напишите код VBA для установления соединения с базой данных Access
Чтобы подключить Excel к базе данных Access, у вас должна быть база данных Access. Имя моей базы данных — «Test Database.accdb». Он сохраняется в папке «C: \ Users \ Manish Singh \ Desktop». Эти две переменные важны. Вам нужно будет изменить их в соответствии с вашими потребностями. Остальной код можно оставить как есть.
Скопируйте приведенный ниже код, чтобы создать модуль Excel VBA и внести изменения в соответствии с вашими требованиями. Я объяснил каждую строку кода ниже:
Sub ADO_Connection()
'Creating objects of Connection and Recordset
Dim conn As New Connection, rec As New Recordset
Dim DBPATH, PRVD, connString, query As String
'Declaring fully qualified name of database. Change it with your database's location and name.
DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb"
'This is the connection provider. Remember this for your interview.
PRVD = "Microsoft.ace.OLEDB.12.0;"
'This is the connection string that you will require when opening the the connection.
connString = "Provider=" & PRVD & "Data Source=" & DBPATH
'opening the connection
conn.Open connString
'the query I want to run on the database.
query = "SELECT * from customerT;"
'running the query on the open connection. It will get all the data in the rec object.
rec.Open query, conn
'clearing the content of the cells
Cells.ClearContents
'getting data from the recordset if any and printing it in column A of excel sheet.
If (rec.RecordCount <> 0) Then
Do While Not rec.EOF
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
rec.Fields(1).Value
rec.MoveNext
Loop
End If
'closing the connections
rec.Close
conn.Close
End Sub
Скопируйте приведенный выше код или загрузите файл ниже и внесите в него изменения в соответствии с вашими требованиями.
`link: /wp-content-uploads-2020-02-VBA-Database-Learning.xls [__ Загрузить файл: Обучение базам данных VBA]
Когда вы запустите этот код VBA, Excel установит соединение с базой данных. После этого он выполнит разработанный запрос. Он очистит все старое содержимое на листе и заполнит столбец A значениями поля 1 (второе поле) базы данных.
Как работает подключение к базе данных VBA Access?
Dim conn As New Connection, rec As New Recordset
В приведенной выше строке мы не просто объявляем переменные Connection и recordset, но инициализируем их напрямую с помощью ключевого слова New.
DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Эти две строчки — участники. DBPATH изменится только с вашей базой данных. PRVD подключает поставщика OLE DB.
conn.Open connString
Эта строка открывает соединение с базой данных. Открыть — это функция объекта подключения, которая принимает несколько аргументов. Первый и необходимый аргумент — ConnectingString. Эта строка содержит поставщика OLE DB (здесь PRVD) и источник данных (здесь DBPATH). Он также может принимать admin и пароль в качестве дополнительных аргументов для защищенных баз данных.
Синтаксис Connection.Open:
connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long=-1])
Поскольку в моей базе данных нет идентификатора и пароля, я использую только ConnectionString. Формат ConnectionString: «Provider = provider_you want to use; _ Data Source = _ полностью квалифицированное имя базы данных». Мы сделали и сохранили эту строку в переменной connString.
query = "SELECT * from customerT;"
Это запрос, который я хочу выполнить в базе данных. У вас могут быть любые запросы.
rec.Open query, conn
Этот оператор запускает определенный запрос в определенном соединении. Здесь мы используем метод Open объекта набора записей. Весь вывод сохраняется в наборе записей objectrec. Вы можете получать, изменять или удалять значения из объекта набора записей.
Cells.ClearContents
Эта строка очищает содержимое листа. Другими словами, удаляет все из ячеек листа.
If (rec.RecordCount <> 0) Then
Do While Not rec.EOF
Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _
rec.Fields(1).Value
rec.MoveNext
Loop
End If
Приведенный выше набор строк проверяет, пуст ли набор записей. Если набор записей не пуст (это означает, что запрос вернул несколько записей), цикл начинается и начинает печатать каждое значение поля 1 (второе поле, в данном случае имя) в // диапазонах-ячейках-строк-и-столбцах-в- vba / 3-best-sizes-to-last-non-blank-row-and-column-using-vba.html [последняя неиспользованная ячейка в столбце] `.
(Это используется только для объяснения. У вас может не быть этих строк. Если вы просто хотите открыть соединение с базой данных, тогда кода VBA над этими строками будет достаточно.)
Мы использовали rec.EOF для выполнения цикла до конца набора записей. Rec.MoveNext используется для перехода к следующему набору записей. rec.Fields (1) используется для получения значений из поля 1 (которое является вторым, поскольку его индексирование полей начинается с 0. В моей базе данных второе поле — это имя клиента).
rec.Close conn.Close
Наконец, когда вся работа, которую мы хотели от rec и conn, сделана, мы закрываем их.
У вас могут быть эти строки в отдельной подпрограмме, если вы хотите отдельно открывать и закрывать определенные соединения.
Итак, ребята, вот как вы устанавливаете соединение с базой данных ACCESS с помощью ADO. Есть и другие методы, но это самый простой способ подключиться к источнику данных доступа через VBA. Я объяснил это как можно подробнее. Сообщите мне, было ли это полезно, в разделе комментариев ниже.
Статьи по теме:
`link: / import-and-export-in-vba-use-a-closed-workbook-as-a-database-dao-using-vba-in-microsoft-excel [Использовать закрытую книгу как базу данных (DAO) с использованием VBA в Microsoft Excel] `| Чтобы использовать закрытую книгу в качестве базы данных с подключением к DAO, используйте этот фрагмент VBA в Excel.
`link: / import-and-export-in-vba-use-a-closed-workbook-as-a-database-ado-using-vba-in-microsoft-excel [Использовать закрытую книгу как базу данных (ADO) с использованием VBA в Microsoft Excel] `| Чтобы использовать закрытую книгу в качестве базы данных с подключением ADO, используйте этот фрагмент VBA в Excel.
link: / applications-word-outlook-in-vba-Getting-started-with-excel-vba-userforms [Начало работы с пользовательскими формами Excel VBA] | * Для вставки данных в базу данных мы используем формы. Пользовательские формы Excel полезны для получения информации от пользователя. Вот как вам следует начать с пользовательских форм VBA.
===
`link: / user-forms-input-box-in-vba-change-the-valuecontent-of-нескольких-userform-controls-using-vba-in-microsoft-excel [Изменить значение / содержимое нескольких элементов управления UserForm использование VBA в Excel] `| * Чтобы изменить содержимое элементов управления пользовательской формы, используйте этот простой фрагмент кода VBA.
`link: / user-forms-input-box-in-vba-prevent-a-userform-from-closed-when-the-user-clicks-the-x-button-using-vba-in-microsoft-excel [ Предотвратить закрытие пользовательской формы, когда пользователь нажимает кнопку x, используя VBA в Excel] `| Чтобы предотвратить закрытие пользовательской формы, когда пользователь нажимает кнопку x формы, мы используем событие UserForm_QueryClose.
Популярные статьи:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-повышения-продуктивность [50 сочетаний клавиш Excel для повышения производительности] | Выполняйте свою задачу быстрее. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.
link: / формулы-и-функции-введение-функции-vlookup [Функция ВПР в Excel] | Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листов.
link: / tips-countif-in-microsoft-excel [COUNTIF в Excel 2016] | Подсчитайте значения с условиями, используя эту удивительную функцию. Вам не нужно фильтровать данные для подсчета определенного значения.
Функция Countif важна для подготовки вашей приборной панели.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel] | Это еще одна важная функция приборной панели. Это поможет вам суммировать значения для конкретных условий.