Baza danych Access to system zarządzania relacyjnymi bazami danych, który efektywnie zapisuje duże ilości danych w zorganizowany sposób. Gdzie Excel jest potężnym narzędziem do przekształcania danych w znaczące informacje. Jednak Excel nie może przechowywać zbyt dużej ilości danych. Ale kiedy używamy razem programu Excel i Access, moc tych narzędzi rośnie wykładniczo. Nauczmy się więc, jak połączyć bazę danych Access jako źródło danych z Excelem za pomocą VBA.
Łączenie bazy danych Access jako źródła danych Excel
1: Dodaj odniesienie do obiektu danych ActivveX
Będziemy używać ADO do łączenia się w celu uzyskania dostępu do bazy danych. Więc najpierw musimy dodać referencję do obiektu ADO.
Dodaj moduł do swojego projektu VBA i kliknij narzędzia. Tutaj kliknij na referencje.
Teraz poszukaj biblioteki Microsoft ActiveX Data Object Library. Sprawdź najnowszą posiadaną wersję. Mam 6.1. Kliknij przycisk OK i gotowe. Teraz jesteśmy gotowi do utworzenia łącza do Bazy Danych Access.
2. Napisz kod VBA, aby ustanowić połączenie z bazą danych dostępu
Aby połączyć program Excel z bazą danych programu Access, musisz mieć bazę danych programu Access. Nazwa mojej bazy danych to „Testuj bazę danych.accdb". Jest zapisany w „C:\Użytkownicy\Manish Singh\Pulpit” Lokalizacja. Te dwie zmienne są ważne. Będziesz musiał je zmienić zgodnie ze swoimi potrzebami. Kod reszty można zachować bez zmian.
Skopiuj poniższy kod, aby utworzyć moduł Excel VBA i wprowadzić zmiany zgodnie z wymaganiami. Wyjaśniłem każdy wiersz kodu poniżej:
Sub ADO_Połączenie() „Tworzenie obiektów połączenia i zestawu rekordów” Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, zapytanie As String 'Deklarowanie w pełni kwalifikowanej nazwy bazy danych. Zmień go, podając lokalizację i nazwę bazy danych. DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb" „To jest dostawca połączenia. Zapamiętaj to podczas rozmowy kwalifikacyjnej. PRVD = "Microsoft.ace.OLEDB.12.0;" 'To jest ciąg połączenia, którego będziesz potrzebować podczas otwierania połączenia. connString = "Dostawca =" & PRVD & "Źródło danych =" & DBPATH „otwieranie połączenia” conn.Open connString „zapytanie, które chcę uruchomić w bazie danych. zapytanie = "WYBIERZ * z klientaT;" 'uruchamianie zapytania na otwartym połączeniu. Otrzyma wszystkie dane w rec obiekt. rec.Otwórz zapytanie, połącz „czyszczenie zawartości komórek” Komórki.WyczyśćZawartość 'pobranie danych z zestawu rekordów, jeśli istnieje i wydrukowanie ich w kolumnie A arkusza Excela. If (rec.RecordCount 0) Then nie rec.EOF Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _ rec.Fields (1).Wartość rec.MoveNext Koniec pętli, jeśli „zamykanie połączeń” rec.Close conn.Close End Sub
Skopiuj powyższy kod lub pobierz plik poniżej i wprowadź zmiany w pliku zgodnie z własnymi wymaganiami.
Pobierz plik: Nauka bazy danych VBAPo uruchomieniu tego kodu VBA program Excel nawiąże połączenie z bazą danych. Następnie uruchomi zaprojektowane zapytanie. Wyczyści starą zawartość arkusza i wypełni kolumnę A wartościami Pola 1 (drugie pole) bazy danych.
Jak działa połączenie bazy danych dostępu VBA?
Dim conn As New Connection, rec As New Recordset
W powyższym wierszu nie deklarujemy tylko zmiennych Connection i zestawu rekordów, ale inicjujemy je bezpośrednio za pomocą słowa kluczowego New.
DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Te dwie linie to zawodnicy. DBPATH zmieni się tylko z twoją bazą danych. PRVD łączy dostawcę OLE DB.
conn.Open connString
Ta linia otwiera połączenie z bazą danych. Open to funkcja obiektu połączenia, która przyjmuje kilka argumentów. Pierwszym i niezbędnym argumentem jest ConnectingString. Ten ciąg zawiera dostawcę OLE DB (tutaj PRVD) i źródło danych (tutaj DBPATH). Może również przyjąć admin i hasło jako opcjonalne argumenty dla chronionych baz danych.
Składnia Connection.Open to:
połączenie.otwarte ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long=-1])
Ponieważ nie mam żadnego identyfikatora ani hasła w mojej bazie danych, używam tylko ciągu ConnectionString. Format ciągu połączenia to „Provider=dostawca_chcesz użyć; Źródło danych=w pełni kwalifikowana nazwa bazy danych". Stworzyliśmy i zapisaliśmy ten ciąg wconnString zmienny.
zapytanie = "WYBIERZ * z klientaT;"
To jest zapytanie, które chcę uruchomić w bazie danych. Możesz mieć dowolne pytania.
rec.Otwórz zapytanie, połącz
Ta instrukcja uruchamia zdefiniowane zapytanie w zdefiniowanym połączeniu. Tutaj używamy metody Open obiektu zestawu rekordów. Wszystkie dane wyjściowe są zapisywane w obiekcie zestawu rekordówrec. Możesz pobierać wartości manipulować lub usuwać z obiektu zestawu rekordów.
Komórki.WyczyśćZawartość
Ta linia czyści zawartość arkusza. Innymi słowy, usuwa wszystko z komórek arkusza.
If (rec.RecordCount 0) Then nie rec.EOF Range("A" & Cells(Rows.Count, 1).End(xlUp).Row).Offset(1, 0).Value2 = _ rec.Fields (1).Wartość rec.MoveNext Koniec pętli, jeśli
Powyższy zestaw wierszy sprawdza, czy zestaw rekordów jest pusty, czy nie. Jeśli zestaw rekordów nie jest pusty (co oznacza, że zapytanie zwróciło kilka rekordów) pętla rozpoczyna się i rozpoczyna drukowanie każdej wartości pola 1 (w tym przypadku drugie pole, w tym przypadku imię) w ostatniej nieużywanej komórce w kolumnie.
(Jest to używane, po prostu wyjaśnij. Możesz nie mieć tych wierszy. Jeśli chcesz tylko otworzyć połączenie z bazą danych, wystarczy kod VBA powyżej tych wierszy.)
Użyliśmy rec.EOF do uruchomienia pętli do końca zestawu rekordów. rec.MoveNext służy do przejścia do następnego zestawu rekordów. rec.Fields(1) służy do pobierania wartości z pola 1 (które jest drugie, ponieważ jego indeksowanie pól zaczyna się od 0. W mojej bazie danych drugie pole to Imię klienta).
rec.Zamknij poł.Zamk.
W końcu, kiedy cała praca, którą chcieliśmy od rec i conn jest skończona, zamykamy je.
Możesz mieć te linie w osobnym podprogramie, jeśli chcesz osobno otwierać i zamykać określone połączenia.
Więc tak, w ten sposób nawiązujesz połączenie z bazą danych ACCESS za pomocą ADO. Istnieją również inne metody, ale jest to najłatwiejszy sposób na połączenie się ze źródłem dostępu do danych za pośrednictwem VBA. Wyjaśniłem to tak szczegółowo, jak tylko potrafię. Daj mi znać, czy to było pomocne w sekcji komentarzy poniżej.
Powiązane artykuły:
Użyj zamkniętego skoroszytu jako bazy danych (DAO) przy użyciu VBA w Microsoft Excel | Aby użyć zamkniętego skoroszytu jako bazy danych z połączeniem DAO, użyj tego fragmentu kodu VBA w programie Excel.
Użyj zamkniętego skoroszytu jako bazy danych (ADO) za pomocą VBA w Microsoft Excel | Aby użyć zamkniętego skoroszytu jako bazy danych z połączeniem ADO, użyj tego fragmentu kodu VBA w programie Excel.
Pierwsze kroki z formularzami użytkownika Excel VBA | W celu wprowadzenia danych do bazy danych korzystamy z formularzy. Formularze użytkownika programu Excel są przydatne do uzyskiwania informacji od użytkownika. Oto, jak powinieneś zacząć od formularzy użytkownika VBA.
Zmień wartość / zawartość kilku formantów UserForm za pomocą VBA w programie Excel | Aby zmienić zawartość kontrolek formularza użytkownika, użyj tego prostego fragmentu kodu VBA.
Zapobiegaj zamykaniu formularza użytkownika, gdy użytkownik kliknie przycisk x za pomocą VBA w programie Excel | Aby uniemożliwić zamknięcie formularza użytkownika, gdy użytkownik kliknie przycisk x formularza, używamy zdarzenia UserForm_QueryClose.
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Funkcja WYSZUKAJ.PIONOWO w programie Excel | Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
LICZ.JEŻELI w Excelu 2016 | Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby policzyć konkretną wartość. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.