Il database di Access è un sistema di gestione di database relazionali che salva efficacemente una grande quantità di dati in modo organizzato. Dove Excel è un potente strumento per sminuzzare i dati in informazioni significative. Tuttavia, Excel non può memorizzare troppi dati. Ma quando usiamo Excel e Access insieme, la potenza di questi strumenti aumenta in modo esponenziale. Quindi, impariamo come connettere il database di Access come origine dati a Excel tramite VBA.
Connessione del database di Access come origine dati Excel
1: Aggiungi riferimento all'oggetto dati AcitveX
Useremo ADO per connetterci per accedere al database. Quindi prima dobbiamo aggiungere il riferimento all'oggetto ADO.
Aggiungi un modulo al tuo progetto VBA e fai clic sugli strumenti. Qui clicca sui riferimenti.
Ora cerca la libreria di oggetti dati Microsoft ActiveX. Controlla l'ultima versione che hai. Ho 6.1. Fare clic sul pulsante OK e il gioco è fatto. Ora siamo pronti per creare un collegamento al database di Access.
2. Scrivi un codice VBA per stabilire una connessione al database di Access
Per connettere Excel a un database di Access, è necessario disporre di un database di Access. Il nome del mio database è "Test Database.accdb". È salvato su "C:\Utenti\Manish Singh\Desktop" Posizione. Queste due variabili sono importanti. Dovrai cambiarli in base alle tue esigenze. Il codice di riposo può essere mantenuto così com'è.
Copia il codice qui sotto per creare il tuo modulo VBA di Excel e apportare modifiche secondo le tue esigenze. Ho spiegato ogni riga del codice di seguito:
Sub ADO_Connection() 'Creazione di oggetti di connessione e Recordset Dim conn As New Connection, rec As New Recordset Dim DBPATH, PRVD, connString, query As String 'Dichiarazione del nome completo del database. Cambialo con la posizione e il nome del tuo database. DBPATH = "C:\Utenti\ExcelTip\Desktop\Test Database.accdb" 'Questo è il provider di connessione. Ricordalo per la tua intervista. PRVD = "Microsoft.ace.OLEDB.12.0;" 'Questa è la stringa di connessione necessaria all'apertura della connessione. connString = "Fornitore=" & PRVD & "Origine dati=" & DBPATH 'apertura della connessione conn.Open connString 'la query che voglio eseguire sul database. query = "SELECT * from customerT;" 'eseguendo la query sulla connessione aperta. Otterrà tutti i dati nel rec oggetto. rec.Open query, conn "cancellare il contenuto delle celle" Cells.ClearContents 'ottenendo i dati dal recordset se presenti e stampandoli nella colonna A del foglio excel. 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 'chiudere le connessioni rec.Close conn.Close End Sub
Copia il codice sopra o scarica il file sottostante e apporta modifiche al file in base alle tue esigenze.
Scarica il file: Apprendimento del database VBAQuando esegui questo codice VBA, Excel stabilirà una connessione al database. Successivamente, eseguirà la query progettata. Cancellerà qualsiasi vecchio contenuto sul foglio e riempirà la colonna A con i valori del Campo 1 (secondo campo) del database.
Come funziona questa connessione al database di accesso VBA?
Dim conn As New Connection, rec As New Recordset
Nella riga sopra, non stiamo solo dichiarando le variabili Connection e recordset, ma inizializzandole direttamente usando la parola chiave New.
DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Queste due linee sono concorrenti. Il DBPATH cambierà solo con il tuo database. PRVD sta connettendo il provider OLE DB.
conn.Open connString
Questa riga apre la connessione al database. Open è la funzione dell'oggetto connection che accetta diversi argomenti. Il primo e necessario argomento è ConnectingString. Questa stringa contiene il provider OLE DB (qui PRVD) e l'origine dati (qui DBPATH). Può anche prendere admin e password come argomenti opzionali per i database protetti.
La sintassi di Connection.Open è:
connessione.aperta ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long=-1])
Dal momento che non ho ID e password nel mio database, utilizzo solo ConnectionString. Il formato di ConnectionString è "Provider=provider_che vuoi usare; Sorgente dati=nome completo del database". Abbiamo creato e salvato questa stringa inconnString variabile.
query = "SELECT * from customerT;"
Questa è la query che voglio eseguire sul database. Puoi avere tutte le domande che vuoi.
rec.Open query, conn
Questa istruzione esegue la query definita nella connessione definita. Qui stiamo usando il metodo Open dell'oggetto recordset. Tutto l'output viene salvato nell'oggetto recordsetrec. È possibile recuperare manipolare o eliminare valori dall'oggetto recordset.
Cells.ClearContents
Questa riga cancella il contenuto del foglio. In altre parole, elimina tutto dalle celle del foglio.
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
L'insieme di righe sopra controlla se il recordset è vuoto o meno. Se il recordset non è vuoto (significa che la query ha restituito alcuni record) il ciclo inizia e inizia a stampare ogni valore del campo 1 (secondo campo, nome in questo caso) nell'ultima cella inutilizzata della colonna.
(Questo è usato solo per spiegare. Potresti non avere queste righe. Se vuoi solo aprire una connessione al database, il codice VBA sopra queste righe è sufficiente.)
Abbiamo usato rec.EOF per eseguire il ciclo fino alla fine del recordset. Il rec.MoveNext viene utilizzato per passare al recordset successivo. rec.Fields (1) viene utilizzato per ottenere valori dal campo 1 (che è il secondo poiché l'indicizzazione del campo inizia da 0. Nel mio database, il secondo campo è il nome del cliente).
rec.Chiudi conn.Chiudi
Alla fine, quando tutto il lavoro che volevamo da rec e conn è terminato, li chiudiamo.
Potresti avere queste linee in una subroutine separata se desideri aprire e chiudere separatamente connessioni specifiche.
Quindi sì ragazzi, ecco come stabilire una connessione al database ACCESS usando ADO. Esistono anche altri metodi, ma questo è il modo più semplice per connettersi a un'origine dati di accesso tramite VBA. L'ho spiegato nel modo più dettagliato possibile. Fammi sapere se questo è stato utile nella sezione commenti qui sotto.
Articoli Correlati:
Utilizzare una cartella di lavoro chiusa come database (DAO) utilizzando VBA in Microsoft Excel | Per utilizzare una cartella di lavoro chiusa come database con connessione DAO, utilizzare questo frammento VBA in Excel.
Utilizzare una cartella di lavoro chiusa come database (ADO) utilizzando VBA in Microsoft Excel | Per utilizzare una cartella di lavoro chiusa come database con connessione ADO, utilizzare questo frammento VBA in Excel.
Iniziare con i moduli utente VBA di Excel | Per inserire i dati nel database, utilizziamo i moduli. I moduli utente di Excel sono utili per ottenere informazioni dall'utente. Ecco come dovresti iniziare con i moduli utente VBA.
Modifica il valore/contenuto di diversi controlli UserForm utilizzando VBA in Excel | Per modificare il contenuto dei controlli del form utente, utilizzare questo semplice frammento VBA.
Impedisci la chiusura di un modulo utente quando l'utente fa clic sul pulsante x utilizzando VBA in Excel | Per impedire la chiusura del form utente quando l'utente fa clic sul pulsante x del form, utilizziamo l'evento UserForm_QueryClose.
Articoli popolari:
50 scorciatoie di Excel per aumentare la produttività | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
La funzione CERCA.VERT in Excel | Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
CONTA.SE in Excel 2016 | Conta i valori con le condizioni usando questa fantastica funzione. Non è necessario filtrare i dati per contare un valore specifico. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel | Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.