Basis data akses kueri excel vba

Fokus artikel ini adalah untuk mendemonstrasikan cara menggunakan ADO/ADOX, dalam proyek Excel VBA, untuk memanipulasi tampilan dan prosedur Access, secara kolektif disebut sebagai 'kueri'. Sebenarnya, umumnya saat berhadapan dengan Microsoft Access, DAO (Data Access Objects) akan menjadi pustaka objek yang lebih baik daripada ADO. ADOX, bagaimanapun, dapat diperluas ke database lain, bukan hanya Microsoft Access

Table of Contents Show

Buat Kueri Sederhana
Buat Permintaan Tindakan
Buat Kueri Parameter
Hapus Kueri
Jalankan Permintaan Tindakan
Jalankan Kueri Pilih
Jalankan Permintaan Parameter
Bagaimana cara menjalankan kueri di Access dari Excel VBA?
Bagaimana cara mengekstrak data dari database Access ke Excel menggunakan VBA?
Bagaimana cara menanyakan database Access di Excel?
Bisakah Anda meminta database Access?

ADOX (Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security ) adalah ekstensi untuk objek ADO dan model pemrograman. ADOX menyertakan objek untuk pembuatan dan modifikasi skema, serta keamanan. Itu memaparkan objek tambahan untuk membuat, memodifikasi, dan menghapus objek skema, seperti tabel, kueri, dan prosedur

Kemampuan ADOX jauh melampaui apa yang dijelaskan dalam artikel ini. Untuk informasi lebih lanjut tentang ADOX, lihat tautan di

Menyimpan pernyataan SQL yang panjang dan rumit dalam kode Excel VBA Anda bisa jadi berantakan. Itu juga membuatnya sedikit lebih sulit untuk dimodifikasi, apalagi fakta bahwa beberapa prosedur SQL mungkin dapat digunakan kembali, baik dalam proyek yang sama atau oleh proyek lain. Sebaiknya pertahankan kueri SQL Anda di dalam database itu sendiri. Oleh karena itu, seseorang harus menggunakan Excel VBA untuk mengeksekusi kueri, daripada berisi kode dan logika kueri

Artikel berikut bertujuan untuk mengilustrasikan bagaimana Anda bisa membuat, mengubah, menghapus, dan mengeksekusi kueri basis data, menggunakan Excel VBA. Lihat lebih jauh ke bawah untuk konteks tentang cara mengimplementasikan sub-rutin berikut

Buat Tampilan atau Prosedur baru

Kode VBA untuk membuat kueri atau prosedur Access

Dasar visual

Publik Enum ViewOrProc

Lihat

Proses

Akhir Enum

Publik Sub CreateQuery(ByVal strDBPath As String, ByVal strSql As String, ByVal strQueryName As String, ByVal vpType As ViewOrProc)

Redup objCat Sebagai Objek

Redup objCmd Sebagai Objek

Aktif Kesalahan GoTo exit_point

Set objCat = CreateObject(Class:="ADOX. Katalog")

objCat. ActiveConnection = "Provider=Microsoft. KARTU AS. OLEDB. 12. 0;" & _

"Data Source=" & strDBPath &< ";" & _

"Jet OLEDB. Tipe Mesin=5;" & _

"Persist Security Info=False;"

Set objCmd = CreateObject(Class:="ADODB. Perintah")

objCmd. CommandText = strSql

Jika vpType = Lihat Then

Panggil objCat. Tampilan. Lampirkan(Nama. =strQueryName, Perintah. =objCmd)

ElseIf vpType = Proc Then

Panggil objCat. Prosedur. Lampirkan(Nama. =strQueryName, Perintah. =objCmd)

Akhiri Jika

titik_keluar.

Atur objCat = Tidak ada

Jika Err. Bilangan Kemudian

Telepon Err. Naikkan(Angka. =Err. Nomor, Deskripsi. =Err. Deskripsi)

Akhiri Jika

Akhir Sub

Sub-rutin ini memiliki argumen berikut

strDBPath. Yg dibutuhkan. Jalur folder direktori tempat database Access disimpan
strSql. Yg dibutuhkan. SQL kueri/prosedur yang ingin Anda buat
vpType. Yg dibutuhkan. Lihat jika Anda ingin menambahkan kueri ke koleksi Tampilan, jika tidak, Prosedur untuk menambahkannya ke koleksi Prosedur. Gunakan koleksi Prosedur untuk Parameter Queries (Prosedur Tersimpan). Gunakan koleksi Tampilan untuk yang lainnya

Ubah Tampilan atau Prosedur yang ada

Kode VBA untuk mengubah kueri atau prosedur Access

Dasar visual

Publik Enum ViewOrProc

Lihat

Proses

Akhir Enum

Publik Sub ModifyQuery(ByVal strDBPath As String, ByVal strSql As String, ByVal strQueryName As String)

Redup objCat Sebagai Objek

Redup objCmd Sebagai Objek

Redup vpType Sebagai ViewOrProc

Aktif Kesalahan GoTo exit_point

Set objCat = CreateObject(Class:="ADOX. Katalog")

objCat. ActiveConnection = "Provider=Microsoft. KARTU AS. OLEDB. 12. 0;" & _

"Data Source=" & strDBPath &< ";" & _

"Jet OLEDB. Tipe Mesin=5;" & _

"Persist Security Info=False;"

Aktif Kesalahan Lanjutkan Selanjutnya

Set objCmd = objCat.Tampilan(strQueryName). Perintah

Jika Tidak objCmd Adalah Nothing Then

vpType = Lihat

Lainnya

Set objCmd = objCat.Prosedur(strQueryName). Perintah

Jika Tidak objCmd Apakah Nothing Then

vpType = Proc

Akhir Jika

Aktif Kesalahan GoTo exit_point

Jika objCmd Tidak Tidak Ada Then GoTo exit_point

objCmd. CommandText = strSql

Jika vpType = Lihat Then

Set objCat. Tampilan(strQueryName). Perintah = objCmd

ElseIf vpType = Proc Then

Set objCat. Prosedur(strQueryName). Perintah = objCmd

Akhiri Jika

titik_keluar.

Atur objCat = Tidak ada

Jika Err. Bilangan Kemudian

Telepon Err. Naikkan(Angka. =Err. Nomor, Deskripsi. =Err. Deskripsi)

Akhiri Jika

Akhir Sub

Sub-rutin ini memiliki argumen berikut

strDBPath. Yg dibutuhkan. Jalur folder direktori tempat database Access disimpan
strSql. Yg dibutuhkan. SQL kueri/prosedur yang ingin Anda ganti dengan SQL kueri yang ada
strNamaKueri. Yg dibutuhkan. Nama kueri (Prosedur atau Tampilan) yang ingin Anda ubah

Hapus Tampilan atau Prosedur yang ada

Kode VBA untuk menghapus kueri atau prosedur Access

Dasar visual

Publik Sub DeleteQuery(ByVal strDBPath As String, ByVal strQueryName As String)

Redup objCat Sebagai Objek

Redup lngCount Sebagai Panjang

Set objCat = CreateObject(Class:="ADOX. Katalog")

objCat. ActiveConnection = "Provider=Microsoft. KARTU AS. OLEDB. 12. 0;" & _

"Data Source=" & strDBPath &< ";" & _

"Jet OLEDB. Tipe Mesin=5;" & _

"Persist Security Info=False;"

Dengan objCat

lngCount = . Prosedur. Hitung + . Tampilan. Hitung

Aktif Kesalahan Lanjutkan Berikutnya

Hubungi . Prosedur. Hapus(strQueryName)

Hubungi . Tampilan. Hapus(strQueryName)

Aktif Kesalahan GoTo exit_point

Jika . Prosedur. Hitung + . Tampilan. Hitung = lngHitung Kemudian

Err. Nomor = 3265

Err. Deskripsi = "Item tidak dapat ditemukan dalam koleksi yang sesuai dengan nama atau urutan yang diminta. "

Akhir Jika

Akhiri Dengan

titik_keluar.

Atur objCat = Tidak ada

Jika Err. Bilangan Kemudian

Telepon Err. Naikkan(Angka. =Err. Nomor, Deskripsi. =Err. Deskripsi)

Akhiri Jika

Akhir Sub

Sub-rutin ini memiliki argumen berikut

strDBPath. Yg dibutuhkan. Jalur folder direktori tempat database Access disimpan
strNamaKueri. Yg dibutuhkan. Nama kueri (Prosedur atau Tampilan) yang ingin Anda hapus

Jalankan Tampilan atau Prosedur yang ada

Kode VBA untuk menjalankan kueri atau prosedur Access

Dasar visual

Publik Fungsi RunQuery(ByVal strDBPath As String, ByVal strQueryname As String, ParamArray parArgs() As Variant) As Object

Redup objCmd Sebagai Objek

Redup objRec Sebagai Objek

Redup varArgs() As Variant

Redup lngPrm Sebagai Panjang

Set objCmd = CreateObject("ADODB.Command")

Jika Bound(parArgs) = -1 Then

varArgs = VBA. Larik("")

Lainnya

varArgs = parArgs

Akhiri Jika

Dengan objCmd

. ActiveConnection = "Provider=Microsoft. KARTU AS. OLEDB. 12. 0;" & _

"Data Source=" & strDBPath &< ";" & _

"Jet OLEDB. Tipe Mesin=5;" & _

"Persist Security Info=False;"

. CommandText = strQueryname

Jika Bound(parArgs) = -1 Then

Atur objRec = . Jalankan(Opsi. =4)

Lainnya

varArgs = parArgs

Atur objRec = . Jalankan(Parameter. =varArgs, Opsi. =4)

Aktif Kesalahan Lanjutkan Berikutnya

Untuk lngPrm = . Parameter. Hitung - 1 Ke 0 Step -1

Telepon . Parameter. Hapus(lngPrm)

Berikutnya lngPrm

Aktif Kesalahan Buka 0

Akhir Jika

Akhiri Dengan

Atur RunQuery = objRec

Atur objRec = Tidak ada

Akhir Fungsi

Sub-rutin ini memiliki argumen berikut

strDBPath. Yg dibutuhkan. Jalur folder direktori tempat database Access disimpan
strNamaKueri. Yg dibutuhkan. Nama kueri (Prosedur atau Tampilan) yang ingin Anda jalankan
parArgs. Opsional. Parameter Prosedur

Contoh

* Catatan. Saat istilah 'Tampilan' atau 'Proc' digunakan sebagai argumen sub-rutin, ini mengacu pada Enum ViewOrProc (seperti yang diilustrasikan dalam beberapa prosedur sebelumnya)

Buat Kueri Sederhana

Kode VBA untuk membuat Kueri Sederhana

Dasar visual

Publik Sub CreateSimpleQuery()

Const strDB Sebagai String = "C:\Demo.accdb"

Const strSql Sebagai String = "SELECT * FROM [Personnel] WHERE [Personnel].[Division]='HR';"

Const strQueryName Sebagai String = "qrySimple"

Panggil CreateQuery(strDB, strSql, strQueryName, View)

Akhir Sub

Ini akan membuat kueri baru, dalam koleksi Views, yang disebut 'qrySimple'. Kueri Sederhana (terkadang hanya disebut sebagai 'Kueri Pilih') hanya mengambil data dari salah satu tabel/tampilan lainnya

Buat Permintaan Tindakan

Kode VBA untuk membuat Action Query

Dasar visual

Publik Sub CreateActionQuery()

Const strDB Sebagai String = "C:\Demo.accdb"

Const strSql Sebagai String = "UPDATE [Personnel] " & _

"SET [Personil]. [LAST_NAME] = Ganti([LAST_NAME],""-"","" "") " & _

"DI MANA ((([Personil]]. [LAST_NAME]) Seperti ""*-*""));"

Const strQueryName Sebagai String = "qryAction"

Panggil CreateQuery(strDB, strSql, strQueryName, View)

Akhir Sub

Ini akan membuat kueri baru, dalam koleksi Views, yang disebut 'qryAction'. Kueri Tindakan adalah jenis kueri yang dapat menambah, mengubah, atau menghapus rekaman atau objek

Buat Kueri Parameter

Kode VBA untuk membuat Parameter Query

Dasar visual

Publik Sub CreateParameterQuery()

Const strDB Sebagai String = "C:\Users\Jon von der Heyden\Desktop\Desktop Files\test.accdb"

Const strSql Sebagai String = "PARAMETERS prmDivision LongText; " & _

"PILIH * DARI [Personil] " & _

"DI MANA [Personil]. [Pembagian] = prmPembagian;"

Const strQueryName Sebagai String = "qryParams"

Panggil CreateQuery(strDB, strSql, strQueryName, Proc)

Akhir Sub

Ini akan membuat kueri baru, dalam kumpulan Prosedur, yang disebut 'qryParams'. Kueri Parameter (biasanya disebut sebagai 'Prosedur Tersimpan'), dapat berupa Kueri Sederhana atau Kueri Tindakan. Ini dibedakan karena memungkinkan penelepon untuk melewati satu atau lebih parameter. Dalam pameran ini kueri akan menghasilkan tabel hasil dari tabel yang disebut 'Personil', namun hanya untuk divisi tertentu. 'Divisi' adalah parameternya, yang juga merupakan kolom/bidang di tabel 'Personil'

Ubah Kueri

Kode VBA untuk mengubah Kueri

Dasar visual

Publik Sub ModifyAQuery()

Const strDB Sebagai String = "C:\Demo.accdb"

Const strSql Sebagai String = "UPDATE [Personnel] " & _

"SET [Personil]. [LAST_NAME] = Ganti([LAST_NAME],""-"","" "") " & _

"DI MANA ((([Personil]]. [LAST_NAME]) Suka ""%-%""));"

Const strQueryName Sebagai String = "qryAction"

Panggil ModifyQuery(strDB, strSql, strQueryName)

Akhir Sub

Kode ini menggantikan SQL yang berisi 'qryAction' dengan SQL di strSQL

Hapus Kueri

Kode VBA untuk menghapus Permintaan

Dasar visual

Publik Sub DeleteAQuery()

Const strDB Sebagai String = "C:\Demo.accdb"

Const strQueryName Sebagai String = "qryOld"

Panggil DeleteQuery(strDB, strQueryName)

Akhir Sub

Ini akan menghapus kueri yang disebut 'qryOld'. Setelah dihapus, tindakan tidak dapat diurungkan / dicabut

Jalankan Permintaan Tindakan

Kode VBA untuk mengeksekusi Action Query

Dasar visual

Publik Sub RunActionQuery()

Const strDB Sebagai String = "C:\Demo.accdb"

Const strQueryName Sebagai String = "qryAction"

Panggil RunQuery(strDB, strQueryName)

Akhir Sub

Ini akan menjalankan Permintaan Tindakan yang disebut 'qryAction'. Tidak diperlukan kode lebih lanjut, karena kami tidak mengharapkan hasil apa pun untuk dikembalikan

Jalankan Kueri Pilih

Kode VBA untuk menjalankan Select Query

Dasar visual

Publik Sub RunSelectQuery()

Redup objRec Sebagai Objek

Redup lngField Sebagai Panjang

Const strDB Sebagai String = "C:\Demo.accdb"

Const strQueryName Sebagai String = "qrySimple"

Aktif Kesalahan Lanjutkan Selanjutnya

Atur objRec = RunQuery(strDB, strQueryName, "HR")

Aktif Kesalahan Buka 0

Jika Tidak objRec Adalah Nothing Then

Dengan Sheet1. Rentang("A1")

Untuk lngField = 1 To objRec.Bidang. Hitung

. Sel(1, lngField . ).Nilai = objRec. Bidang(lngBidang - 1 . ).Nama

Berikutnya lngField

Hubungi . Offset(1, 0 . ).CopyFromRecordset(objRec)

Akhiri Dengan

Akhiri Jika

Atur objRec = Tidak ada

Akhir Sub

Kami berharap bahwa menjalankan kueri yang disebut 'qrySimple' akan menghasilkan tabel hasil. Hasilnya dikembalikan ke objek ADO Recordset. Kode ini kemudian mengilustrasikan cara menjatuhkan hasil recordset ke sheet

Jalankan Permintaan Parameter

Kode VBA untuk menjalankan Parameter Query

Dasar visual

Publik Sub RunParamQuery()

Redup objRec Sebagai Objek

Redup lngField Sebagai Panjang

Const strDB Sebagai String = "C:\Demo.accdb"

Const strQueryName Sebagai String = "procPersonnelByDivision"

Aktif Kesalahan Lanjutkan Selanjutnya

Atur objRec = RunQuery(strDB, strQueryName, "HR")

Aktif Kesalahan Buka 0

Jika Tidak objRec Adalah Nothing Then

Dengan Sheet1. Rentang("A1")

Untuk lngField = 1 To objRec.Bidang. Hitung

. Sel(1, lngField . ).Nilai = objRec. Bidang(lngBidang - 1 . ).Nama

Berikutnya lngField

Hubungi . Offset(1, 0 . ).CopyFromRecordset(objRec)

Akhiri Dengan

Akhiri Jika

Atur objRec = Tidak ada

Akhir Sub

Ini mirip dengan , kecuali dalam hal ini kami meneruskan parameter, 'HR', ke Query. Dengan demikian hasil yang diturunkan ke sheet akan disaring untuk divisi HR saja

Bagaimana cara menjalankan kueri di Access dari Excel VBA?

Langkah Membuat VBA untuk Menjalankan Query di MS Access .

Langkah 1. Tambahkan Formulir Akses. Untuk memulai, buka MS Access, lalu tambahkan Formulir Akses. .

Langkah 2. Tempatkan Tombol. Selanjutnya, letakkan tombol di Formulir itu sendiri. .

Langkah 3. Buka Layar VBA. .

Langkah 4. Tulis VBA untuk Menjalankan Kueri. .

Langkah 5. Lihat Hasilnya

Bagaimana cara mengekstrak data dari database Access ke Excel menggunakan VBA?

Langkah 1. Buka Buku Kerja Excel dan dapatkan pembuat kode VBA (Alt + F11) dan Buka Modul Baru. Langkah 2. Salin dan Tempel kode di bawah ini di dalamnya. Langkah 3. Klik tombol Jalankan atau F5 (Anda juga dapat memasukkan tombol/bentuk di lembar excel lalu klik kanan dan tetapkan makro ini untuk dijalankan setiap saat. )

Bagaimana cara menanyakan database Access di Excel?

Pada tab Data, di grup Dapatkan Data Eksternal, klik Dari Sumber Lain, lalu klik Dari Microsoft Query . Kotak dialog Pilih Sumber Data ditampilkan. Di kotak dialog Pilih Sumber Data, klik tab Kueri. Klik dua kali kueri tersimpan yang ingin Anda buka.