ใช้ ADO ใน excel ทำการเชื่อมต่อ SQL server

พอดีตอนนี้ที่ทำงานได้เครื่องใหม่แล้ว เห็น office 2003 OEM มาลองดูว่าจะทำอะไรได้มั่ง เลยกลับมารื้อฟื้นเรื่อง macro ใหม่ ( VBA ) โดยคราวนี้ตั้งความสามารถที่จะลองใช้คือ การเอา record ใน excel ไปทำการเก็บไว้ในฐานข้อมูล ตอนนี้ที่ลองเขียนดู ( แต่ยังไม่เสร็จ ไป 80% ) โดยเชื่อมกับ SQL 2005 Express ใช้ Native sqlclient ในการเชื่อมต่อ อนาคตก็ปรับไปเรื่อย ๆ ตามใจอยาก
TODO
*ข้อมูลที่ได้จาก excel ใช้ ASP.NET เอามาประมวลผลอีกที*ทดลองเขียน AJAX กับ ASP*Import ตารางเวลาจาก LotusNote เอามา ทดลองใช้งาน
ที่เห็นนี้ เป็นตัวอย่าง Marcro procedure สำหรับทดสอบการเชื่อมกับ ดาต้าเบส SQL server 2005 โดยการใช้ sqlnative client ( Provider=SQLNCLI.1 )และมีการใช้ Error ออปเจ็กต์ เป็นตัวตรวจสอบข้อผิดพลาดที่เกิดขึ้น เพื่อใช้ในการ debug ภายหลัง จริงๆ จะไปหาเพิ่มเติมจากใน เอกสาร MSDN ของ ไมโครซอฟต์ก็ได้ หรือ หากไปดาวน์โหลดเอาชุด MDAC SDK มาก็มีเอกสารให้ ไม่ว่ากัน
รายละเอียด Connection String
Provider = อนุมานว่า ไดร์เวอร์ที่ใช้สำหรับการเชื่อมต่อ Integrated Security = รูปแบบความปลอดภัยที่ใช้ในการเชื่อมต่อ Data Source = Instance ฐานข้อมูล ([ serverName/instance ]) Initial Catalog = ชื่อดาต้าเบส

Sub test_ado_con()
Dim con As New ADODB.ConnectionDim con_err As ADODB.Error
Dim err_des As String Dim err_num As String
Set con = New ADODB.Connection
' Set Trap Error
On Error GoTo DisplayErr
' Connect use sql native client
con.ConnectionString = "Provider=SQLNCLI.1 ;Integrated Security = SSPI ;Data Source=udomsak\sqlexpress;Initial Catalog=ClassRoom "
' Open connectin
con.Open
'Trap Error use for debug:
DisplayErr:

For Each con_err In con.Errors
err_des = con_err.Descriptionerr_num = Hex(con_err.Number)
Next
If err_des = "" Then
MsgBox "Connection sucess!"
Else
MsgBox err_des & err_num
End If

ใช้ ADO ใน excel ตอนส่งข้อมูลเข้าเก็บในฐานขัอมูล

ทดลองทำการเชื่อมต่อไปยัง ฐานข้อมูล sql server express 2005 คราวนี้ลองทดลองป้อนข้อมูลเช้าไปดู
ในที่นี้สมมุติว่า เราได้ทำการ สร้างฐานข้อมูล OrderTest มีฟิลด์ทั้งหมดสามฟิลด์ คือ
eq_reserv_form_number,eq_reserv_name,eq_reserv_quantity
เอกสารการใช้งานค้นหาได้ที่ http://msdn2.microsoft.com/en-us/library/ms678262.aspx หรือจะติดตั้ง จากชุดพัฒนา MDAC ( MDAC sdk ) แต่รู้สึกว่าต้องเป็น microsoft genuine ถึงจะดาวน์โหลดได้
======= ตัวอย่างโค้ด ======
Dim con As New ADODB.ConnectionDim con_err As ADODB.ErrorDim conCmd As New ADODB.Command
Dim sqlCmd As StringDim err_des As StringDim err_num As String
Dim frmNum as stringDim csName as stringDim csOdrName as string
'กำหนดคำสั่ง sql
sqlCmd = _"INSERT INTO equipment(eq_reserv_form_number,eq_reserv_name,eq_reserv_quantity)" & _"values ('" + frmNum + "','" + csName + "'," + csOdrname + ")"
Set con = New ADODB.Connection
'ตรวจจับข้อผิดพลาด
On Error GoTo DisplayConnectErr
'เชื่อมต่อกับ SQL 2005 โดยใช้ sql native client
con.ConnectionString = "Provider=SQLNCLI.1 ;Integrated Security = SSPI ;Data Source=udomsak\sqlexpress;Initial Catalog=OrderTest"con.Open
'ตั้งค่าตัวแปร conCmdSet conCmd = New ADODB.Command
'ระบุว่า session ที่ใช้ในการเชื่อมต่อSet conCmd.ActiveConnection = con
'เตรียม sql string conCmd.CommandText = sqlCmd
'สั่ง execute conCmd.Execute

'จับข้อผิดพลาด
DisplayConnectErr:
For Each con_err In con.Errors
err_des = con_err.Descriptionerr_num = Hex(con_err.Number)
Next
If err_des = "" Then
ElseMsgBox err_des + " ERROR-NUM IS-> " & err_numEnd If