การเรียกใช้งานฐานข้อมูล SQLite ผ่าน ODBC บน Excel

เมื่อวานเกิดนึกสนุก นั่งรื้อตัวเชื่อมต่อฐานข้อมูลบน VBA ที่เขียนไว้นานแล้วใหม่ ของเดิมใช้ต่อกับ Access Database อย่างเดียว ก็เลยอยากทดลองต่อกับฐานข้อมูลอื่นดูบ้าง เลยลองจับเอา SQLite มาลองดู เนื่องจากเป็นฐานข้อมูลที่เรียบง่าย และเหมาะกับการใช้บันทึกข้อมูลทั่วไปที่ใช้งานทีละคน (หรือไม่กี่คนพร้อมกัน)

ดาวน์โหลด ODBC Driver

การเชื่อมต่อที่จะใช้ทดสอบในครั้งนี้ จะใช้ ODBC ซึ่งเป็นวิธีการเชื่อมต่อที่ Excel และ VBA รองรับอยู่แล้ว แค่หาตัวโปรแกรม driver ที่จะใช้เป็นตัวกลางคุยกันเท่านั้น เท่าที่หาได้ในอินเทอร์เน็ตจะมี 2 แบบคือ Open source ใช้งานฟรี แต่เสี่ยงมี bugs บ้าง กับตัวเสียเงิน

แน่นอนเนื่องจากเป็นการทดสอบ เลยเลือกตัวฟรีมาใช้ดู โดยการไปดาวน์โหลดที่เว็บไซต์ http://www.ch-werner.de/sqliteodbc/ ถ้าใครใช้ Windows รุ่น 64-bit และตัว Excel เป็น 64-bit ด้วย ให้ลงรุ่น 64-bit (x64) ผู้เขียนใช้ 32-bit เลยลงรุ่น x86 ธรรมดา

จากนั้นให้ทำการติดตั้งลงในเครื่องให้เรียบร้อย

สร้างข้อมูลสำหรับทดสอบ

ขั้นต่อไป ให้ลองสร้างฐานข้อมูลสำหรับทดสอบ ผู้เขียนใช้โปรแกรม SQLite Database Browser สร้างตัวอย่างสำหรับข้อมูล ชื่อ R:\demo.db3 และตารางชื่อ demo ดังนี้

หากใครไม่สะดวกสร้างข้อมูลทดสอบเอง สามารถดาวน์โหลดไฟล์ demo.db3 ได้ที่นี่

นำเข้าข้อมูลด้วย Microsoft Query

เมื่อมีข้อมูลสำหรับทดสอบเรียบร้อยแล้ว ขั้นตอนต่อไป ให้สร้างไฟล์ Excel ใหม่ขึ้นมา จากนั้นให้คลิกที่แท็บ Data → From Other Sources → From Microsoft Query เพื่อเปิดหน้าต่างสำหรับเชื่อมต่อฐานข้อมูล และเลือก Driver สำหรับเชื่อมต่อตามภาพด้านล่าง

ในบางกรณี จะเกิดปัญหา ตัวเชื่อมต่อมองไม่เห็นตารางในฐานข้อมูล ให้ลองคลิกที่ System Tables โดยลองคลิกเลือก หรือไม่คลิกเลือก ทำสลับกันไปมา แล้วกด OK จนมีตารางขึ้นมาให้เลือก

เมื่อเห็นตาราง demo แล้ว ให้คลิกที่ปุ่ม > เพื่อนำค่าฟิลด์ (Field) ในตารางไปแสดง (ในที่นี้คือ id fullname และ age)

จากขั้นตอนที่กล่าวมา ตอนนี้โปรแกรม Excel รู้แหล่งข้อมูลที่จะให้นำเข้าแล้ว สิ่งสุดท้ายที่จะทำคือ กำหนดตำแหน่งที่จะให้แสดงข้อมูลจากตาราง ให้เลือกเซลล์ที่ต้องการ และคลิก OK

การนำเข้าข้อมูลล่าสุดเมื่อข้อมูลเปลี่ยนแปลง

สิ่งที่น่าสนใจอีกอย่างสำหรับการนำเข้าข้อมูลด้วยวิธีการข้างต้นคือ เมื่อตารางในฐานข้อมูลมีการเปลี่ยนแปลง ผู้ใช้งานสามารถ Refresh เพื่อสั่งให้ Excel ทำการดึงข้อมูลล่าสุดมาแสดงได้ ในตัวอย่างจะลองเพิ่มข้อมูลเข้าไปอีก 1 แถว ดังนี้

วิธีการ Refresh ข้อมูล สามารถทำได้โดย ให้คลิกเลือก table ที่ใช้แสดงข้อมูลจาก demo.db3 จากนั้นให้เลือกแท็บ Data → Refresh All → Refresh เพื่อดึงข้อมูลล่าสุดมาแสดง

Microsoft Query

สำหรับเครื่องมือ Microsoft Query ที่แถมมากับ Excel จะคล้าย ๆ กับโปรแกรม Access ที่สามารถช่วยจัดการตารางในฐานข้อมูล เช่น filter sort เรียกว่าเป็น Access ตัวเล็ก ซึ่งใครเคยใช้โปรแกรม Access มาแล้ว จะสามารถใช้ Microsoft Query ได้อย่างคล่องแคล่วเลย เพราะใช้หลักการทำงานแบบเดียวกัน ซึ่งผู้เขียนไม่ได้ลงรายละเอียดในส่วนนี้ หากผู้อ่านสนในสามารถศึกษาหาอ่านในอินเทอร์เน็ตเพิ่มเติมได้

การนำเข้าข้อมูลด้วย VBA ผ่าน ADODB Object

ในส่วนของ VBA สามารถเชื่อมต่อกับ ODBC ได้เหมือนกันโดยผ่าน ADODB วิธีการไม่ยาก สามารถหาได้จากอินเทอร์เน็ต ทางผู้เขียนเลยลองดูพบว่า ไม่ค่อยเสถียรเท่าไหร่ ยังมี Error อยู่บ้างในบางครั้ง ตัวอย่างโปรแกรมที่ใช้เชื่อมต่อผ่าน ODBC ด้านล่าง จะเป็นการนำเอาข้อมูลตาราง demo ทั้งหมด ไปแสดงในเซลล์ A1 ผ่านคำสั่ง CopyFromRecordset

Sub SQLiteDemo()
    Dim dbConnect As New ADODB.Connection
    Dim rsRecords As New ADODB.Recordset

    dbConnect.Open "DRIVER=SQLite3 ODBC Driver;Database=R:\demo.db3;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"
    rsRecords.Open "Select * from demo", dbConnect

    Range("A1").Select
    ActiveCell.CopyFromRecordset Data:=rsRecords

    rsRecords.Close
    dbConnect.Close
End Sub	

ที่มาของการเชื่อมต่อให้ดูได้จากชื่อใน ODBC Data source หากระบุไม่ตรง หรือชื่อผิดจะทำให้โปรแกรม Error

เมื่อ Run โปรแกรมพบปัญหา ข้อความที่เป็นชื่อไม่มา บางครั้งเกิดปัญหา multiple-step ole db operation generated errors ซึ่งทางผู้เขียนลองพยายามหาสาเหตุ และวิธีการแก้ไขแล้ว แต่ไม่เจอ เลยลองเขียนอีกแบบโดยให้เขียนข้อมูลผ่านการอ่านค่าฟิลด์ของ RecordSet โดยตรง พบว่า ได้บ้าง ไม่ได้บ้าง

Sub SQLiteDemo2()
    Dim dbConnect As New ADODB.Connection
    Dim rsRecords As New ADODB.Recordset
    Dim rCell As Range

    dbConnect.Open "DRIVER=SQLite3 ODBC Driver;Database=R:\demo.db3;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;"
    rsRecords.Open "Select * from demo", dbConnect

    Set rCell = ActiveCell 	'กำหนดจุดที่จะแสดงข้อมูลคือตำแหน่งที่เลือกปัจจุบัน
    Do Until rsRecords.EOF		'วนลูปจนข้อมูลแถวสุดท้าย
      rCell.Cells(1, 1).Value = rsRecords.Fields(0).Value
      rCell.Cells(1, 2).Value = rsRecords.Fields(1).Value
      rCell.Cells(1, 3).Value = rsRecords.Fields(2).Value
      rsRecords.MoveNext		'เลื่อนตำแหน่งไปแถวถัดไป
      Set rCell = rCell.Cells(2, 1) 	'กำหนดให้แสดงในแถวถัดไป
    Loop

    rsRecords.Close
    dbConnect.Close
End Sub

ส่งท้าย

ในการใช้งาน ADODB อย่าลืมเลือก Microsoft ActiveX Data Objects 2.x Library (x คือเลข version) ใน References หากไม่ได้เลือกจะ Compile ไม่ผ่านและเกิด Error (ใน Microsoft Visual Basic for Applications Editor ให้เลือกเมนู Tools → References...) ซึ่งจากผลการทดลอง VBA ที่กล่าวมาข้างต้น จะเห็นว่าไม่เหมาะกับงาน production มากเพราะยังมีปัญหาอยู่ ควรใช้วิธีเชื่อมต่อแบบอื่น ในการใช้งานจริงผู้เขียนคิดว่าใช้งานผ่าน Microsoft Query น่าจะดีกว่า

ปัญหาที่ผู้เขียนเจอกับ SQLite อีกอย่างคือ หากโฟลเดอร์ที่เก็บไฟล์ฐานข้อมูล มีชื่อเป็นภาษาอื่นนอกเหนือจากภาษาอังกฤษ จะเกิดปัญหา ODBC ไม่สามารถเปิดฐานข้อมูลได้ ดังนั้นโปรดระวังเรื่องนี้ด้วย

สำหรับการเชื่อมต่อแบบอื่นมีอีกไหม ตอบได้เลยว่ามี แต่ยังไม่มีเวลาทดสอบเนื่องจากในงานจริง ๆ ยังไม่มี project ที่ใช้งาน SQLite ครับ หากใครมีวิธีอื่น ๆ สามารถบอกใน comment เพื่อเป็นประโยชน์กับผู้หลงเข้ามาอ่านคนอื่น ๆ ได้ครับ