การสร้างตารางด้วย Access VBA

วันก่อน สร้าง Query เพื่อปรับข้อมูลในตาราง โดยสร้าง Query ให้หาผู้เรียนปัจจุบันที่เรียนจบ และนำข้อมูลไปปรับในตารางผู้เรียน ปรากฏว่า ไม่สำเร็จ เพราะมี Error ว่า Operation must use updateable query

ลองหาข้อมูลดู ก็พบว่า Update Query ก็มีข้อจำกัดอยู่เหมือนกัน ใช้ไม่ได้ทุกเรื่อง เช่น ฟิลด์ที่มีการคำนวณ หรือที่มาจาก Cross Tab ไม่สามารถใช้ Update Query ได้ เพราะมีการคำนวณเหมือนกัน  บังเอิญ Query ของผมเอามาจาก Cross Tab จึงทำให้มีปัญหา ทางออกของผมจึงนำเอาผลที่ได้ไปไว้ในตารางที่สร้างด้วยโค้ด VBA เป็นการสร้างตาราง On the fly ความจริงใช้ Query สร้างตารางแล้วเรียกใช้งานก็ได้ แต่ผมว่า ใช้โค้ด VBA สร้างตารางดีกว่า มีคนบอกว่า น่าจะใช้โค้ด VBA มากกว่าการใช้ Query เพราะ Query ปรับ เปลี่ยน ลบ ได้ ทำให้มีโอกาสการเปลี่ยนแปลงจากต้นฉบับเดิมได้สูง

 

ผมสร้างตารางชั่วคราว และเอาข้อมูลในตารางนี้ไปปรับข้อมูลในอีกตารางหนึ่ง ไม่มีปัญหา ปรับได้เรียบร้อยดี

 

โค้ด VBA สำหรับการสร้างตาราง มีดังนี้

 

Private Sub TestTemp()

   On Error GoTo ErrorHandler
   Dim strSQL as String
   Dim strTable as String
   strTable = "tblTempTest"
   'ลบตารางเก่าออกก่อน ถ้ามี เพื่อให้ได้ตารางใหม่ที่บริสุทธิ์
   DoCmd.DeleteObject  acTable, strTable

   'สร้างตารางใหม่ด้วยคำสั่ง select into

   strSQL = "Select fullName INTO " & strTable & " FROM " &_

  "qryApprove_Final "

   'คำสั่งให้ดำเนินการตามที่กำหนด

   Currentdb.Execute strSQL

   'คำสั่งสำหรับการปรับข้อมูลในอีกตารางหนึ่ง

   strSQL = "UPDATE tblTempNames INNER JOIN " &_

   "nameFromAppForm ON tblTempNames.fullname = " &_

   "nameFromAppForm.fullname SET " &_

    "nameFromAppForm.approved = Date();"

    CurrentDb.Execute strSQL
    MsgBox "ปรับข้อมูลเรียบร้อบแล้ว " & Date, vbOKOnly, "Social Media"

Exit Sub
ErrorHandler:
IF Err.Number = 7874 Then
Resume Next
End If
End Sub

 

ถ้าต้องการเปลี่ยนแปลงโครงสร้างตารางที่สร้างขึ้น เช่น เปลี่ยน Primary Key เปลี่ยนชนิดข้อมูล สร้างฟิลด์ใหม่ ให้ใช้คำสั่ง ALTER เช่น

 

strSQL = "ALTER TABLE " & strTable & " ADD CONSTRAINT KeyName PRIMARY KEY(ID)"

strSQL = "ALTER " & strTable & " ADD COLUMN TotalDollars Currency"

 

strSQL = "ALTER " & strTable & " ALTER COLUMN TotalDollars Currency"

 

ถ้าต้องการสร้าง Query สำหรับ Update ข้อมูลในตารางหนึ่ง โดยใช้ข้อมูลจากอีกตารางหนึ่ง ทำดังนี้

 

1. สร้าง Select Query และนำเข้าตารางทั้งสอง
2. สร้างความสัมพันธ์ระหว่างสองตาราง
3. เปลี่ยน Select Query เป็น Update Query โดยไปที่แถบออกแบบ ชนิดแบบสอบถาม เลือก Update
4. กำหนดฟิลด์ที่จะเปลี่ยนแปลง ดังภาพ

   
5. คลิกปุ่มเรียกใช้ Query นี้จะทำงาน ข้อมูลจะปรับตามที่กำหนด

 

อ้างอิง

http://accessexperts.com/blog/2011/07/20/use-temp-tables-in-your-code-for-quick-and-easy-analysis/