Menampilkan Data dari Access ke Excel Menggunakan Microsoft Query

Microsoft (MS) Query adalah SQL Statement yang terdapat di MS Excel. MS Excel, seperti halnya MS Access menggunakan ACE.OLEDB atau JET.OLEDB provider dari Windows untuk menjalankan query.

Meskipun merupakan program yang kemampuannya tidak selevel dengan program Office, MS Query dapat membantu pengguna Excel untuk meningkatkan kinerja dalam mengolah data secara efisien, terutama dalam hal yang berhubungan dengan data eksternal. Dengan MS Query, kita bisa menggabungkan dua atau lebih tabel untuk membentuk query baru yang untuk selanjutnya ditampilkan dalam bentuk tabel, tabel pivot,  atau pun grafik di lembar kerja Excel.


Pengguna MS Query tidak perlu mengerti secara detail apa itu SQL, karena program itu telah menyediakan fitur Query Wizard yang sangat mudah dipahami oleh mereka yang awam tentang database.

Untuk menggunakan MS Query, ikuti latihan berikut ini.

Sebagai tahap awal, buka aplikasi database Access dan spreadsheet Excel. Susun kedua aplikasi secara berdampingan seperti berikut ini.

Gambar 1 Access dan Excel dibuka secara bersamaan

Pada aplikasi Access, kita menggunakan contoh database harga saham yang berisi satu buah tabel trx2007. Untuk memeperoleh database dan tabel ini, silakan download filenya di posting yang berjudul Membuat Grafik Candlestick (OHLC) di Access.  Tabel itu terdiri dari banyak kolom. Kolom yang diperlukan adalah STK_DATE (tanggal), STOK_CODE (kode saham), dan seterusnya sampai kolom STK_AMNT (total nilai rupiah saham yang ditransaksikan). Dengan tabel trx2007 ini, kita akan membuat report di Excel menggunakan Microsoft Query. Selanjutnya, kita  berpindah ke MS Excel.

Klik ribbon Data > Get External Data > From Other Sources > From Micorsoft Query

Gambar 2. Form Microsoft Query

Pada kotak dialog Choose Data Source, Pilih MS Access Database, dan tekan OK

Gambar 3. Memilih Data Source

Pada kotak Select Database, pilih nama database yang ingin dibuka, termasuk Drives, Directories, dll. Tekan OK setelah lengkap.

Gambar 4. Memilih Database

Pada kotak dialog Query Wizard, Choose Columns, pilihlah query atau tabel yang ada dalam database Access. Setelah itu pilih field apa saja yang diinginkan...

Gambar 5. Memilih kolom atau field

Sorot field dan tekan tanda lebih besar (>) untuk memilih field ke dalam Colum in your query. Bila semua field mencukupi, tekan tombol Next...

Pada langkah kedua dari Query Wizard, Filter Data, pilih kriteria yang diinginkan. Dalam hal ini kriterianya adalah STK_Code Equals AALI. Tekan Next...

Gambar 6. Membuat kriteria atau filter data

Pada langkah ketiga dary Query Wizard, Sort Order, pilih Sort by yang diinginkan. Dalam hal ini fieldnya adalah STK_DATE Asecending. Tekan Next...

Gambar 7. Mengurutkan tabel berdasarkan tanggal

Pada langkah terakhir dary Query Wizard, Finish, untuk pertanyaan What would you like to do next?, pilih Return Data to Microsoft Excel. Tekan Finish..

Gambar 8. Akhir dari query wizard

Pilih cell B4 dan Tekan OK

Gambar 9. Menempatkan posisi tabel dalam worksheet

Tabel yang berasal dari database Access bernama GrafikCandlestik.accdb telah
ditampilkan di Excel. Tampilkan aplikasi MS Excel secara maksimal dengan menekan tombol Maximize. Lalu, taruh cellpointer ke sel B2.

Gambar 10. Memaksimalkan jendela Excel

Ketikkan Kode Stok lalu geser cellpointer ke C2. Selanjutnya, kita membuat macro dengan
merekam setiap langkah melalui Record Macro. Klik View di ribbon > Macros > Record Macro... dan beri nama: Macro1, lalu tekan OK untuk memulai perekaman.

Pindahkan cellpointer ke bagian Header dari tabel di cell B4. Klik mouse kanan untuk
menampilkan menu shortcut. Pilih Table > Edit Query... Pada kotak dialog Query Wizard, tekan Next untuk menuju langkah kedua, Filter Data. Pada bagian criteria, gantilah AALI menjadi BUMI. Tekan Next sampai langkah terakhir dan pilih Finish untuk mengakhiri Query Wizard.

Nilai pada kolom STK_CODE telah berganti, dari AALI menjadi BUMI. Untuk mengakhiri macro, klik grup Macros, lalu pilih Stop Recording...

Selanjutnya, pada grup Macros, pilih View Macros. Sorot Macro1 pada Macro Name, lalu tekan Edit. Jendela Microsoft VBA ditampilkan Berikut ini adalah fungsi yang ada di VBA:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("Table_Query_from_MS_Access_Database[[#Headers],[STK_DATE]]").Select
    With Selection.ListObject.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=D:\AccessTerapan\JSX\GrafikCandlestick.accdb;DefaultDir=D:\AccessTerapan\JSX;DriverId=25;FIL=MS Acce" _
        ), Array("ss;MaxBufferSize=2048;PageTimeout=5;"))
        .CommandText = Array( _
        "SELECT Trx2007.STK_DATE, Trx2007.STK_CODE, Trx2007.STK_OPEN, Trx2007.STK_HIGH, Trx2007.STK_LOW, Trx2007.STK_CLOS, Trx2007.STK_VOLM, Trx2007.STK_PREV, Trx2007.STK_CHG, Trx2007.STK_AMNT" & Chr(13) & "" & Chr(10) & "FROM `D:\Access" _
        , _
        "Terapan\JSX\GrafikCandlestick.accdb`.Trx2007 Trx2007" & Chr(13) & "" & Chr(10) & "WHERE (Trx2007.STK_CODE='BUMI')" _
        )
        .Refresh BackgroundQuery:=False
    End With
End Sub

Gantilah BUMI dengan formula berikut ini: " & Range("C2").Value & " . Formula ini merujuk pada cell C2 yang berisi kode stok yang dapat diubah sesuai dengan data yang tersimpan di tabel database Access (tabel Trx2007)

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("Table_Query_from_MS_Access_Database[[#Headers],[STK_DATE]]").Select
    With Selection.ListObject.QueryTable
        .Connection = Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=D:\AccessTerapan\JSX\GrafikCandlestick.accdb;DefaultDir=D:\AccessTerapan\JSX;DriverId=25;FIL=MS Acce" _
        ), Array("ss;MaxBufferSize=2048;PageTimeout=5;"))
        .CommandText = Array( _
        "SELECT Trx2007.STK_DATE, Trx2007.STK_CODE, Trx2007.STK_OPEN, Trx2007.STK_HIGH, Trx2007.STK_LOW, Trx2007.STK_CLOS, Trx2007.STK_VOLM, Trx2007.STK_PREV, Trx2007.STK_CHG, Trx2007.STK_AMNT" & Chr(13) & "" & Chr(10) & "FROM `D:\Access" _
        , _
        "Terapan\JSX\GrafikCandlestick.accdb`.Trx2007 Trx2007" & Chr(13) & "" & Chr(10) & "WHERE (Trx2007.STK_CODE='" & Range("C2").Value & "')" _
        )
        .Refresh BackgroundQuery:=False
    End With
End Sub

Bila sudah selesai mengedit, tutuplah jendela VBA atau kembali ke Excel untuk menguji macro yg baru saja diedit. Caranya, di sel C2 ketikan kode saham, misalnya AALI, BMRI atau lainnya. Setelah itu klik View > Macros > View Macros... Sorot Macro1 dan klik tombol Run...

Nilai pada STK_CODE telah berubah sesuai dengan nilai yang ada di sel C2. Kita dapat menyimpan file Excel dengan menggunakan ekstensi xlsm, misalnya book1.xlsm seperti Gambar 11 di bawah ini. Setelah itu, sisipkan sebuah tombol perintah dan berikan penugasan ke tombol itu untuk menjalankan
macro Macro1. Supaya mudah dipahami, gantilah caption pada "Button 1" menjadi "Tampilkan".

Gambar 11. Tabel setelah ada penambahan tombol

Tabel yang terhubung dengan MS Access Database melalui Microsoft Query siap digunakan. Silakan coba masukkan di sel C2, klik tombol Tampilkan, dan lihat hasilnya. Petunjuk lengkap dapat dilihat di video di awal posting ini.