Eksport & Import di Excel dengan bantuan ADO, VBA dan SQL (Bag. 1)

A.   Latar belakang

Beberapa pertanyaan mungkin timbul beberapa diantaranya mengapa harus menggunakan ADO + Macro ? kan ada fasilitas eksport dan import di Excel maupun Access. Ya kali, kalau cuma sesekali nggak apa. Tapi kalau sudah jadi pekerjaan rutin kan lebih mudah kalau tinggal sekali klik saja J. Dan dengan bantuan SQL, kita menentukan kriteria record yang akan kita ambil. Ini akan terasa sangat membantu pada saat data kita sudah mencapai ribuan atau bahkan puluhan ribu baris.

Atau mungkin kenapa harus dari Excel ke Access atau sebaliknya ? Excel punya kelebihan dalam hal formula, Pivot Table dan Chart untuk analisa data. Access punya kelebihan relasi antara Tabel, Query dan Integritas antar relasi nya. Paling tidak buat saya pribadi, kalau kita menggabungkan kedua kelebihan pada kedua aplikasi ini maka akan banyak hal yang dapat dilakukan.

B.    Sekilas tentang ADO

gambaran mengenai ADO di Excel

Sebelum melangkah lebih jauh, ada baiknya apabila kita mengenal sedikit apakah ADO itu. ADO adalah singkatan dari ActiveX Data Object. ADO adalah teknologi yang diciptakan Microsoft sebagai perantara untuk mengakses berbagai macam Database. Database dapat berupa file Microsoft Access, Excel, Dbase, SQL Server dan sebagainya. Sebelum ADO, teknologi sejenis adalah DAO (Data Access Object) dan RDO (Remote Data Object). Pengembangan dari ADO adalah ADO.net yang saat ini banyak digunakan pada pemrograman dot Net.

C.    Obyek Connection dan Recordset

Komponen paling penting dalam ADO adalah obyek Connection / Koneksi dan Recordset / kumpulan data. Kedua obyek ini apabila di ibaratkan sama hal nya apabila kita membutuhkan satu buah folder berisikan data / informasi dari sebuah instansi atau departemen. Nomer telpon instansi yang dimaksud di ibaratkan sama dengan obyek Connection. Dan folder berisikan data yang kita butuhkan di ibarakan sama dengan obyek Recordset.

Langkah pertama yang harus kita lakukan apabila kita membutuhkan sekumpulan data dari sebuah Instansi adalah kita harus mengetahui dulu nomer telpon instansi tersebut. Nomer telpon nya harus benar. Kalau perlu, nomer ekstension petugas terkait juga harus jelas. Sesudah itu kita dapat menghubungi nomer telpon tersebut dan memberikan informasi informasi apa saja yang kita butuhkan. Apabila informasi yang kita butuhkan kepada petugas benar, maka hasil nya (sebuah Recordset) sudah tentu juga benar dan sesuai.

Setelah komunikasi selesai, dan data sudah kita dapatkan, maka koneksi telpon tadi dapat kita tutup. Dengan menggunakan contoh diatas secara garis besar maka kita dapat susun langkah-langkah nya sebagai berikut :

•  Buka koneksi / Connection
•  Buka recordset
•  Berdasarkan koneksi diatas, jelaskan kriteria Recordset (melalui perintah SQL)
•  Kalau sudah dapat Recordset, tutup recordset
•  Tutup koneksi / Connection

Secara garis besar pola nya dapat dilihat pada langkah nomer 1 dan 5, kemudian 2 dan 4. Yaitu buka koneksi dulu, buka recordset, tutup recordset nya dulu, baru tutup koneksinya.

Ada banyak komponen lain selain Connection dan Recordset.  Seperti Connection String, Cursor, Lock Type dan lain-lain. Untuk bahasan ini kita akan bahas sampai dengan Connection String saja. Connection String ini adalah property atau bagian dari Obyek Connection. Pada Connection String inilah kita menentukan tipe database yang akan kita Eksport atau Import dari dan ke Excel beserta atribut-atributnya.

D. Metode Early dan Late Binding

Untuk melakukan Eksport ataupun Import dari dan ke Microsoft Excel dapat dilakukan dengan dua cara yaitu Early dan Late Binding. Late Binding ini agak sedikit ribet. Tapi jangan khawatir, nggak ribet banget kok. Yang harus tetap di ingat adalah 2 obyek diatas : Connection dan Recordset ditambah Connection String nya.

a.       Metode Early Binding

Menu Refferences ADO Library

Sesuai dengan namanya, Early = Awal, yaitu kumpulan obyek-obyek ADO seperti Connection dan Recordset yang disebutkan diatas tadi, atau disebut Library, referensi nya kita atur di awal. Caranya adalah melalui menu VBE (Alt + F11) – Tools – Refference. Scroll ke bawah dan Pilih “Microsoft Active X Data Object Library”. Akan ada beberapa pilihan dengan angka dibelakangnya sebagai versi. Pilih versi paling tinggi. Di laptop yang saya gunakan dengan Windows 10 dan Office 2013 versi tertinggi referensi ADO Library nya adalah versi 6.1.

Keuntungan menggunakan metode ini adalah pada saat menuliskan skrip, Excel akan menunjukan property dan metode-metode (methods) yang terkait melalui intellisense nya. Contoh apabila kita akan membuka koneksi dengan menuliskan con sebagai variabel koneksi, maka metode Open dan Close akan langsung terlihat begitu kita ketik “con.” (con dan titik).

b.      Metode Late Binding

Metode ini adalah lawan dari metode Early Binding. Yaitu pengaturan referensi kita lakukan belakangan dengan cara mendeklarasikan obyek-obyek nya melalui skrip (Create Object). Baik obyek connection maupun recordset. Setelah obyek nya ada, barulah kita definisikan obyek-obyek tersebut. Obyek mana yang menjadi Connection dan obyek mana yang menjadi Recordset.

Dengan tidak adanya referensi library sebelumnya, maka kekurangan nya tidak adanya fasilitas intellisense. Sehingga kita harus hafal baik property, methods maupun event nya.

Sebaliknya, kita tidak perlu khawatir saat aplikasi kita berpindah ke pc lain. Aplikasi dapat langsung digunakan. Mungkin yang perlu dilakukan hanyalah di property connection string.

Terus pilih yang mana ? Microsoft sendiri menyarankan untuk menggunakan Early Binding (https://support.microsoft.com/en-us/help/245115/using-early-binding-and-late-binding-in-automation). Dari sisi performa menggunakan Early Binding jelas lebih cepat. Karena referensi nya sudah kita tentukan di awal. Pada metode Late Binding, pada saat aplikasi di jalankan (runtime) aplikasi masih harus menciptakan sebuah Obyek dahulu baru mendefinisikan nya menjadi bagian dari ADO. Untuk lebih jelasnya ikuti terus bahasannya lebih lanjut.