Access -databasen är ett relationsdatabashanteringssystem som effektivt sparar en stor mängd data på ett organiserat sätt. Där Excel är ett kraftfullt verktyg för att krossa ner data till meningsfull information. Excel kan dock inte lagra för mycket data. Men när vi använder Excel och Access tillsammans ökar kraften i dessa verktyg exponentiellt. Så låt oss lära oss hur du ansluter Access -databasen som en datakälla till Excel via VBA.
Ansluter åtkomstdatabas som datakälla Excel
1: Lägg till referens till AcitveX Data Object
Vi kommer att använda ADO för att ansluta för att komma åt databasen. Så först måste vi lägga till referensen till ADO -objektet.
Lägg till en modul i ditt VBA -projekt och klicka på verktygen. Klicka här på referenserna.
Leta nu efter Microsoft ActiveX Data Object Library. Kontrollera den senaste versionen du har. Jag har 6.1. Klicka på OK -knappen och det är klart. Nu är vi redo att skapa en länk till Access Database.
2. Skriv en VBA -kod för att upprätta en anslutning till åtkomstdatabasen
För att ansluta Excel till en Access -databas måste du ha en Access -databas. Min databas heter "Testa Database.accdb ". Det sparas kl "C: \ Users \ Manish Singh \ Desktop" plats. Dessa två variabler är viktiga. Du måste ändra dem efter dina behov. Restkod kan behållas som den är.
Kopiera koden nedan för att göra din Excel VBA -modul och göra ändringar enligt dina krav. Jag har förklarat varje rad i koden nedan:
Sub ADO_Connection () 'Skapa objekt för anslutning och inspelning Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, query As String 'Förklarar fullständigt databasnamn. Ändra det med din databas plats och namn. DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" 'Detta är anslutningsleverantören. Kom ihåg detta för din intervju. PRVD = "Microsoft.ace.OLEDB.12.0;" 'Detta är anslutningssträngen som du behöver när du öppnar anslutningen. connString = "Provider =" & PRVD & "Data Source =" & DBPATH 'öppnar anslutningen conn.Open connString 'frågan jag vill köra på databasen. query = "SELECT * från customerT;" 'kör frågan på den öppna anslutningen. Det kommer att få all data i rec objekt. rec.Open fråga, anslut 'rensar innehållet i cellerna Celler. Rensa Innehåll 'hämta data från rekordmängden om någon och skriva ut den i kolumn A i excelark. 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) .Värde rec.MoveNext Loop End If 'stänga anslutningarna rec.Close anslut.Close End Sub
Kopiera koden ovan eller ladda ner filen nedan och gör ändringar i filen för att passa dina krav.
Ladda ner fil: VBA Database LearningNär du kör den här VBA -koden upprättar Excel en anslutning till databasen. Efteråt körs den designade frågan. Det rensar allt gammalt innehåll på arket och fyller kolumn A med värdena för fält 1 (andra fältet) i databasen.
Hur fungerar denna VBA Access Database Connection?
Dim conn As New Connection, rec As New Recordset
På raden ovan deklarerar vi inte bara anslutnings- och rekorduppsättningsvariablerna utan initierar det direkt med det nya nyckelordet.
DBPATH = "C: \ Users \ ExcelTip \ Desktop \ Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Dessa två rader är tävlande. DBPATH ändras endast med din databas. PRVD ansluter OLE DB -leverantör.
conn.Open connString
Denna rad öppnar anslutningen till databasen. Öppen är funktionen för anslutningsobjektet som tar flera argument. Det första och nödvändiga argumentet är ConnectingString. Den här strängen innehåller OLE DB -leverantören (här PRVD) och datakällan (här DBPATH). Det kan också ta admin och lösenord som valfria argument för skyddade databaser.
Syntaxen för Connection.Open är:
anslutning. öppna ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long = -1])
Eftersom jag inte har något ID och lösenord i min databas använder jag bara ConnectionString. ConnectionStrings format är "Provider =provider_you vill använda; Datakälla =fullständigt kvalificerat databasnamn". Vi gjorde och sparade den här strängenconnString variabel.
query = "SELECT * från customerT;"
Detta är frågan jag vill köra på databasen. Du kan ha alla frågor du vill ha.
rec.Open fråga, anslut
Denna sats kör den definierade frågan i den definierade anslutningen. Här använder vi Open -metoden för recordset -objekt. All utmatning sparas i rekorduppsättningsobjektetrec. Du kan hämta manipulera eller ta bort värden från rekorduppsättningsobjektet.
Celler. Rensa Innehåll
Denna rad rensar innehållet i arket. Med andra ord, raderar allt från bladets celler.
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) .Värde rec.MoveNext Loop End If
Ovanstående uppsättning rader kontrollerar om postuppsättningen är tom eller inte. Om postuppsättningen inte är tom (det betyder att frågan returnerade vissa poster) börjar slingan och börjar skriva ut varje värde i fält 1 (andra fältet, förnamnet i det här fallet) i den sista oanvända cellen i kolumnen.
(Detta används bara förklara. Du kanske inte har dessa rader. Om du bara vill öppna en anslutning till databasen är VBA -koden ovanför dessa rader tillräckligt.)
Vi har använt rec.EOF för att köra slingan till slutet av rekordsatsen. Rec.MoveNext används för att gå upp till nästa postuppsättning. rec.Fields (1) används för att hämta värden från fält 1 (vilket är andra eftersom dess fältindexering börjar från 0. I min databas är det andra fältet kundens förnamn).
rec.Close anslut.Close
Slutligen, när allt arbete vi ville ha från rec och conn är klart stänger vi dem.
Du kan ha dessa rader i separat underrutin om du vill öppna och stänga specifika anslutningar separat.
Så ja killar, så här upprättar du en anslutning till ACCESS -databasen med hjälp av ADO. Det finns också andra metoder, men det här är det enklaste sättet att ansluta till en datakälla för åtkomst via VBA. Jag har förklarat det så detaljerat jag kan. Låt mig veta om detta var till hjälp i kommentarfältet nedan.
Relaterade artiklar:
Använd en sluten arbetsbok som en databas (DAO) med VBA i Microsoft Excel | Om du vill använda en sluten arbetsbok som en databas med DAO -anslutning använder du detta VBA -kodavsnitt i Excel.
Använd en sluten arbetsbok som en databas (ADO) med VBA i Microsoft Excel | Om du vill använda en sluten arbetsbok som en databas med ADO -anslutning använder du detta VBA -kodavsnitt i Excel.
Komma igång med Excel VBA UserForms | För att infoga data till databasen använder vi formulär. Excel UserForms är användbara för att få information från användaren. Så här bör du börja med VBA -användarformulär.
Ändra värde/innehåll för flera UserForm-kontroller med VBA i Excel | Använd detta enkla VBA -utdrag för att ändra innehållet i användarformskontrollerna.
Förhindra att en användarform stängs när användaren klickar på x-knappen med hjälp av VBA i Excel | För att förhindra att användarformen stängs när användaren klickar på x -knappen i formuläret använder vi UserForm_QueryClose -händelsen.
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet | Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
VLOOKUP -funktionen i Excel | Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
COUNTIF i Excel 2016 | Räkna värden med förhållanden med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifikt värde. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel | Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.