How to Connect Excel zu Access-Datenbank mit VBA
Die Access-Datenbank ist ein relationales Datenbankverwaltungssystem, das effektiv eine große Datenmenge auf organisierte Weise speichert. Wo Excel ein leistungsstarkes Tool ist, um Daten in aussagekräftige Informationen umzuwandeln. Excel kann jedoch nicht zu viele Daten speichern. Wenn wir jedoch Excel und Access zusammen verwenden, steigt die Leistung dieser Tools exponentiell an. Lassen Sie uns also lernen, wie Sie die Access-Datenbank als Datenquelle über VBA mit Excel verbinden.
Access-Datenbank als Datenquelle Excel verbinden
1: Verweis auf AcitveX-Datenobjekt hinzufügen
Wir werden ADO verwenden, um eine Verbindung für den Zugriff auf die Datenbank herzustellen. Zuerst müssen wir den Verweis auf das ADO-Objekt hinzufügen.
Fügen Sie Ihrem VBA-Projekt ein Modul hinzu und klicken Sie auf die Tools. Hier klicken Sie auf die Referenzen.
Suchen Sie nun nach Microsoft ActiveX Data Object Library. Überprüfen Sie die neueste Version, die Sie haben. Ich habe 6.1. Klicken Sie auf OK und fertig. Jetzt können Sie 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 lautet „Test Database.accdb“. Es wird unter „C: \ Users \ Manish Singh \ Desktop“ gespeichert. Diese beiden Variablen sind wichtig. Sie müssen sie entsprechend Ihren Anforderungen ändern. Der Restcode kann unverändert beibehalten werden.
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:
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
Kopieren Sie den obigen Code oder laden Sie die folgende Datei herunter und nehmen Sie Änderungen an der Datei vor, die Ihren Anforderungen entsprechen.
`link: /wp-content-uploads-2020-02-VBA-Database-Learning.xls [__ Datei herunterladen: VBA Database Learning]
Wenn 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 die Variablen Connection und Recordset nicht nur, sondern initialisieren sie direkt mit dem Schlüsselwort New.
DBPATH = "C:\Users\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. Diese Zeichenfolge enthält den OLE DB-Anbieter (hier PRVD) und die Datenquelle (hier DBPATH). Es kann auch Administrator und Kennwort als optionale Argumente für geschützte Datenbanken verwenden.
Die Syntax von Connection.Open lautet:
connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long=-1])
Da meine Datenbank keine ID und kein Kennwort enthält, verwende ich nur ConnectionString. Das ConnectionString-Format lautet „Provider = provider_you use to; _ Data Source = _voll qualifizierter Name der Datenbank„. Wir haben diese Zeichenfolge inconnString-Variable erstellt und gespeichert.
query = "SELECT * from customerT;"
Dies ist die Abfrage, die ich in der Datenbank ausführen möchte. Sie können beliebige 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 objectrec gespeichert. Sie können Manipulations- oder Löschwerte aus dem Recordset-Objekt abrufen.
Cells.ClearContents
Diese Zeile löscht den Inhalt des Blattes. Mit anderen Worten, löscht alles aus den Zellen des Blattes.
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
Die obigen Zeilen prüfen, ob das Recordset leer ist oder nicht. Wenn das Recordset nicht leer ist (dies bedeutet, dass die Abfrage einige Datensätze zurückgegeben hat), beginnt die Schleife und beginnt mit dem Drucken jedes Werts von Feld 1 (zweites Feld, in diesem Fall Vorname) in // Zellenbereiche-Zeilen-und-Spalten-in vba / 3-beste-Wege-um-die-letzte-nicht-leere-Zeile-und-Spalte-mit-vba.html zu finden [letzte nicht verwendete Zelle in Spalte] `.
(Dies wird nur zum Erklären 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 aus.)
Wir haben rec.EOF verwendet, um die Schleife bis zum Ende des Recordset auszuführen. Mit rec.MoveNext können Sie zum nächsten Recordset 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
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 ja Leute, so stellen Sie mit ADO eine Verbindung zur ACCESS-Datenbank her. Es gibt auch andere Methoden, aber dies ist der einfachste Weg, um über VBA eine Verbindung zu einer Datenquelle für den Zugriff herzustellen. Ich habe es so ausführlich wie möglich erklärt. Lassen Sie mich im Kommentarbereich unten wissen, ob dies hilfreich war.
Verwandte Artikel:
`link: / import-and-export-in-vba-benutze-eine-geschlossene-arbeitsmappe-als-datenbank-dao-benutze-vba-in-microsoft-excel [benutze eine geschlossene arbeitsmappe als datenbank (DAO) Verwenden von VBA in Microsoft Excel] `| Verwenden Sie dieses VBA-Snippet in Excel, um eine geschlossene Arbeitsmappe als Datenbank mit DAO-Verbindung zu verwenden.
`link: / import-and-export-in-vba-benutze-eine-geschlossene-arbeitsmappe-als-datenbank-ado-benutze-vba-in-microsoft-excel [benutze eine geschlossene arbeitsmappe als datenbank (ADO) Verwenden von VBA in Microsoft Excel] `| Verwenden Sie dieses VBA-Snippet in Excel, um eine geschlossene Arbeitsmappe als Datenbank mit ADO-Verbindung zu verwenden.
link: / Applications-Word-Outlook-in-VBA-Erste Schritte mit Excel-VBA-Benutzerformularen [Erste Schritte mit Excel VBA UserForms] | * Zum Einfügen von Daten in die Datenbank verwenden wir Formulare. Die Excel UserForms sind nützlich, um Informationen vom Benutzer abzurufen. So sollten Sie mit VBA-Benutzerformularen beginnen.
===
`link: / Benutzerformulare-Eingabefelder-in-vba-ändern-den-Wertinhalt-mehrerer-Benutzerformular-Steuerelemente-mit-vba-in-Microsoft-Excel [Ändern Sie den Wert / Inhalt mehrerer UserForm-Steuerelemente Verwenden von VBA in Excel] `| * Verwenden Sie dieses einfache VBA-Snippet, um den Inhalt der Benutzerformular-Steuerelemente zu ändern.
`link: / benutzerformulare-eingabefelder-in-vba-verhindern-das-schließen-eines-benutzerformulars-wenn-der-benutzer-auf-die-x-button-klick-mit-vba-in-microsoft-excel klickt [ Verhindern Sie, dass ein Benutzerformular geschlossen wird, wenn der Benutzer mithilfe von VBA in Excel auf die x-Schaltfläche klickt 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.
Beliebte Artikel:
link: / Tastatur-Formel-Verknüpfungen-50-Excel-Verknüpfungen-zur-Steigerung-Ihrer-Produktivität [50 Excel-Verknüpfungen zur Steigerung Ihrer Produktivität] | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Verknüpfungen können Sie noch schneller in Excel arbeiten.
link: / formeln-und-funktionen-einführung-der-vlookup-funktion [Die VLOOKUP-Funktion in Excel] | Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, mit der Werte aus verschiedenen Bereichen und Tabellen gesucht werden.
link: / tips-countif-in-microsoft-excel [COUNTIF in Excel 2016] | Zählen Sie Werte mit Bedingungen, die diese erstaunliche Funktion verwenden. Sie müssen Ihre Daten nicht filtern, um einen bestimmten Wert zu zählen.
Die Countif-Funktion ist wichtig, um Ihr Dashboard vorzubereiten.
link: / excel-formel-und-funktion-excel-sumif-funktion [Verwendung der SUMIF-Funktion in Excel] | Dies ist eine weitere wichtige Funktion des Dashboards. Auf diese Weise können Sie Werte unter bestimmten Bedingungen zusammenfassen.