Menggunakan Database Eksternal di MS Access Query

Umumnya, bila ingin membuat query di Access, kita menggunakan tabel yang sudah tersedia di Access Navigation Pane pada bagian objek Tables. Tabel yang tersedia di Access Navigation Pane itu bisa merupakan tabel lokal atau merupakan linked table yang bisa berasal dari berbagai macam database, entah itu FoxPro, Dbase III, atau bahkan MS Excel. Membuat query yang menggunakan database eksternal dengan linked table memang merupakan cara yang paling mudah untuk dilakukan. Namun demikian, hal ini bukan merupakan pokok bahasan dari posting berikut ini.

Database eksternal adalah database yang bukan merupakan database yang sedang dibuka. Kita dapat membuat query yang menggunakan database eksternal, baik melalui objek Queries yang ada di Access Navigation Pane, maupun melalui program dengan menuliskan kode VBA. Pada dasarnya, Access menyediakan dua macam cara untuk membuat query yang melibatkan database eksternal. Dua macam cara itu adalah:

1. Menggunakan klausa IN yang disediakan oleh MS Access SQL
2. Menggunakan string koneksi (connection string) setelah klausa FROM

Berikut ini pembahasan ringkasnya.

Menggunakan klausa IN yang disediakan oleh MS Access SQL

Microsoft Access SQL mempunyai klausa IN yang memungkinkan kita untuk mengakses database eksternal. Metode ini hanya bisa digunakan untuk satu database eksternal. Jadi, bila ingin menghubungkan dua atau lebih database eksternal, maka kita harus membuat dua atau lebih query yang masing-masing query berisi satu database eksternal. Setelah itu, kita membuat query untuk menggabungkan query yang berasal dari database eksternal.

Klausa IN terdiri dari dua bagian yaitu nama database dan string koneksi (connection string). Nama database harus lengkap, yaitu terdiri dari nama direktori atau path beserta nama file. Bila tidak ada nama direktori atau path, maka lokasi database eksternal itu diasumsikan sama dengan database yang sedang terbuka. Bagian kedua, koneksi string berisi tipe provider database dan parameter lain yang mungkin diperlukan (seperti user id, password, dsb).

Untuk menggunakan database eksternal, kita harus menambahkan tanda titik koma (semicolon) di posisi terakhir dari bagian string koneksi. Berikut ini adalah beberapa contoh penggunaan klausa IN untuk mengakses database eksternal.

Mengakses tabel yang bernama "Pelanggan" di database SALES dengan format aplikasi database dBASE IV.

SELECT pelangganID, namaPelanggan, alamatPelanggan FROM Pelanggan
IN "C:\DBASE\DATA\SALES" "dBASE IV;"
WHERE pelangganID Like "A*"

Mengakses tabel yang bernama "tblRekUtama " di database eksternal yang bernama Database_be.accdb dengan format aplikasi database MS Access.

SELECT * FROM tblRekUtama IN 'C:\Trial\Database_be.accdb';

Bila direktori database ekseternal berada di lokasi yang sama dengan database Access yang sedang terbuka, maka penulisan SQL menjadi sebagai berikut:

SELECT * FROM tblRekUtama IN 'Database_be.accdb';

Gambar 1. Query menggunakan database eksternal MS Excel 2007 di MS Access

Mengakses MS Excel Worksheet yang bernama "RekUtama" di database eksternal yang bernama tblRekUtama.xlsx dengan format aplikasi database MS Excel 12.0 (Excel 2007) (Gambar 1 di atas):

SELECT KodeRek,NamaRek,Grup
FROM [RekUtama$] IN "C:\Trial\tblRekUtama.xlsx" "EXCEL 12.0;";

atau

SELECT KodeRek,NamaRek,Grup
FROM [RekUtama$] IN 'C:\Trial\tblRekUtama.xlsx'[EXCEL 12.0;];

Pada contoh di atas, baris pertama dari tabel sumber yang ada di worksheet RekUtama diperlakukan sebagai header atau nama field. Bila baris pertama dari tabel sumber tidak diperlakukan sebagai header atau nama field, maka penulisan SQLnya adalah sebagai berikut

SELECT *
FROM [RekUtama$] IN "C:\Trial\tblRekUtama.xlsx" "EXCEL 12.0;HDR=No";

atau

SELECT *
FROM [RekUtama$] IN 'C:\Trial\tblRekUtama.xlsx'[EXCEL 12.0;HDR=No];

Gambar 2. Query menggunakan database eksternal berupa range MS Excel 2007 di MS Access

Mengakses range di MS Excel yang bernama "Contoh" di database eksternal yang bernama tblRekUtama.xlsx dengan format aplikasi database MS Excel 12.0 (Excel 2007) (Gambar 2 di atas):

SELECT *
FROM Contoh IN "C:\Trial\tblRekUtama.xlsx" "EXCEL 12.0;";

atau

SELECT *
FROM Contoh IN "C:\Trial\tblRekUtama.xlsx"[EXCEL 12.0;];

Baris pertama dari tabel sumber yang ada di range Contoh diperlakukan sebagai header atau nama field. Bila baris pertama dari tabel sumber tidak diperlakukan sebagai header atau nama field, maka penulisan SQLnya adalah sebagai berikut:

SELECT *
FROM Contoh IN "C:\Trial\tblRekUtama.xlsx" "EXCEL 12.0;HDR=No";

atau

SELECT *
FROM Contoh IN "C:\Trial\tblRekUtama.xlsx"[EXCEL 12.0;HDR=No];

Menggunakan connection string setelah klausa FROM

String koneksi untuk membuka objek database diletakkan setelah klausa FROM. Klausa FROM pada MS Access dapat menggunakan string koneksi database secara lengkap yang kemudian diikuti dengan nama tabel. String koneksi itu harus diletakkan di antar kurung siku pembuka ("[")  dan penutup ("]"). Di antara string koneksi dan nama tabel diberi tanda titik sebagai pemisahnya. Dengan menggunakan metode ini, kita bisa mengakses beberapa database eksternal secara bersamaan melalui query. Kita dapat membuat query yang berasal dari berbagai macam database, misalnya Excel dan Access (lokal maupun eksternal) secara bersamaan.

Mengakses tabel yang bernama "tblRekUtama " di database eksternal yang bernama Database_be.accdb dengan tipe provider database MS Access.

SELECT *
FROM [Database=C:\Trial\Database_be.accdb;MS ACCESS].tblRekUtama

Mengakses MS Excel Worksheet yang bernama "RekUtama" di database eksternal yang bernama tblRekUtama.xlsx dengan tipe provider database MS Excel 12.0 (Excel 2007).

SELECT *
FROM [database=C:\Trial\tblRekUtama.xlsx;EXCEL 12.0].[RekUtama$];

Hasilnya sama seperti Gambar 1 di atas.


Mengakses range di MS Excel yang bernama "Contoh" di database eksternal yang bernama tblRekUtama.xlsx dengan tipe provider database MS Excel 12.0 (Excel 2007).

SELECT *
FROM [database=C:\Trial\tblRekUtama.xlsx;EXCEL 12.0].Contoh;

Hasilnya sama seperti Gambar 2 di atas.

Bila baris pertama tidak diperlakukan sebagai header atau nama field, maka di bagian terakhir dari string koneksi ditambahkan parameter HDR=No, sama seperti contoh terdahulu.

Contoh di bawah ini lebih kompleks, dengan melibatkan dua buah tabel dari database eksternal untuk digabungkan melalui LEFT JOIN. Tabel pertama diambil dari sebuah range yang bernama Contoh di file MS Excel yang bernama tblRekUtama.xlsx yang merupakan tabel master. Tabel kedua diambil dari sebuah tabel bernama tblPermTransJournal_Child yang berada di dalam database Access bernama Database_be.accdb yang merupakan tabel transaksi.

SELECT q.KodeRek,q.NamaRek,q.Grup
FROM [database=C:\Trial\tblRekUtama.xlsx;EXCEL 12.0].Contoh AS q LEFT JOIN [Database=C:\Trial\Database_be.accdb;MS Access].tblPermTransJournal_Child ON q.KodeRek=tblPermTransJournal_Child.KodeRek GROUP BY q.KodeRek,q.NamaRek,q.Grup

Pada berbagai contoh di atas, teks string yang berada di dalam kurung siku adalah string koneksi yang akan diinterpretasikan oleh Access untuk memuat database yang diinginkan.

Manfaat Menggunakan Eksternal Database

1. Database eksternal dapat mengamankan data yang bersifat konfidensial. Contohnya, data gaji karyawan sebaiknya dibuat terpisah dari database yang utama dan diberi password. Untuk mengakses data gaji seperti ini, maka kita dapat membukanya dengan menuliskan Kode VBA yang digunakan untuk mengakses data gaji.
2. Jika ingin melakukan migrasi dari database lain ke MS Access, maka penggunaan ekstenal database seperti ini akan membantu.
3. Pada situasi tertentu, penggunaan database eksternal di MS Access dirasa lebih praktis dari pada di database lain.

Pembaca dapat memperoleh sample file database eksternal dengan cara sbb:

1. Ikuti blog Access Terapan di Facebook dengan mem-follow atau me-like FB page di https://www.facebook.com/AccessTerapan atau follow Google Blog Follower. Bagi yang tidak memfollow, permintaan (request) akan diabaikan.
2. Kirim email ke admin yanto.e.subroto@gmail.com untuk diikutsertakan menjadi anggota grup Access Terapan. 
3. Sebagai tanda bukti keanggotaan, admin grup Access Terapan akan mengirimkan email ke alamat yang dituju. 
4. Selanjutnya, silakan download  file zip bernama Trial.zip yang berisi tiga macam file, yaitu:
   
   1. qryExternalDbase.accdb: merupakan front-end database yang berisi query Access SQL
   2. Database_be.accdb: merupakan back-end database yang hanya berisi tabel-tabel dan bisa diakses melalui Access SQL.
   3. tblRekUtama.xlsx: merupakan file Excel yang diakses melalui Access SQL
5. Unzipped/ekstrak file Trial.zip di drive C:\ sehingga akan muncul C:\Trial
6. Buka file qryExternalDbase.accdb
7. Buka query yang ada dalam file qryExternalDbase.accdb satu per satu, baik secara Design View, SQL View, atau Datasheet View
8. Gunakan pernyataan yang ada dalam SQL di atas. Gantilah nilai "D:\Trial" yang ad di dalam SQL di atas sesuai dengan lokasi database eksternal.