Базата данни на Access е релационна система за управление на база данни, която ефективно спестява голямо количество данни по организиран начин. Където Excel е мощен инструмент за разбиване на данни в смислена информация. Excel обаче не може да съхранява твърде много данни. Но когато използваме Excel и Access заедно, силата на тези инструменти се увеличава експоненциално. Така че, нека научим как да свържем базата данни на Access като източник на данни към Excel чрез VBA.
Свързване на база данни за достъп като източник на данни Excel
1: Добавяне на препратка към обект с данни на AcitveX
Ще използваме ADO за свързване за достъп до базата данни. Така че първо трябва да добавим препратката към ADO обект.
Добавете модул към вашия VBA проект и кликнете върху инструментите. Тук щракнете върху препратките.
Сега потърсете Microsoft ActiveX Data Object Library. Проверете последната версия, която имате. Имам 6.1. Щракнете върху бутона OK и това е готово. Сега сме готови да създадем връзка към базата данни на Access.
2. Напишете VBA код, за да установите връзка с базата данни на Access
За да свържете Excel с база данни на Access, трябва да имате база данни на Access. Името на моята база данни е "Тествайте Database.accdb ". Запазва се в "C: \ Users \ Manish Singh \ Desktop" местоположение. Тези две променливи са важни. Ще трябва да ги промените според вашите нужди. Кодът за почивка може да се запази такъв, какъвто е.
Копирайте кода по -долу, за да направите вашия Excel VBA модул и да направите промени според вашите изисквания. Обясних всеки ред от кода по -долу:
Под ADO_Connection () 'Създаване на обекти за връзка и набор от записи Dim conn като нова връзка, запис като нов набор от записи Dim DBPATH, PRVD, connString, заявка като низ „Деклариране на напълно квалифицирано име на база данни. Променете го с местоположението и името на вашата база данни. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" „Това е доставчикът на връзка. Запомнете това за интервюто си. PRVD = "Microsoft.ace.OLEDB.12.0;" „Това е низът за връзка, който ще ви е необходим при отваряне на връзката. connString = "Доставчик =" & PRVD & "Източник на данни =" & DBPATH 'отваряне на връзката conn.Open connString „заявката, която искам да изпълня в базата данни. query = "SELECT * from customerT;" 'стартиране на заявката на отворената връзка. Той ще получи всички данни в рек обект. rec.Отваряне на заявка, свърз „изчистване на съдържанието на клетките Cells.ClearContents „получаване на данни от набора от записи, ако има такива, и отпечатването им в колона А на Excel лист. Ако (rec.RecordCount 0) След това Направете, докато не rec.EOF Диапазон ("A" & клетки (Rows.Count, 1). Край (xlUp) .Row). Отместване (1, 0) .Value2 = _ rec.Fields (1) .Запазване на стойността.MoveNext Loop End If 'затваряне на връзките rec.Close conn.Close End Sub
Копирайте горния код или изтеглете файла по -долу и направете промени във файла според вашите изисквания.
Изтегляне на файл: VBA Database LearningКогато стартирате този VBA код, Excel ще установи връзка с базата данни. След това той ще изпълни проектираната заявка. Той ще изчисти всяко старо съдържание на листа и ще запълни колоната А със стойности на поле 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
Този ред отваря връзката с базата данни. Open е функцията на обекта на връзка, която приема няколко аргумента. Първият и необходим аргумент е ConnectingString. Този низ съдържа доставчика на OLE DB (тук PRVD) и източника на данни (тук DBPATH). Той може също да приема администратор и парола като незадължителни аргументи за защитени бази данни.
Синтаксисът на Connection.Open е:
connection.open ([ConnectionString като низ], [UserID като низ], [Парола като низ], [Опции като дълъг = -1])
Тъй като нямам идентификатор и парола в базата си данни, използвам само ConnectionString. Форматът на ConnectionString е „Доставчик =доставчик_, който искате да използвате; Източник на данни =напълно квалифицирано име на база данни"Ние направихме и запазихме този низ вconnString променлива.
query = "SELECT * from customerT;"
Това е заявката, която искам да изпълня в базата данни. Можете да имате всякакви заявки, които искате.
rec.Отваряне на заявка, свърз
Този израз изпълнява дефинираната заявка в дефинираната връзка. Тук използваме метода Open на обект набор от записи. Целият изход се записва в обекта набор от записирек. Можете да извличате манипулиране или изтриване на стойности от обекта набор от записи.
Cells.ClearContents
Този ред изчиства съдържанието на листа. С други думи, изтрива всичко от клетките на листа.
Ако (rec.RecordCount 0) След това Направете, докато не е rec.EOF Range ("A" & Cells (Rows.Count, 1) .End (xlUp) .Row). Offset (1, 0) .Value2 = _ rec.Fields (1) .Запазване на стойността.MoveNext Loop End If
Горният набор от редове проверява дали наборът от записи е празен или не. Ако наборът от записи не е празен (това означава, че заявката е върнала някои записи), цикълът започва и започва да отпечатва всяка стойност на поле 1 (второ поле, първо име в този случай) в последната неизползвана клетка в колоната.
(Това се използва само за обяснение. Може да нямате тези редове. Ако просто искате да отворите връзка към базата данни, кодът VBA над тези редове е достатъчен.)
Използвахме rec.EOF, за да стартираме цикъла до края на набора от записи. Rec.MoveNext се използва за преминаване към следващия набор от записи. rec.Fields (1) се използва за получаване на стойности от поле 1 (което е второ, тъй като индексирането на неговото поле започва от 0. В моята база данни второто поле е първото име на клиента).
rec.Close conn.Close
И накрая, когато цялата работа, която искахме от rec и conn, приключи, ние ги затваряме.
Може да имате тези редове в отделна подпрограма, ако искате да отваряте и затваряте отделни връзки поотделно.
Така че да, момчета, по този начин установявате връзка с базата данни ACCESS с помощта на ADO. Има и други методи, но това е най -лесният начин да се свържете с източник на данни за достъп чрез VBA. Обясних го възможно най -подробно. Кажете ми дали това е било полезно в раздела за коментари по -долу.
Свързани статии:
Използвайте затворена работна книга като база данни (DAO), използвайки VBA в Microsoft Excel | За да използвате затворена работна книга като база данни с DAO връзка, използвайте този фрагмент VBA в Excel.
Използвайте затворена работна книга като база данни (ADO), използвайки VBA в Microsoft Excel | За да използвате затворена работна книга като база данни с ADO връзка, използвайте този фрагмент VBA в Excel.
Първи стъпки с потребителските формуляри на Excel VBA | За да вмъкнем данни в базата данни, използваме формуляри. Потребителските формуляри на Excel са полезни за получаване на информация от потребителя. Ето как трябва да започнете с потребителските форми на VBA.
Променете стойността/съдържанието на няколко контроли на UserForm с помощта на VBA в Excel | За да промените съдържанието на контролите на потребителската форма, използвайте този прост VBA фрагмент.
Предотвратете затварянето на потребителска форма, когато потребителят кликне върху бутона x, като използва VBA в Excel | За да предотвратим затварянето на потребителската форма, когато потребителят кликне върху бутона x на формуляра, използваме събитие UserForm_QueryClose.
Популярни статии:
50 преки пътища в Excel за повишаване на вашата производителност | Бъдете по -бързи в задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Функцията VLOOKUP в Excel | Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
COUNTIF в Excel 2016 | Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да преброите конкретна стойност. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel | Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.