Come collegare Excel a database di Access con VBA
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 analizzare i dati in informazioni significative. Tuttavia, Excel non può memorizzare troppi dati. Ma quando utilizziamo 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 al 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 fare clic sui riferimenti.
Ora cerca la libreria Microsoft ActiveX Data Object. 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. Scrivere 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”. Viene salvato nella posizione “C: \ Users \ Manish Singh \ Desktop”. Queste due variabili sono importanti. Dovrai modificarli in base alle tue esigenze. Il codice di riposo può essere mantenuto così com’è.
Copia il codice seguente per creare il tuo modulo VBA di Excel e apportare le modifiche secondo le tue esigenze. Ho spiegato ogni riga del codice di seguito:
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
Copia il codice sopra o scarica il file qui sotto e apporta le modifiche al file in base alle tue esigenze.
`link: /wp-content-uploads-2020-02-VBA-Database-Learning.xls [__ Scarica file: VBA Database Learning]
Quando 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 precedente, non stiamo solo dichiarando le variabili Connection e recordset, ma inizializzandole direttamente utilizzando la parola chiave New.
DBPATH = "C:\Users\ExcelTip\Desktop\Test Database.accdb" PRVD = "Microsoft.ace.OLEDB.12.0;"
Queste due linee sono concorrenti. 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 connessione 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 è:
connection.open ([ConnectionString as String], [UserID as String], [Password as String], [Options as Long=-1])
Poiché non ho ID e password sul mio database, utilizzo solo ConnectionString. Il formato di ConnectionString è “Provider = provider_you desidera utilizzare; _ Data Source = _ nome completo del database“. Abbiamo creato e salvato questa stringa inconnString variable.
query = "SELECT * from customerT;"
Questa è la query che voglio eseguire sul database. Puoi avere tutte le domande che desideri.
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 nel recordset objectrec. È possibile recuperare, modificare o eliminare i 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
Il gruppo 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, in questo caso il primo nome) in // celle-intervalli-righe-e-colonne-in vba / 3-best-ways-to-find-last-non-blank-row-and-column-using-vba.html [ultima cella non utilizzata nella 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 utilizzato 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 i 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.Close conn.Close
Infine, quando tutto il lavoro che volevamo da rec and conn è finito, li chiudiamo.
Potresti avere queste linee in una subroutine separata se desideri aprire e chiudere separatamente connessioni specifiche.
Quindi sì ragazzi, è così che stabilisci 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:
`link: / import-and-export-in-vba-use-a-closed-workbook-as-a-database-dao-using-vba-in-microsoft-excel [Usa 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 di codice VBA in Excel.
`link: / import-and-export-in-vba-use-a-closed-workbook-as-a-database-ado-using-vba-in-microsoft-excel [Usa 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 di codice VBA in Excel.
link: / applications-word-outlook-in-vba-get-started-with-excel-vba-userforms [Primi passi con Excel VBA UserForms] | * Per inserire dati nel database, usiamo moduli. Gli Excel UserForms sono utili per ottenere informazioni dall’utente. Ecco come dovresti iniziare con i moduli utente VBA.
===
`link: / user-forms-input-box-in-vba-change-the-valueecontent-of-many-userform-controls-using-vba-in-microsoft-excel [Cambia il valore / contenuto di diversi controlli UserForm utilizzando VBA in Excel] `| * Per modificare il contenuto dei controlli del modulo utente, utilizzare questo semplice frammento di codice VBA.
`link: / user-forms-input-box-in-vba-prevent-a-userform-by-closing-when-user-click-the-x-button-using-vba-in-microsoft-excel [ Impedire la chiusura di un modulo utente quando l’utente fa clic sul pulsante x utilizzando VBA in Excel] `| Per evitare che il modulo utente si chiuda quando l’utente fa clic sul pulsante x del modulo, utilizziamo l’evento UserForm_QueryClose.
Articoli popolari:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentare-la-produttività [50 scorciatoie Excel per aumentare la produttività] | Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [La funzione CERCA.VERT in Excel] | Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare valori da diversi intervalli e fogli.
link: / tips-countif-in-microsoft-excel [COUNTIF in Excel 2016] | Contare i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare un valore specifico.
La funzione Countif è essenziale per preparare la tua dashboard.
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel] | Questa è un’altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.