Komplexebb folyamatok kialakítása, többféle adathalmazt használó dashboardok kidolgozása esetén elég gyakran lehet szükségünk Access adatbázisok használatára Excelben, amelynek lehet több formája is, most a legegyszerűbbről fogunk beszélni - azaz egy makróval szimplán egy Access adatbázis meghatározott táblájából Excelünk egyik munkalapjára másolunk adatokat. Ott aztán feldolgozhatjuk, szűrhetjük, átalakíthatjuk és így tovább.

Adott tehát a következő roppant egyszerű tábla egy Access adatbázisban:

A célunk is roppant egyszerű - ezeket az adatokat egy VBA-kódsor segítségével másoljuk be egy meghatározott Excel-fájl valamelyik sheetjére. Az Excel megnyitása után Alt+F11 lenyomásával gyorsan masírozzunk is át a VBA-editorba, ahol hozzá is láthatunk a kód kidolgozásának.

Ahogy mindig, most is a változók definiálásával kezdünk és ez most különösen fontos, hiszen itt már meghatározzuk az Access-kapcsolatfelvételt. Azt előzetesen tudnunk kell, hogy a Microsoft által kifejlesztett ADO (ActiveX Data object) arra szolgál, hogy egy fejlesztő úgy írhasson programokat adatbázisokra, hogy ne legyen szükséges az adatbázis felépítésének teljes ismerete, csak a hozzá való kapcsolódás. És most, a kódunk változókat deklaráló részénél ezt ki is fogjuk használni, hiszen először létrehozunk egy változót ADO objektumra, majd ezzel együtt létre is hozunk egy ilyen adatbázis-kapcsolódási objektumot:

Dim cnn As New ADODB.Connection

A connection object tárolja a kapcsolódásról szóló információkat és a módszereket a csatlakozásra, így a kódban néhány sorral később majd szükségünk lesz a connection object Open metódusának használatára, amely konkrétan meghatározza a kapcsolódás paramétereit, így például az Open metódus Provider paramétere az adatbázis típusát, Data Source paramétere pedig a konkrét elérési útvonalat adja meg.

A recordset ADO objektum az előzőekhez képest rekordok csoportjára hivatkozik az adatbázisban, ami lehet konkrét egy tábla, de lehet egy lekérdezés eredménye is - mostani kódunkban értelemszerűen tehát szükség van egy vadiúj recordset létrehozására:

Dim rs As New ADODB.Recordset

Szükségünk lesz még két String típusú változóra, az egyik a recordsetünket majd feltöltő lekérdezésre, a másik pedig az adatbázisunk elérési útvonalának tárolására:

Dim query As String
Dim Path As String

Most pedig gyors lendülettel fel is töltjük a változókat, ahol szükséges, így a Path nevű változónkba az elérési útvonalat írjuk be, a query névre hallgatóba pedig azt a lekérdezést, ami meghatározza a másolandó adatainkat, a mi esetünkben ez szimplán SELECT * FROM status (ez a táblám neve az Accessben).

Ezután jön az a lépés, amiről fentebb már volt szó, azaz az adatbázisunkat meg is nyitjuk, tehát a connection objektum Open metódusát használjuk és megadjuk az adatbázis típusát (értelemszerűen Access esetén a Provider könnyen meghatározható) és elérési útvonalát, ahogy fentebb már leírásra került:

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Path & ";"

Ezután jön nagyjából a lelke az egész kódnak, hiszen elkezdünk a recordsettel dolgozni. A Recordset objektum Open metódusát fogjuk felhasználni, amelynek segítségével egy táblát vagy lekérdezésünk eredményét tudjuk megnyitni paraméterek felhasználásával.

rs.Open query, cnn, adOpenStatic, adLockReadOnly

Ez lesz a felhasznált kódsorunk itt, amely értelemszerűen az Open metódus paramétereit tartalmazza (mind opcionális, sorban: Source, ActiveConnection, CursorType, LockType, Options). Elsőként a query jön a Sourcehoz, ami a lekérdezésünket jelenti, mint forrást. Aztán a cnn, ami az aktív kapcsolatot állítja be, majd az adOpenStatic kurzortípus, ami egy statikus kurzor, azaz egy statikus másolathoz férünk hozzá adatainkról, törléseket, változtatásokat nem fogunk látni (ennek ellenpárja tehát értelemszerűen az adOpenDynamic). A negyedik használt paraméterünk pedig az adLockReadOnly, ami pedig azt határozza meg, hogy miközben feldolgozunk, milyen lock legyen az adatainkon, azaz az adLockReadOnly csak olvasható adatokat jelent.
Itt tartunk most:

Nagyon kevés van már csak hátra, most jön a Recordset objektum CopyFromRecordset metódusának bevetése, ami jelen esetben az első sheetünk A1 cellájába fogja bemásolni az előbb megnyitott rs Recordsetünket:

Sheet1.Range("A1").CopyFromRecordset rs

Már csak az van hátra, hogy bezárjuk a recordsetünket és a kapcsolatot is befejezzük, majd mindkét változót kiürítsük:

rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub

Kódunk lefuttatása után pedig ott is az eredmény az első munkalapunkon:

Innentől kezdve pedig a határ a csillagos ég, hiszen egyrészt több táblából is szedhetünk össze adatokat, csak úgy kell megírnunk az SQL-t, másrészt az Excelben pedig már bármit megcsinálhatunk ezen adatokkal.

Szövegként a kód:

Sub access()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim query As String
Dim Path As String
Path = "C:\Database1.accdb"
query = "SELECT * FROM status"
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & Path & ";"
rs.Open query, cnn, adOpenStatic, adLockReadOnly
Sheet1.Range("A1").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Exit Sub
End Sub