Selain Excel, MS Access memang sudah unggul dan tidak diragukan lagi kehebatannya.

Sejak versi 2007 sudah lebih dari satu juta baris yang tersedia dalam Access bahkan ini melebihi catatan volume data yang tersimpan di Excel.

Kemampuan penyimpanan database Access yang luar biasa besar itu jika dipadukan dengan VBA Excel akan mampu menjadikannya sebagai suatu kombinasi manajemen data yang sangat kuat.

Kali ini kami akan berbagi tip sederhana mengirim data dari workbook Excel ke dalam data tabel di Access.

Kami tidak menjelaskan cara membuat database di Access, anggap saja Anda telah mampu membuat setidaknya sebuah tabel database.

Sebagai contoh ada sebuah database Access bernama Database1.accdb yang isinya adalah sebuah tabel bernama Table1 dengan enam kolom isian di dalamnya.

Tabel Database Access

Di Sheet1 dalam workbook Excel Anda telah mencatat beberapa data karyawan tambahan yang akan ditransfer pada tabel yang ada di Access.

Tabel Database Excel

Agar tabel Access dapat secara otomatis memperbarui data berdasarkan catatan yang ada di Excel, maka Anda harus memastikan agar susunan kolom tabel Excel dan kolom kotak isian Access harus sama, artinya judul kotak isian harus diberi nama yang sama seperti pada tabel Access.

Untuk menghindari terjadinya error karena konflik data pada setiap kotak isian Excel maupun Access, maka pastikan pula agar jenis data telah sesuai, contohnya jika kotak isian pada tabel Access adalah berjenis data Number maka pada Excel harus format Number juga.

Ya langsung saja kita mulai...

Berikut ini langkah-langkah yang harus Anda ikuti:

1. Pertama-tama yang wajib Anda lakukan, dalam Excel silakan buka VBE kemudian aktifkan Microsoft ActiveX Data Objects 2.8 Library pada menu Tools > References... tandai centang lalu klik OK.

2. Buka menu Insert > Module

3. Macro Excel yang akan kita buat bisa Anda namakan dengan TambahData untuk mentrasfer ketiga data karyawan baru dari worksheet Excel ke dalam Table1 file Database1. Silakan ketik dalam Module lalu tekan Enter:

Sub TambahData()

3. Di bawahnya ketik variabel sebagai berikut:

Dim Sambungan As ADODB.Connection
Dim NamaFile As String
Dim Dataset As ADODB.Recordset
Dim Baris As Long, Kolom As Long
Dim BarisAkhir As Long

4. Perintah ini untuk membuka worksheet yang berisi data karyawan yang mau ditransfer ke Access:

Worksheets("Sheet1").Activate

5. Tentukan baris akhir data sepanjang kolom A dengan kode:

BarisAkhir = Cells(Rows.Count, 1).End(xlUp).Row

6. Buat sambungan database-nya:

Set Sambungan = New ADODB.Connection

7. Tentukan nama file database Anda sesuai alamat di komputer Anda:

NamaFile = "C:\Alamat\File\Anda\Database1.accdb"

8. Tentukan penyedia data dan bukalah sambungan datanya:

With Sambungan
.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & NamaFile & _
";Persist Security Info=False;"
.Open NamaFile
End With

9. Perintah ini untuk menyimpan data di Access:

Set Dataset = New ADODB.Recordset
Dataset.CursorLocation = adUseServer
Dataset.Open Source:="Table1", _
ActiveConnection:=Sambungan, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable

10. Perintah ini memuat data yang ada pada baris dan kolom Excel ke Access, anggap saja baris 1 adalah baris judul maka kita mulai dari baris kedua:

For Baris = 2 To BarisAkhir
Dataset.AddNew

11. Kolom berjumlah 6 buah dimulai dari kolom A maka tulis:

For Kolom = 1 To 6
Dataset(Cells(1, Kolom).Value) = Cells(Baris, Kolom).Value
Next Kolom
Dataset.Update
Next Baris

12. Perintahkan macro untuk menutup sambungan:

Dataset.Close
Sambungan.Close

13. Lepas memori variabel objek:

Set Dataset = Nothing
Set Sambungan = Nothing

14. Tambahkan konfirmasi bahwa proses telah selesai:

MsgBox "Transfer sukses!", , "Berhasil!"


Kode macro lengkap sebagai berikut:

Sub TambahData()
Dim Sambungan As ADODB.Connection
Dim NamaFile As String
Dim Dataset As ADODB.Recordset
Dim Baris As Long, Kolom As Long
Dim BarisAkhir As Long
Worksheets("Sheet1").Activate
BarisAkhir = Cells(Rows.Count, 1).End(xlUp).Row
Set Sambungan = New ADODB.Connection
NamaFile = "C:\Alamat\File\Anda\Database1.accdb"
With Sambungan
.Provider = "Microsoft.ACE.OLEDB.12.0;Data Source=" & NamaFile & _
";Persist Security Info=False;"
.Open NamaFile
End With
Set Dataset = New ADODB.Recordset
Dataset.CursorLocation = adUseServer
Dataset.Open Source:="Table1", _
ActiveConnection:=Sambungan, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdTable
For Baris = 2 To BarisAkhir
Dataset.AddNew
For Kolom = 1 To 6
Dataset(Cells(1, Kolom).Value) = Cells(Baris, Kolom).Value
Next Kolom
Dataset.Update
Next Baris
Dataset.Close
Sambungan.Close
Set Dataset = Nothing
Set Sambungan = Nothing
MsgBox "Transfer sukses!", , "Berhasil!"
End Sub

OK silakan Anda uji coba kode macro yang telah Anda buat di atas. Selamat mencoba. Semoga bermanfaat dan sukses.