Die Access-Datenbank ist ein relationales Datenbankverwaltungssystem, das effektiv eine große Datenmenge auf organisierte Weise speichert. Wo Excel ein leistungsstarkes Werkzeug ist, um Daten in aussagekräftige Informationen zu zerlegen. Excel kann jedoch nicht zu viele Daten speichern. Aber wenn wir Excel und Access zusammen verwenden, erhöht sich die Leistungsfähigkeit dieser Tools exponentiell. Lassen Sie uns also lernen, wie Sie die Access-Datenbank als Datenquelle über VBA mit Excel verbinden.
Verbinden der Access-Datenbank als Datenquelle Excel
1: Referenz zum AcitveX-Datenobjekt hinzufügen
Wir werden ADO verwenden, um eine Verbindung herzustellen, um auf die Datenbank zuzugreifen. Zuerst müssen wir also den Verweis auf das ADO-Objekt hinzufügen.
Fügen Sie Ihrem VBA-Projekt ein Modul hinzu und klicken Sie auf die Tools. Klicken Sie hier auf die Referenzen.
Suchen Sie nun nach der Microsoft ActiveX-Datenobjektbibliothek. Überprüfen Sie die neueste Version, die Sie haben. Ich habe 6.1. Klicken Sie auf die Schaltfläche OK und es ist fertig. Jetzt können wir einen Link zur Access-Datenbank erstellen.
2. Schreiben Sie einen VBA-Code, um eine Verbindung zur Access-Datenbank herzustellen
Um Excel mit einer Access-Datenbank zu verbinden, benötigen Sie eine Access-Datenbank. Der Name meiner Datenbank ist "Datenbank.accdb testen". Es ist gespeichert unter "C:\Benutzer\Manish Singh\Desktop" Lage. Diese beiden Variablen sind wichtig. Sie müssen sie entsprechend Ihren Bedürfnissen ändern. Der Restcode kann so belassen werden, wie er ist.
Kopieren Sie den folgenden Code, um Ihr Excel VBA-Modul zu erstellen und Änderungen gemäß Ihren Anforderungen vorzunehmen. Ich habe jede Zeile des Codes unten erklärt:
Unter ADO_Connection() 'Objekte von Connection und Recordset erstellen Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, query As String 'Vollqualifizierten Namen der Datenbank deklarieren. Ändern Sie es mit dem Speicherort und Namen Ihrer Datenbank. DBPATH = "C:\Benutzer\ExcelTip\Desktop\Test Database.accdb" 'Das ist der Verbindungsanbieter. Denken Sie daran für Ihr Vorstellungsgespräch. PRVD = "Microsoft.ace.OLEDB.12.0;" 'Dies ist die Verbindungszeichenfolge, die Sie beim Öffnen der Verbindung benötigen. connString = "Provider=" & PRVD & "Datenquelle=" & DBPATH 'Verbindung öffnen conn.Open connString 'die Abfrage, die ich in der Datenbank ausführen möchte. query = "SELECT * from customerT;" 'die Abfrage auf der offenen Verbindung ausführen. Es werden alle Daten im rec Objekt. rec.Open query, conn 'Löschen des Inhalts der Zellen Cells.ClearContents 'Daten aus dem Recordset abrufen, falls vorhanden, und in Spalte A der Excel-Tabelle drucken. 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).Wert rec.MoveNext Schleifenende Wenn 'Verbindungen schließen rec.Close conn.Close End Sub
Kopieren Sie den obigen Code oder laden Sie die Datei unten herunter und nehmen Sie Änderungen an der Datei vor, die Ihren Anforderungen entspricht.
Datei herunterladen: VBA-Datenbank lernenWenn Sie diesen VBA-Code ausführen, stellt Excel eine Verbindung zur Datenbank her. Anschließend wird die entworfene Abfrage ausgeführt. Es löscht alle alten Inhalte auf dem Blatt und füllt die Spalte A mit den Werten von Feld 1 (zweites Feld) der Datenbank.
Wie funktioniert diese VBA Access-Datenbankverbindung?
Dim conn As New Connection, rec As New Recordset
In der obigen Zeile deklarieren wir nicht nur die Variablen Connection und Recordset, sondern initialisieren sie direkt mit dem Schlüsselwort New.
DBPATH = "C:\Benutzer\ExcelTip\Desktop\Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Diese beiden Linien sind Kandidaten. Der DBPATH ändert sich nur mit Ihrer Datenbank. PRVD verbindet den OLE DB-Anbieter.
conn.Open connString
Diese Zeile öffnet die Verbindung zur Datenbank. Open ist die Funktion des Verbindungsobjekts, die mehrere Argumente akzeptiert. Das erste und notwendige Argument ist ConnectingString. Dieser String enthält den OLE DB-Provider (hier PRVD) und die Datenquelle (hier DBPATH). Es kann auch admin und password als optionale Argumente für geschützte Datenbanken verwenden.
Die Syntax von Connection.Open ist:
Verbindung.öffnen ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long=-1])
Da ich keine ID und kein Passwort in meiner Datenbank habe, verwende ich nur ConnectionString. Das Format des ConnectionStrings ist "Provider=Anbieter_Sie möchten verwenden; Datenquelle=vollqualifizierter Name der Datenbank". Wir haben diese Zeichenfolge erstellt und gespeichert inconnString Variable.
query = "SELECT * from customerT;"
Dies ist die Abfrage, die ich in der Datenbank ausführen möchte. Sie können alle gewünschten Fragen haben.
rec.Open query, conn
Diese Anweisung führt die definierte Abfrage in der definierten Verbindung aus. Hier verwenden wir die Open-Methode des Recordset-Objekts. Die gesamte Ausgabe wird im Recordset-Objekt gespeichertrec. Sie können Werte aus dem Recordset-Objekt manipulieren oder löschen.
Cells.ClearContents
Diese Zeile löscht den Inhalt des Blatts. Mit anderen Worten, löscht alles aus den Zellen des Blatts.
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).Wert rec.MoveNext Schleifenende Wenn
Der obige Satz von Zeilen überprüft, ob das Recordset leer ist oder nicht. Wenn das Recordset nicht leer ist (dh die Abfrage hat einige Datensätze zurückgegeben), beginnt die Schleife und beginnt, jeden Wert von Feld 1 (zweites Feld, in diesem Fall Vorname) in der letzten nicht verwendeten Zelle in der Spalte zu drucken.
(Dies wird nur zur Erläuterung verwendet. Möglicherweise haben Sie diese Zeilen nicht. Wenn Sie nur eine Verbindung zur Datenbank herstellen möchten, reicht der VBA-Code über diesen Zeilen.)
Wir haben rec.EOF verwendet, um die Schleife bis zum Ende des Recordsets auszuführen. Der rec.MoveNext wird verwendet, um zum nächsten Recordset zu wechseln. rec.Fields(1) wird verwendet, um Werte aus Feld 1 abzurufen (das an zweiter Stelle steht, da die Feldindizierung bei 0 beginnt. In meiner Datenbank ist das zweite Feld der Vorname des Kunden).
rec.Close conn.Close
Schließlich, wenn alle Arbeiten, die wir von rec und conn wollten, erledigt sind, schließen wir sie.
Sie können diese Zeilen in einem separaten Unterprogramm haben, wenn Sie bestimmte Verbindungen separat öffnen und schließen möchten.
Also, Leute, so stellt ihr mit ADO eine Verbindung zur ACCESS-Datenbank her. Es gibt auch andere Methoden, aber dies ist die einfachste Möglichkeit, eine Verbindung zu einer Datenquelle mit Zugriff über VBA herzustellen. Ich habe es so ausführlich wie möglich erklärt. Lassen Sie mich wissen, ob dies im Kommentarbereich unten hilfreich war.
In Verbindung stehende Artikel:
Verwenden Sie eine geschlossene Arbeitsmappe als Datenbank (DAO) mit VBA in Microsoft Excel | Um eine geschlossene Arbeitsmappe als Datenbank mit DAO-Verbindung zu verwenden, verwenden Sie dieses VBA-Snippet in Excel.
Verwenden Sie eine geschlossene Arbeitsmappe als Datenbank (ADO) mit VBA in Microsoft Excel | Um eine geschlossene Arbeitsmappe als Datenbank mit ADO-Verbindung zu verwenden, verwenden Sie dieses VBA-Snippet in Excel.
Erste Schritte mit Excel VBA UserForms | Um Daten in die Datenbank einzufügen, verwenden wir Formulare. Die Excel UserForms sind nützlich, um Informationen vom Benutzer zu erhalten. So sollten Sie mit VBA-Benutzerformularen beginnen.
Ändern Sie den Wert/Inhalt mehrerer UserForm-Steuerelemente mit VBA in Excel | Um den Inhalt der Userform-Steuerelemente zu ändern, verwenden Sie dieses einfache VBA-Snippet.
Verhindern Sie, dass ein Benutzerformular geschlossen wird, wenn der Benutzer auf die x-Schaltfläche klickt, indem Sie VBA in Excel verwenden | Um zu verhindern, dass das Benutzerformular geschlossen wird, wenn der Benutzer auf die Schaltfläche x des Formulars klickt, verwenden wir das Ereignis UserForm_QueryClose.
Populäre Artikel:
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
Die SVERWEIS-Funktion in Excel | Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
ZÄHLENWENN in Excel 2016 | Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um einen bestimmten Wert zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel | Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.