Senin, 25 April 2011

Get External Data - 03 - Koneksi melalui MS Query

(3 dari 4)

Muatan :

  • Kilasan tentang koneksi data memanfaatkan MS Query
  • Penjelasan tentang beberapa proses dari diagram alir

Kilasan

Koneksi data pada sebuah database atau file data yang dapat berlaku layaknya database seperti Excel, dapat dilakukan dengan bantuan aplikasi Microsoft Query (MS Query). MS Query adalah aplikasi yang mampu menjembatani komunikasi antara aplikasi Microsoft Office dengan database, melalui sebuah koneksi data. Query juga dapat disusun sesuai kebutuhan. Driver data provider jenis ODBC maupun OLEDB dapat digunakan oleh MS Query.



Secara garis besar, alur pembuatan koneksi melalui MS Query adalah :
Nomor pada gambar bukan nomor proses, tetapi nomor penjelasan.

Penjelasan tentang beberapa proses dari diagram alir

Berdasar nomor penjelasan pada diagram alir di atas, secara berurutan proses yang diberi nomor adalah :
  1. Jalankan fitur Get External
    • Excel 2003
      Data -> Import External Data -> New Database Query
    • Excel 2007
      Data -> Get External Data -> From Other Sources -> From Microsoft Query
      • Akan muncul dialog box seperti gambar berikut ini.

  2. Buat Data Source Name (DSN)
      Ketika tidak ada pilihan DSN yang sesuai dengan kebutuhan, maka pilihan &ltNew Data Source&gt yang dipilih untuk membuat DSN yang sesuai kebutuhan. Dialog box pembuatan DSN akan muncul. Berikut langkah-langkah pembuatan DSN baru.
      1. Memberi nama DSN baru
      2. Memilih driver data provider yang sesuai kebutuhan dari daftar yang ada
        Daftar berisi driver yang telah ter-install pada komputer tersebut.
      3. Tekan Connect
        Setting akan mengarahkan ke cara koneksi sesuai driver data provider yang dipilih pada nomor 2. Umumnya terbagi dalam 2 jenis, yaitu :
        • Database server
          Yaitu server DBMS yang mengelola banyak database. Untuk setting yang koneksi ke database server, secara umum membutuhkan setting seperti :
          • nama server
          • nama user dan passwordnya atau pilihan menggunakan trusted connection seperti pada SQL Server
          • Port
          • TCP/IP untuk koneksi menggunakan TCP/IP
          • Default Database yang dikoneksi
          Gambar berikut adalah pembuatan DSN baru dengan driver data provider untuk koneksi ke MSSQL Server sebagai contoh bahwa kebutuhan setting koneksi ke server database yang tergantung kepada tuntutan server database tersebut.
        • non Database Server Yaitu database yang tidak menggunakan server seperti MS Access. Bisa juga berupa file data yang memiliki sifat database seperti MS Excel. Umumnya data yang dibutuhkan dalam setting tahap ini adalah :
          • versi
          • jenis data yang umumnya berdasar ekstensi file seperti .dbf atau .dbt pada dBase
          • File database, dan terkadang cukup Path-nya saja, seperti untuk text driver
          Gambar berikut adalah contoh setting DSN untuk koneksi ke Paradox yang membutuhkan path dari file database tanpa spesifik menunjuk sebuah file database default.
      4. Memilih tabel default (bila perlu)
        Yaitu memilih sebuah tabel yang selalu akan menjadi tabel pertama yang dipilih ketika melakukan koneksi menggunakan DSN baru ini. Contoh setting tabel default yang bisa dikosongkan maupun diisi sesuai keperluan.
          Tanpa set tabel default
          Menetapkan tabel default dari daftar tabel yang dimiliki database yang dipilih
      5. Tekan Cancel untuk membatalkannya atau tekan OK jika telah selesai setting dan nama DSN baru akan muncul dalam daftar DSN. Gambar berikut menunjukkan nama DSN baru yang telah muncul dalam daftar DSN yang dapat dipilih untuk proses selanjutnya.

  3. Pilih Data Source Name (DSN)
      Pemilihan DSN disesuaikan dengan kebutuhan dan dilanjutkan menekan tombol OK. Gambar berikut adalah contoh pemilihan DSN untuk koneksi ke file Excel.

  4. Pilih file database
      Untuk DSN yang belum ditentukan database-nya atau yang bukan melakukan koneksi ke server data, maka akan muncul Open File Dialog untuk memilih satu file database yang akan digunakan. Pemilihan diakhiri dengan menekan tombol OK. Pembatalan dilakukan dengan menekan tombol Cancel.
      Untuk memilih file dari komputer lain yang dalam satu jaringan, maka perlu diadakan mapping terhadap folder yang akan dituju. Hal ini dapat dilakukan dengan menekan tombol Network.

  5. Memilih tabel dan fields yang akan digunakan
      Fields yang digunakan akan diletakkan pada bagian SELECT dalam query. Fields dipilih dari daftar sebelah kiri dengan double click atau dengan menekan tombol &gt. Pemilihan diakhiri dengan menekan tombol Next untuk melanjutkan proses. Gambar berikut adalah contoh pada tahap ini.

  6. Mengatur kriteria filter dan pengurutan (sort) data (bila perlu)
    • Filter data
      Jika tidak memerlukan filter data, langsung tekan tombol Next. Andaikan akan memfilter data, dapat dilakukan seperti contoh berikut.
      Kemudian menekan tombol Next.
    • Data sort
      Jika tidak memerlukan pengurutan data, langsung tekan tombol Next. Andaikan akan mengurutkan data, dapat dilakukan seperti contoh berikut.
      Kemudian menekan tombol Next.
Sampai disini, data telah diambil dan diatur melalui proses 5 dan 6. Dialog box yang muncul adalah pilihan untuk mengeksplorasi data lebih dulu dengan MS Query atau langsung mengirimkannya ke Excel. Pada tahap ini, diberi opsi untuk menyimpan query yang terbentuk sampai proses 6. Jika dipilih langsung mengirimkan data ke Excel, maka proses akan berlanjut ke proses 8.
Eksplorasi dengan MS Query memungkinkan untuk mengubah atau menyusun ulang query. Hasil yang ditampilkan pada MS Query adalah 1000 record pertama. Berikut contoh penampilan data pada MS Query.
Pengiriman data dari MS Query ke Excel melalui :
    menu File -> Return data to Microsoft Office Excel

  1. Penyusunan Query
      Penyusunan query dapat berupa pengubahan, penulisan ulang, atau penggantian query. Penyusunan dilakukan sesuai kebutuhan. Penyusunan dilaksanakan pada ruang SQL melalui penekanan tombol toolbar dengan icon SQL atau melalui menu View kemudian memilih SQL. Query sampai proses nomor 6 adalah hasil penyusunan MS Query, seperti contoh berikut.
      Jika akan mengubah query, maka dapat dilakukan langsung di ruang SQL ini. Pengubahan umumnya karena membutuhkan query yang lebih sesuai dengan kebutuhan, yang belum bisa terpenuhi sampai proses nomor 6. Gambar berikut adalah contoh penyusunan query agar lebih sesuai dengan kebutuhan.
      Tekan OK jika telah selesai mengubah query.

  2. Mengembalikan data ke Excel
      Dialog box pemilihan jenis output, lokasi peletakan hasil query, dan set properti range data akan muncul. Tahap ini telah menutup MS Query. Pilih lokasi output, pada Worksheet yang ada di cell apa atau dibuatkan worksheet baru.

  3. Pengaturan properti (bila perlu)
      Pengaturan properti koneksi dapat juga dilakukan melalui connection properties di worksheet untuk Excel 2007. Tab yang perlu diperhatikan pada pengaturan properti koneksi di tahap ini adalah pada tab Definition.
      Connection properties untuk koneksi ke database berbeda dengan connection properties untuk web query. Hal-hal yang perlu diperhatikan antara lain :
      • Bisa menyimpanan file office data connection (.odc) melalui tombol Export Connection File. Pilih lokasi penyimpanan file .odc yang umumnya diletakkan pada folder user\My Documents\My Data Sources
      • Tombol Edit query akan mengarahkan kembali ke MS Query untuk bisa melakukan pengubahan query lagi. Pengubahan query juga dapat dilakukan pada ruang Command Text.
      • Koneksi bisa diubah dengan mengedit langsung pada ruang Connection String atau melalui pemuatan dari file koneksi lain melalui tombol Browse untuk memilih file odc.
      Akhiri pengaturan properti dengan menekan tombol OK.

  4. Memilih jenis tabel output
      Pilihan output yang tersedia adalah table atau pivot table (disertai chart atau tidak) dan harus dipilih salah satu. Kemudian tekan tombol OK.

Closing :

Pada koneksi ke file Excel, worksheet, named range, atau rujukan ke range tertentu yang satu area bertindak sebagai tabel. Penulisan nama worksheet selalu disertai karakter $ dibelakangnya.

Pathfile lokasi database pada koneksi ke file yang dapat digolongkan sebagai database bersifat mengikat. Pemindahan file yang dinyatakan dalam Connection String akan merusak koneksi.

File(s) :


Unzip file excel_datasource.zip pada root directory drive E:

Coretan terkait :


Diposting oleh di
Label: , ,

38 komentar:

  1. -uhr-25 April 2011 pukul 19.18

    Mantab gan, Save As lagi pokoknya dah...


    Coba bikin buku gan, kayak bang abi gitu....

    BalasHapus
  2. Mr. Kid26 April 2011 pukul 04.38

    @-uhr- : Insya Allah.

    BalasHapus
  3. Aurino26 Juni 2011 pukul 21.42

    Blog yang bagus. Keep Posting ya Mas ...

    BalasHapus
  4. Mr. Kid27 Juni 2011 pukul 17.19

    @Aurino : Terimakasih atas supportnya.

    BalasHapus
  5. Sua Betria Dhani21 September 2012 pukul 11.52

    luar biasa..
    Salam kenal mas Mr. Kid,
    oya, klu berkenan boleh saya bertanya :
    Apakah program yg kita buat pake excel, bisa diupdate oleh komputer yang berbeda (menggunakan LAN)??
    Terima kasih mas atas jawabannya!

    BalasHapus
  6. Mr. Kid21 September 2012 pukul 12.57

    @Pak Sua BetriaDhani :
    Salam kenal juga Pak. Cukup panggil Kid saja supaya lebih enak dirasa.

    Pada dasarnya adalah bisa. Cara yang sesuai dengan kondisi dilapanganlah yang membedakannya.

    Excel memiliki fitur Shared Workbook (walau sebaiknya mulai dikurangi penggunaannya). Melalui sebuah prosedur VBA (biasa lebih dikenal Macro), juga bisa dilakukan proses pengubahan file Excel lain.
    Penggunaan shared workbook pada aplikasi ber-VBA terkadang memiliki masalah. Aplikasi ber-VBA meng-'update' file Excel lain umumnya melalui proses open book - write - close book. Selain itu, proses update menggunakan query juga bisa dilakukan. Syarat utama penggunaan query adalah terstrukturnya data, sehingga benar-benar berkaidah database.
    Pada aplikasi yang dibangun dengan multi tier (ada file Excel yang isinya hanyalah data, ada file Excel yang tugasnya mengolah data, dan ada file excel lain lagi yang menjadi interface [baik sisi Input maupun Output] ke-user), bisa jadi penggunaan VBA yang proses transaksi datanya memanfaatkan query akan lebih mudah (tergantung struktur datanya).

    BalasHapus
    Balasan
    1. Sua Betria Dhani11 September 2014 pukul 22.52

      Mas kid, tanya lagi..
      Apakah bisa excel (vba) sebagai pengolah data, dimana ms. Access tempat penyimpanan databasenya, lalu bisa diakses pada LAN..???
      Jadi disetiap komputer client tersedia aplikasi-vba excel yg mengolah database access yg berada dikomputer server.
      Mudah2n pertanyaannya bisa dimengerti ya mas
      Oya, gmn supaya jago spt mas kid? Hehe

      Hapus
    2. Mr. Kid11 September 2014 pukul 23.31

      hehe...

      Bisa Pak.
      Tugas fitur Get External Data adalah berkomunikasi data dengan berbagai sumber data yang berupa database.
      Coba disempatkan lihat-lihat coretan bagian 04 tentang Get External Data.
      Formula dan VBA bisa digunakan untuk mengolah lebih lanjut hasil koneksi data tersebut, sehingga Excel bisa seperti aplikasi pengolah data yang terintegrasi dengan database.
      VBA juga bisa digunakan untuk mengubah-ubah query text ataupun connection string yang digunakan untuk mengambil data ke sumber data (database), sehingga seakan-akan Excel dengan VBA-nya bisa seperti aplikasi untuk mengeksplorasi database.

      ;)
      rejeki kali pak. soale bisanya cuma minta (doa) terus usaha lewat belajar sebisanya dan berusaha berbagi apa yang udah dipahami.
      supaya ndak rugi, jadi jauh lebih baik dari saya saja Pak.
      Soale lebih banyak yang jauh lebih bisa dari saya wakaka...
      Katanya mereka yang lebih bisa, banyak belajar bersungguh-sungguh dan dipakai dalam pekerjaan.

      Sayangnya, saya ndak cocok dengan cara mereka... jadi ya dinikmati dengan syukur saja yang ada pada saya saat ini.
      :)

      Hapus
    3. Sua Betria Dhani13 September 2014 pukul 11.34

      Mantap...

      Untuk yang LAN bisa, yang penting tau dulu bisa... Gmn caranya tinggal cari dan dipelajari... Mudah2n mas kid bisa ada waktu nulis tentang bagaimananya...hehe

      Nanya lagi ya mas,
      Apakah bisa, mengolah database dengan jaringan internet, jadi bisa online gitu?

      Makasih ilmunya mas kid

      Hapus
    4. Mr. Kid16 September 2014 pukul 10.09

      Bisa Pak.
      Semua data connection tergantung kaidah penyusunan Connection String dari Data Provider yang digunakan. Silakan jelajah internet tentang 'Connection String'. Nanti akan ada banyak cara mendefinisikan koneksi. Dari file lokal sampai file yang ada di tempat nun jauh diujung dunia sana.

      ;)

      Hapus
    5. Sua Betria Dhani18 September 2014 pukul 10.00

      Hehe...Makasih banyak mas kid, atas sharing ilmunya.
      Sy lagi belajar vba excel, dan belum bisa bahasa pemerograman yang lain.
      Oya selain blog ini, mas kid punya web yg lain..
      Biar lebih kenal dengan mas kid... Mau berguru jarak jauh... :D

      Hapus
    6. Mr. Kid18 September 2014 pukul 15.16

      :)

      ndak punya Pak.
      Kalau beberapa coretan saya tentang VBA, bisa dilihat diblog milis Belajar-Excel di :
      http://b-excel.blogspot.com

      Hapus
  7. heka23 September 2012 pukul 18.26

    manteb Mr Kid.....

    BalasHapus
  8. Mr. Kid23 September 2012 pukul 18.48

    @heka :
    Makasih om...
    terus dikembangkan ya om...
    coba coretan terkaitnya.
    Bila masih kurang jelas, kirim request coretan ya. Insya Allah kumpulan coretannya bisa lebih banyak dan sesuai kebutuhan.

    BalasHapus
  9. indahnya berbagi20 November 2015 pukul 11.41

    Terima kasih banyak Mr. Kid ilmunya... bagus sekali
    Saya ada pertanyaan lagi yaitu saya menggunakan langkah 7 diatas dengan memasukkan query yang telah jadi, tapi didalamnya ada procedurenya,
    ketika di OK tidak bisa karena muncul warning "SQL Query cant be represented graphically, continue anyway ?
    Could not add the table 'XXXX'", bagaimana supaya procedure tsb berjalan.
    saya menggunakan koneksi ODBC

    BalasHapus
    Balasan
    1. Mr. Kid20 November 2015 pukul 13.33

      Permasalahan bisa lebih jelas andai bisa di-share bunyi query nya.

      btw,
      dengan asumsi adalah melakukan koneksi ke sebuah worksheet di sebuah file Excel,
      coba langkah berikut :
      1. Pastikan file yang dikoneksi berada di folder yang tepat dengan nama lengkap yang benar.
      misal, file data.xlsx di drive D folder myData benar ada disana.
      2. Pastikan sheet yang dituju ada di dalam file tersebut.
      misal, sheet bernama Data Bulan November benar ada di file data.xlsx
      3. Pastikan header tabel di sheet yang dituju ada di baris 1, tidak ada yang di-merge, dan tidak ada cell kosong di antara 2 kolom yang berisi data.
      4. Gunakan kurung siku yang mengapit nama-nama kolom, worksheet, dan path file. Akhiri nama sheet dengan karakter $
      misal, file data.xlsx di drive D folder myData ditulis :
      [D:\myData\data.xlsx]
      worksheet Data Bulan November ditulis :
      [Data Bulan November$]
      sehingga susunannya di bagian FROM menjadi :
      FROM [D:\myData\data.xlsx].[Data Bulan November$] AS dt
      *** perhatikan karakter titik diantara siku nama file dan siku nama sheet
      *** dt adalah nama panggilan (disebut alias) dari si tabel worksheet di data.xlsx yang disebut dalam FROM tersebut.

      5. Penyebutan nama-nama kolom di bagian SELECT didahului nama aliasnya si tabel sumber, kemudian nama kolom ditulis diapit kurung siku juga.
      misal, mengambil data kolom bernama TANGGAL INVOICE dan QTY ditulis :
      SELECT dt.[TANGGAL INVOICE] , dt.[QTY]
      *** daftar kolom yang disebut atau yang dibentuk dipisahkan dengan karakter koma
      Untuk permisalan dalam nomor 1 sampai 5, jika ditulis utuh akan membentuk query berbunyi :
      SELECT dt.[TANGGAL INVOICE] , dt.[QTY]
      FROM [D:\myData\data.xlsx].[Data Bulan November$] AS dt

      Selamat mencoba penerapan beberapa kaidah penulisan object di dalam query yang telah disebut di atas.
      Semoga sukses.

      Regards,
      Kid


      Hapus
  10. indahnya berbagi20 November 2015 pukul 16.11

    Terima kasih Mr. Kid..
    Untuk data yang saya gunakan adalah .fdb (firebird database), koneksi menggunakan odbc driver firebirds.
    Test koneksi sukses dan bisa menampilkan data dengan query standard, tetapi ketika didalam query memanggil procedure yang yang ada didalam database tidak bisa di execute karena error diatas...
    Query yang digunakan
    "select (select Account_indent from GLAccount_indented(L.GLAccount)) AccountIndent,
    L.*, B.Balance, G.AccountName, G.IndentLevel from FS_MAIN(1) L
    left outer join GET_FS_BALANCE(L.GLACCOUNT, Null, '11/20/2015', L.STMTLINID, 0 ) B on B.STMTLINID=L.STMTLINID
    left outer join GLACCNT G on G.GLACCOUNT=L.GLACCOUNT
    where
    (NType in (0,1,2,3) and (B.Balance<>0) or L.GLAccount is null) order by L.Seq, G.FirstParentAccount, 1, L.GLAccount"

    yang termasuk procedure adalah : GLAccount_indented, FS_MAIN dan GET_FS_BALANCE

    Muncul error :
    1. 'SQL Query cant be represented graphically, continue anyway ?' => Ok
    2. 'Could not add the table 'GLAccount_indented9L.GLAccount))'

    Semua procedure tersebut ada didalam database yang sudah dikoneksikan, di excel sendiri ketika tarik data via odbc -> query ada fitur procedure tapi tidak tau bagaimana fungsinya, maklum masih pemula....

    Terima kasih,

    BalasHapus
    Balasan
    1. Mr. Kid20 November 2015 pukul 16.30

      Apakah query yang Anda buat bisa dijalankan pada query editor aplikasi interface yang dapat mengkoneksi ke database yang Anda gunakan (misal di query editor aplikasi firebird maestro dan sebagainya) ?

      Hapus
    2. indahnya berbagi23 November 2015 pukul 16.17

      Saya jalankan di firebird maestro bisa Mr. Kid,
      Saya coba hari ini test trial install firebird maestro - koneksi database - execute query dan berhasil.

      Apakah Mr. Kid punya tutorial penggunaan procedure pada penerapan odbc di excel ?
      Mungkin saya ada yang salah dalam settingnya

      Terima kasih,

      Hapus
    3. Mr. Kid24 November 2015 pukul 06.19

      Mungkin bisa begini :
      query tersebut disimpan sebagai sebuah Views, misal diberi nama fdbViews
      kemudian query yang digunakan oleh koneksi dengan ODBC dari Excel-nya adalah Select * from fdbViews

      Bila hal tersebut tidak berhasil, coba ubah query awal agar tidak menggunakan subquery pada bagian Select. Jadi subquery yang di-select dipindah menjadi proses join setelah bagian from.



      Hapus
  11. Unknown9 Maret 2016 pukul 18.40

    Mr. Kid
    Kebetulan saya sangat tertarik dengan materi Get External Data - 03 - Koneksi melalui MS Query, terutama point ke 3 yaitu tentang koneksi DSN ke File Excel, saya coba-coba koneksi ke salah satu file data (excel 2007), dan sudah berhasil saya koneksikan ke file excel yang baru dan data saya sudah bisa muncul disitu, tetapi yang membuat saya bingung adalah, apakah data hasil koneksi tersebut saya bisa tambah, edit, maupun delete ? jika ya mohon penjelasan caranya

    Terima kasih
    Salam Kenal
    Yayan

    BalasHapus
    Balasan
    1. Mr. Kid10 Maret 2016 pukul 09.43

      Hai Yayan,

      Fitur Get External Data pada dasarnya untuk kegiatan Read Data. Pengubahan bunyi query di dalam Excel Connection object hasil Get External Data dengan query Insert Into memang bisa dilakukan dan membuat fitur Get External Data bisa menambah record sumbernya. Tapi hal ini tidak efisien meskipun telah diotomasi.

      Jadi, kegiatan :
      a. Read Data :
      - bisa menggunakan Get External Data (bisa diotomasi dengan VBA)
      - bisa juga mengguanakan ADO (harus dengan VBA)

      b. Write Data :
      - menggunakan ADO (harus dengan VBA)

      Hal yang perlu diperhatikan dalam koneksi ke file Excel :
      1. Read data dilakukan memanfaatkan Get External Data dan akan bersifat read only
      2. Write data dilakukan dengan ADO untuk query INSERT INTO (tambah record) dan UPDATE (ubah nilai) saja. Jadi, tidak dimungkinkan untuk menjalankan query DELETE.

      Contoh file ada disini :
      https://app.box.com/s/4d4m3201fielq1ew5c9p0vtmg5u5ysd1

      Hapus
    2. Unknown10 Maret 2016 pukul 10.33

      wah ternyata tidak se-simpel yang saya bayangkan :D, terima kasih Mr. Kid atas penjelasan dan sampel filenya, saya harus belajar lebih banyak lagi nih

      Hapus
  12. Unknown4 November 2016 pukul 15.08

    bisa tidak dibuatkan tombol agar 1 tombol sj yg ditekan trus bisa tarik data tersebut?

    BalasHapus
    Balasan
    1. Mr. Kid5 November 2016 pukul 04.48

      Hai Harita,

      Proses pembuatan koneksi data hanya dilakukan sekali. Setelah itu cukup dilakukan proses refresh untuk memperbarui data melalui tombol Refresh All yang telah disediakan oleh Excel di ribbon Data.

      Misalkan ada sebuah file Excel bernama myData.xlsx memiliki sheet bernama 'Sales' yang terus bertambah record datanya setiap waktu. Kemudian di file lain (anggap saja file summary penjualan beernama summary.xlsx) dibuat koneksi data ke file myData.xlsx sheet 'Sales' dan di-save. File summary.xlsx dibagikan kepada seluruh user yang membutuhkan data penjualan yang ada di file myData.xlsx sheet 'Sales'. Setiap hari atau setiap kali user ingin memperbarui data yang tampak di file summary.xlsx, maka user cukup melakukan refresh all dengan cara menekan tombol Refresh All di ribbon Data.

      Menurut saya, dengan disediakannya tombol Refresh All oleh Excel tersebut, tidak diperlukan lagi tombol khusus buatan user untuk melakukan refresh (menarik data).
      Proses pembuatan koneksi data ke suatu sumber data hanya dibutuhkan 1 (satu) kali saja dan sangat spesifik akan merujuk ke file apa sheet mana dan kolom apa saja yang diperlukan, sehingga tidak perlu dibuat berulang-ulang oleh setiap user. Bisa jadi cukup 1 (satu) orang yang membuatkan dan di-share kepada user lainnya.

      Regards,
      Kid

      Hapus
  13. Unknown6 November 2016 pukul 23.53

    Dear Mr.Kid,

    Kalau databasenya pakai mysql bisa gak yah ?

    BalasHapus
    Balasan
    1. Mr. Kid7 November 2016 pukul 05.16

      Hai Ujang,

      Coba coretan yang ada disini :
      http://excel-mr-kid.blogspot.com/2011/06/koneksi-data-ke-mysql.html

      Hapus
    2. Unknown8 November 2016 pukul 23.24

      terima kasih, setelah saya coba akhirnya sukses konek ke server.
      oya ada cara tidak buat protect string connection gak ya ???

      Hapus
    3. Mr. Kid11 November 2016 pukul 03.49

      Hai Ujang,

      Jika yang dimaksud adalah aman sekali, maka tidak bisa dilakukan oleh Excel, kecuali data provider yang digunakan memiliki opsi untuk menampilkan dialog login database. Tapi, hal ini bisa mengganggu proses refresh, karena akan selalu muncul dan user harus melakukan login.

      Ada yang menggunakan nama object connections yang sangat panjang lalu worksheet diproteksi, tetapi hal ini tetap bisa dibaca VBA dengan mudah.

      Opsi lainnya adalah membuat data connection melalui VBA. Begitu data sudah selesai di-load (selesai refresh data), maka connection dibuang (di-delete). Hal ini tentu ribet dan pasti ada saja jalan untuk membingkar VBA ter-password, sehingga bisa melihat bunyi password dalam script VBA yang ada.

      Nah, yang wajar dan umum dipakai, ketika mengkoneksi suatu database seperti Oracle, MS SQL Server, MySQL dan semacamnya, maka yang dilakukan adalah mendefinisikan hak akses user di dalam database tersebut. Jadi, tidak dilakukan oleh Excel, tetapi dilakukan oleh admin database di database engine-nya. Setiap database engine memiliki beragam security, jadi kemudahan, kenyamanan, dan keamanan koneksi data bisa diatur melalui database engine dan database-nya.

      Regards,
      Kid

      Hapus
    4. Unknown12 November 2016 pukul 15.55

      Terima kasih atas pencerahannya, untuk amannya saat ini terpaksa saya harus masukkan pass tiap pertama kali refresh soalnya saya ambil data dari server live hehehe.
      oya untuk query biasa saya kalau di MySQL menggunakan "as" contoh sum(jumlah) as jlhhk
      nah kalau disini pakai apa ya ? soalnya kalau pakai as atau alias tidak berhasil, tks

      Hapus
    5. Mr. Kid12 November 2016 pukul 16.38

      Tetap menggunakan AS atau spasi karena query yang digunakan adalah standar query ANSI 92.
      Jika tidak bisa menggunakan AS atau spasi, coba dicek ulang query-nya. Kadangkala pemberian nama aliasnya dibuat berbeda dengan nama field yang digunakan bisa berhasil.

      Untuk nama alias yang menggunakan spasi atau karakter selain A-Z atau 0-9 dibutuhkan pengapitan kurung siku sesuai standar query ANSI 92 dan umunya query.

      Contoh :
      Select a.IDKARY AS NIP, b.[NAMA PEGAWAI] AS KARYAWAN, sum(a.JUMLAH) AS [TOTAL PENDAPATAN]
      From tblGAJI AS a
      Inner Join tblKARY as b ON a.IDKARY=b.IDKARY
      Group By a.IDKARY,b.[NAMA PEGAWAI]

      Hapus
    6. Unknown14 November 2016 pukul 21.58

      ternyata setelah saya coba tidak mau menggunakan AS di excel saya, maunya cuma pake spasi hehhe.
      ok mr terima kasih atas ilmunya.

      Hapus
    7. Unknown14 November 2016 pukul 22.03

      Ralat, ternyata AS harus pake huruf besar.

      Hapus
    8. Unknown14 November 2016 pukul 22.12

      Oya satu lagi mr. kalau seandainya criteria (where) nya di letakkan di cell excel bisa gak ya ?
      contoh : select namakaryawan AS nama, karyawanid AS nik from datakaryawan where lokasi tugas='[ini ada di excel contoh A2]' and tipekaryawan='[ini ada di excel contoh A1]'

      Hapus
    9. Mr. Kid14 November 2016 pukul 22.14

      Hai Ujang,

      Connection String dan Command Text (query) tidak dapat merujuk ke cells Excel.
      VBA bisa digunakan untuk mengubah Connection String maupun Command Text (query).

      Regards,
      Kid

      Hapus
    10. Unknown14 November 2016 pukul 22.24

      Waduh kalau pake VBA kayaknya belum bisa saya, ok lah kita tinggal sejenak.
      ada lagi pertanyaan, saya mempunyai lebih dari 1 table di dalam 1 file excel, bagaimana caranya membuat pivot table yg sumbernya lebih dari 1 table.
      Note excel saya versi 2016
      terima kasih.

      Hapus
    11. Unknown14 November 2016 pukul 22.34

      oya pertanyaan lagi, barusan saya edit sebuah table di database mysql, sebagai berikut :
      strukture = tipe varchar(20)
      semula berisi = DT-62-KT8755RD
      kemudian saya ubah = DT-MB10
      saya tekan enter kenapa error ya??? ada apa dengan kata MB10 ??
      soalnya kalau saya buat menjadi DT-MB-10 tidak masalah.
      terima kasih.

      Hapus
    12. Mr. Kid19 November 2016 pukul 14.47

      Hai Ujang,

      imo, tidak ada apa-apanya pada data berbunyi MB10.
      Coba cek lagi batasan table dalam database tersebut. Mungkin ada constraint tertentu.

      Regards,
      Kid


      Hapus

Langganan: Posting Komentar (Atom)