Baza de date Access este un sistem de gestionare a bazelor de date relaționale care economisește eficient o cantitate mare de date într-un mod organizat. În cazul în care Excel este un instrument puternic pentru reducerea datelor în informații semnificative. Cu toate acestea, Excel nu poate stoca prea multe date. Dar când folosim Excel și Access împreună, puterea acestor instrumente crește exponențial. Deci, să învățăm cum să conectăm baza de date Access ca sursă de date la Excel prin VBA.
Conectarea bazei de date de acces ca sursă de date Excel
1: Adăugați referință la obiectul de date AcitveX
Vom folosi ADO pentru a ne conecta pentru a accesa baza de date. Deci mai întâi trebuie să adăugăm referința la obiectul ADO.
Adăugați un modul la proiectul dvs. VBA și faceți clic pe instrumente. Aici faceți clic pe referințe.
Acum căutați Microsoft ActiveX Data Object Library. Verificați cea mai recentă versiune pe care o aveți. Am 6.1. Faceți clic pe butonul OK și gata. Acum suntem gata să creăm un link către baza de date Access.
2. Scrieți un cod VBA pentru a stabili o conexiune la baza de date Access
Pentru a conecta Excel la o bază de date Access, trebuie să aveți o bază de date Access. Numele bazei mele de date este „Test Database.accdb ". Este salvat la „C: \ Users \ Manish Singh \ Desktop” Locație. Aceste două variabile sunt importante. Va trebui să le schimbați în funcție de nevoile dvs. Codul de repaus poate fi păstrat așa cum este.
Copiați codul de mai jos pentru a vă face modulul Excel VBA și a face modificări conform cerințelor dvs. Am explicat fiecare linie a codului de mai jos:
Sub ADO_Connection () 'Crearea obiectelor de conexiune și set de înregistrări Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, interogare As String „Declararea numelui complet calificat al bazei de date. Schimbați-l cu locația și numele bazei de date. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" „Acesta este furnizorul de conexiune. Amintiți-vă acest lucru pentru interviu. PRVD = "Microsoft.ace.OLEDB.12.0;" „Acesta este șirul de conexiune pe care îl veți avea nevoie la deschiderea conexiunii. connString = "Furnizor =" & PRVD & "Sursă de date =" & DBPATH 'deschiderea conexiunii conn.Open connString „interogarea pe care vreau să o rulez în baza de date. query = "SELECT * de la clientT;" 'rularea interogării pe conexiunea deschisă. Va primi toate datele din rec obiect. rec.Open interogare, conn „ștergerea conținutului celulelor Celule.ClearContents „obținerea de date din setul de înregistrări, dacă există, și tipărirea lor în coloana A a foii Excel. If (rec.RecordCount 0) Apoi faceți în timp ce nu rec.EOF Range ("A" & Cells (Rows.Count, 1) .End (xlUp) .Row) .Offset (1, 0) .Value2 = _ rec.Fields (1) .Value rec.MoveNext Loop End If 'închiderea conexiunilor rec.Închide conn.Închide End Sub
Copiați codul de mai sus sau descărcați fișierul de mai jos și modificați fișierul pentru a se potrivi cerințelor dvs.
Descărcați fișierul: Învățarea bazei de date VBACând rulați acest cod VBA, Excel va stabili o conexiune la baza de date. Ulterior, va rula interogarea proiectată. Va șterge orice conținut vechi de pe foaie și va umple coloana A cu valorile câmpului 1 (al doilea câmp) al bazei de date.
Cum funcționează această conexiune la baza de date VBA Access?
Dim conn As New Connection, rec As New Recordset
În linia de mai sus, nu declarăm doar variabilele Conexiune și set de înregistrări, ci o inițializăm direct folosind cuvântul cheie Nou.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Aceste două linii sunt concurenți. DBPATH se va schimba numai cu baza de date. PRVD conectează furnizorul OLE DB.
conn.Open connString
Această linie deschide conexiunea la baza de date. Deschis este funcția obiectului de conexiune care ia mai multe argumente. Primul și necesar argument este ConnectingString. Acest șir conține furnizorul OLE DB (aici PRVD) și sursa de date (aici DBPATH). Poate lua, de asemenea, administratorul și parola ca argumente opționale pentru bazele de date protejate.
Sintaxa Connection.Open este:
connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long = -1])
Deoarece nu am niciun ID și parolă în baza de date, folosesc doar ConnectionString. Formatul ConnectionString este „Furnizor =furnizor_vrei să folosești; Sursa datelor =numele complet calificat al bazei de dateAm creat și am salvat acest șir înconnString variabil.
query = "SELECT * de la clientT;"
Aceasta este interogarea pe care vreau să o execut pe baza de date. Puteți avea orice întrebări doriți.
rec.Open interogare, conn
Această instrucțiune execută interogarea definită în conexiunea definită. Aici folosim metoda Open a obiectului recordset. Toată ieșirea este salvată în obiectul setului de înregistrărirec. Puteți prelua manipularea sau ștergerea valorilor din obiectul setului de înregistrări.
Celule.ClearContents
Această linie șterge conținutul foii. Cu alte cuvinte, șterge totul din celulele foii.
If (rec.RecordCount 0) Apoi, 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
Setul de linii de mai sus verifică dacă setul de înregistrări este gol sau nu. Dacă setul de înregistrări nu este gol (înseamnă că interogarea a returnat unele înregistrări), bucla începe și începe să imprime fiecare valoare a câmpului 1 (al doilea câmp, prenume în acest caz) în ultima celulă neutilizată din coloană.
(Acest lucru este folosit doar explică. Este posibil să nu aveți aceste linii. Dacă doriți doar să deschideți o conexiune la baza de date, atunci codul VBA deasupra acestor linii este suficient.)
Am folosit rec.EOF pentru a rula bucla până la sfârșitul setului de înregistrări. Rec.MoveNext este folosit pentru a trece la următorul set de înregistrări. rec.Fields (1) este folosit pentru a obține valori din câmpul 1 (care este al doilea, deoarece indexarea câmpului său începe de la 0. În baza mea de date, al doilea câmp este prenumele clientului).
rec.Închide conn.Închide
În cele din urmă, când se termină toată munca pe care am dorit-o de la rec și conn, le închidem.
Este posibil să aveți aceste linii în subrutină separată dacă doriți să deschideți și să închideți separat conexiuni specifice.
Deci, băieți, așa stabiliți o conexiune la baza de date ACCESS folosind ADO. Există și alte metode, dar acesta este cel mai simplu mod de a vă conecta la o sursă de date de acces prin VBA. L-am explicat cât de detaliat pot. Spuneți-mi dacă acest lucru a fost util în secțiunea de comentarii de mai jos.
Articole similare:
Utilizați un registru de lucru închis ca bază de date (DAO) utilizând VBA în Microsoft Excel | Pentru a utiliza un registru de lucru închis ca bază de date cu conexiune DAO, utilizați acest fragment VBA în Excel.
Utilizați un registru de lucru închis ca bază de date (ADO) utilizând VBA în Microsoft Excel | Pentru a utiliza un registru de lucru închis ca bază de date cu conexiune ADO, utilizați acest fragment VBA în Excel.
Noțiuni introductive despre Excel VBA UserForms | Pentru a insera date în baza de date, folosim formulare. Formularele de utilizator Excel sunt utile pentru a obține informații de la utilizator. Iată cum ar trebui să începeți cu formele de utilizator VBA.
Modificați valoarea / conținutul mai multor controale UserForm utilizând VBA în Excel | Pentru a schimba conținutul comenzilor formei de utilizator, utilizați acest fragment VBA simplu.
Împiedicați închiderea unui formular de utilizator atunci când utilizatorul face clic pe butonul x folosind VBA în Excel | Pentru a preveni închiderea formei de utilizator atunci când utilizatorul dă clic pe butonul x al formularului, utilizăm evenimentul UserForm_QueryClose.
Articole populare:
50 de comenzi rapide Excel pentru a vă crește productivitatea | Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Funcția VLOOKUP în Excel | Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
COUNTIF în Excel 2016 | Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra o anumită valoare. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel | Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.
