Access datu bāze ir relāciju datu bāzes pārvaldības sistēma, kas organizētā veidā efektīvi ietaupa lielu datu apjomu. Kur Excel ir spēcīgs rīks, lai saspiestu datus nozīmīgā informācijā. Tomēr programmā Excel nevar saglabāt pārāk daudz datu. Bet, lietojot Excel un Access kopā, šo rīku jauda palielinās eksponenciāli. Tātad, iemācīsimies savienot Access datu bāzi kā datu avotu ar Excel, izmantojot VBA.
Piekļuves datu bāzes savienošana kā datu avots Excel
1: Pievienojiet atsauci AcitveX datu objektam
Lai piekļūtu datu bāzei, mēs izmantosim ADO. Tātad vispirms mums jāpievieno atsauce uz ADO objektu.
Pievienojiet VBA projektam moduli un noklikšķiniet uz rīkiem. Šeit noklikšķiniet uz atsaucēm.
Tagad meklējiet Microsoft ActiveX datu objektu bibliotēku. Pārbaudiet savu jaunāko versiju. Man ir 6.1. Noklikšķiniet uz pogas Labi, un tas ir izdarīts. Tagad mēs esam gatavi izveidot saiti uz piekļuves datu bāzi.
2. Uzrakstiet VBA kodu, lai izveidotu savienojumu ar piekļuves datu bāzi
Lai Excel savienotu ar Access datu bāzi, jums ir jābūt Access datu bāzei. Manas datu bāzes nosaukums ir "Pārbaudīt datubāzi.accdb ". Tas tiek saglabāts plkst "C: \ Users \ Manish Singh \ Desktop" atrašanās vietu. Šie divi mainīgie ir svarīgi. Jums tie būs jāmaina atbilstoši jūsu vajadzībām. Atpūtas kodu var saglabāt tādu, kāds tas ir.
Nokopējiet zemāk esošo kodu, lai izveidotu savu Excel VBA moduli un veiktu izmaiņas atbilstoši savām prasībām. Es izskaidroju katru koda rindu zemāk:
ADO_Savienojums () 'Savienojuma un ierakstu kopas objektu izveide Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, vaicājums Kā virkne “Pilnībā kvalificēta datu bāzes nosaukuma deklarēšana. Mainiet to, norādot datubāzes atrašanās vietu un nosaukumu. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" "Šis ir savienojuma nodrošinātājs. Atcerieties to intervijai. PRVD = "Microsoft.ace.OLEDB.12.0;" 'Šī ir savienojuma virkne, kas jums būs nepieciešama, atverot savienojumu. connString = "Provider =" & PRVD & "Datu avots =" & DBPATH 'atverot savienojumu conn. Atvērt connString 'vaicājumu, kuru vēlos palaist datu bāzē. query = "SELECT * no customerT;" "izpildot vaicājumu par atvērto savienojumu. Tas iegūs visus datus rec objekts. rec. Atvērt vaicājumu, savienot 'šūnu satura notīrīšana Šūnas. Skaidrs saturs “iegūt datus no ierakstu kopas, ja tāda ir, un izdrukāt tos Excel lapas A slejā. Ja (rec.RecordCount 0) Tad dariet, kamēr nav rec.EOF diapazons ("A" un šūnas (Rows.Count, 1). End (xlUp) .Row) .Pārvietojums (1, 0) .Vērtība2 = _ rec.Fields (1). Vērtība rec.MoveNext Loop End If 'slēdzot savienojumus rec. Aizvērt savienojumu. Aizvērt beigu apakšdaļu
Kopējiet iepriekš minēto kodu vai lejupielādējiet tālāk esošo failu un veiciet izmaiņas failā atbilstoši savām prasībām.
Lejupielādēt failu: VBA Database LearningPalaižot šo VBA kodu, programma Excel izveidos savienojumu ar datu bāzi. Pēc tam tas izpildīs izstrādāto vaicājumu. Tas izdzēsīs visu veco lapas saturu un aizpildīs kolonnu A ar datu bāzes 1. lauka (otrā lauka) vērtībām.
Kā darbojas šis VBA piekļuves datu bāzes savienojums?
Dim conn As New Connection, rec As New Recordset
Iepriekšējā rindā mēs ne tikai deklarējam savienojuma un ierakstu kopas mainīgos, bet arī inicializējam to tieši, izmantojot Jauns atslēgvārds.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Šīs divas līnijas ir konkursanti. DBPATH mainīsies tikai ar jūsu datu bāzi. PRVD savieno OLE DB nodrošinātāju.
conn. Atvērt connString
Šī rinda atver savienojumu ar datu bāzi. Atvērt ir savienojuma objekta funkcija, kurai ir vairāki argumenti. Pirmais un nepieciešamais arguments ir ConnectingString. Šī virkne satur OLE DB nodrošinātāju (šeit PRVD) un datu avotu (šeit DBPATH). Tas var arī izmantot administratoru un paroli kā papildu argumentus aizsargātām datu bāzēm.
Savienojuma sintakse. Atvērts ir:
connection.open ([Savienojuma virkne kā virkne], [Lietotāja ID kā virkne], [Parole kā virkne], [Iespējas kā garš = -1])
Tā kā manā datu bāzē nav neviena ID un paroles, es izmantoju tikai ConnectionString. ConnectionString formāts ir "Provider =pakalpojumu sniedzējs_ vēlaties izmantot; Datu avots =pilnībā kvalificēts datu bāzes nosaukumsMēs izveidojām un saglabājām šo virkniconnString mainīgais.
query = "SELECT * no customerT;"
Šis ir vaicājums, kuru vēlos palaist datu bāzē. Jums var būt kādi jautājumi, kurus vēlaties.
rec. Atvērt vaicājumu, savienot
Šis paziņojums izpilda definēto vaicājumu noteiktajā savienojumā. Šeit mēs izmantojam ierakstu kopas objekta Open metodi. Visa izvade tiek saglabāta ierakstu kopas objektārec. Ierakstu kopas objektā varat izgūt manipulēt vai dzēst vērtības.
Šūnas. Skaidrs saturs
Šī rinda notīra lapas saturu. Citiem vārdiem sakot, tiek izdzēsts viss no lapas šūnām.
Ja (rec.RecordCount 0) Tad dariet, kamēr nav rec.EOF diapazons ("A" un šūnas (Rows.Count, 1). End (xlUp) .Row) .Pārvietojums (1, 0) .Vērtība2 = _ rec.Fields (1). Vērtība rec.MoveNext Loop End If
Iepriekš minētā līniju kopa pārbauda, vai ierakstu kopa ir tukša. Ja ierakstu kopa nav tukša (tas nozīmē, ka vaicājums ir atgriezis dažus ierakstus), cikls sākas un sāk drukāt katru 1. lauka vērtību (otrais lauks, šajā gadījumā vārds) kolonnas pēdējā neizmantotajā šūnā.
(Tas tiek izmantots tikai paskaidrošanai. Jums, iespējams, nav šo rindiņu. Ja vēlaties tikai izveidot savienojumu ar datu bāzi, pietiek ar VBA kodu virs šīm rindām.)
Mēs esam izmantojuši rec.EOF, lai palaistu cilpu līdz ierakstu kopas beigām. Rec.MoveNext tiek izmantots, lai pārietu uz nākamo ierakstu kopu. rec.Fields (1) tiek izmantots, lai iegūtu vērtības no 1. lauka (kas ir otrais, jo lauka indeksācija sākas no 0. Manā datu bāzē otrais lauks ir klienta vārds).
rec. Aizvērt savienojumu. Aizvērt
Visbeidzot, kad viss darbs, ko vēlējāmies no rec un conn, ir paveikts, mēs tos aizveram.
Ja vēlaties atsevišķi atvērt un aizvērt konkrētus savienojumus, šīs rindiņas var būt atsevišķā apakšprogrammā.
Jā, puiši, šādi jūs izveidojat savienojumu ar ACCESS datu bāzi, izmantojot ADO. Ir arī citas metodes, taču tas ir vienkāršākais veids, kā izveidot savienojumu ar piekļuves datu avotu, izmantojot VBA. Es to esmu izskaidrojis pēc iespējas detalizētāk. Ļaujiet man zināt, vai tas bija noderīgi komentāru sadaļā zemāk.
Saistītie raksti:
Izmantojiet slēgtu darbgrāmatu kā datu bāzi (DAO), izmantojot Microsoft Excel VBA | Lai izmantotu slēgtu darbgrāmatu kā datu bāzi ar DAO savienojumu, izmantojiet šo VBA fragmentu programmā Excel.
Izmantojiet slēgtu darbgrāmatu kā datu bāzi (ADO), izmantojot Microsoft Excel VBA | Lai izmantotu slēgtu darbgrāmatu kā datu bāzi ar ADO savienojumu, izmantojiet šo VBA fragmentu programmā Excel.
Darba sākšana ar Excel VBA UserForms | Lai ievietotu datus datu bāzē, mēs izmantojam veidlapas. Excel UserForms ir noderīgas informācijas iegūšanai no lietotāja. Lūk, kā jums vajadzētu sākt ar VBA lietotāju formām.
Mainiet vairāku UserForm vadīklu vērtību/saturu, izmantojot Excel VBA | Lai mainītu lietotāja formas vadīklu saturu, izmantojiet šo vienkāršo VBA fragmentu.
Neļaujiet lietotāja veidlapai aizvērties, kad lietotājs noklikšķina uz pogas x, izmantojot programmu VBA programmā Excel | Lai novērstu lietotāja veidlapas aizvēršanos, kad lietotājs noklikšķina uz veidlapas pogas x, mēs izmantojam notikumu UserForm_QueryClose.
Populāri raksti:
50 Excel saīsnes, lai palielinātu produktivitāti | Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Funkcija VLOOKUP programmā Excel | Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
COUNTIF programmā Excel 2016 | Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai uzskaitītu noteiktu vērtību. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot SUMIF funkciju programmā Excel | Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.