RECORD ใน ACCESS
วัตถุประสงค์
การเปิด Record ต่างๆ ของ TABLE / QUERY เพื่อจัดการข้อมูลภายในแบบทีละรายการ
เนื้อหา
บทนี้ผมจะพาท่านไปลองสัมผัสกับข้อมูลในแต่ละ Record โดยตรง มีงานด้าน database หลายๆ อย่างที่เราต้องเข้าไป สัมผัสข้อมูลด้วยโปรแกรม ทีละ Record ทีละ Field ด้วยโปรแกรมของเราเอง เช่นการอ่านข้อมูลรายการขาย มาตรวจสอบรหัสสินค้าว่าถูกต้อง ขาดเหลือหรือไม่ รายการไหนไม่สมบูรณ์ ให้พิมพ์ออกมาเป็นต้น การทำงานกับ RecordSet มีวิธีการยุ่งยากทีเดียวครับ ลองมาดูวิธีใช้งานดูครับ
เปิด RecordSet ไปทำไม
ก็เพื่อเข้าไปอ่านข้อมูลใน Table มาทีละรายการ เพื่อทำการตรวจสอบ สมมุติว่าเราอยากรู้ Supplier รายใดบ้างที่ขายสินค้าให้เราต่ำกว่า 2 รายการ ปกติ เราต้องนำ Table Supplier ไป Join กับ Product แล้วหา Count ที่น้อยกว่า 2 ต้องรำหลายกระบวนเพลงเลยละ แต่คราวนี้เราจะใช้ VBA แทนครับ
1 | Sub RecordSetScan() |
2 | Dim rst As Recordset |
3 | Dim x |
4 | Set rst = DBEngine(0)(0).OpenRecordset("Suppliers”) |
5 | |
6 | Do Until rst.EOF |
7 | x = DCount("*", "PRODUCTS", "SUPPLIERID = " & rst("SUPPLIERID")) |
8 | If x < 2 Then MsgBox rst("SUPPLIERID") & ":" & rst("COMPANYNAME") |
9 | rst.MoveNext |
10 | |
11 | Loop |
12 | Set rst = Nothing |
13 | |
14 | End Sub |
การทำงาน
- บรรทัดที่ 2 เราสร้างตัวแปรที่เป็น Recordset
- บรรทัดที่ 4 เป็นการเปิด Recordset ครับ มีหลายวิธีมาก แต่จำ Pattern นี้เลยก็ได้ครับ ให้สนใจตรงที่ “Suppliers” ค่าที่ใส่เป้นการระบุ SQL ที่จะไปหยิบตารางข้อมูลมาเป็น Recordset ให้เรา ตย นี้เป็นการใส่ชื่อ Table ตรงๆ เราจะใช้ ชื่อ Query ที่เราทำไว้ก็ได้ หรือ จะใส่เป็น SQL เลยก็ได้ เช่น “Select * from Suppliers” เป็นต้น อันนี้จะสะดวกในการ สร้าง SQL ได้ดั่งใจ หรือ เราจะ Where อะไรที่ซับซ้อนกว่าเป็น table หรือ query ธรรมดาได้ เช่น
ใช้ SQL ตรงๆ
Set rst = DBEngine(0)(0).OpenRecordset("Select * from Suppliers where Companyname like ‘A*’”)
ผ่านตัวแปรที่เตรียมไว้
Varx = Inputbox(“Enter supplier prefix “)
sqlstr = ("Select * from Suppliers where Companyname like ‘” & varX & “*’”
Set rst = DBEngine(0)(0).OpenRecordset(sqlstr)
- บรรทัดที่ 6 เริ่ม Loop ในการอ่านข้อมูล rst.eof เป็นการตรวจสอบว่า rst ไปถึงรายการสุดท้ายหรือยัง ( end of file จริงๆ น่าจะเป็น end of result มากกว่า ) แน่นอนว่ามาเจอบรรทัดนี้ครั้งแรก ก็น่าจะอยู่ที่ record แรกครับ ทั้งนี้ก็ขึ้นกับการ sort ของ SQL ที่เราเปิดด้วย อย่างกรณีนี้เรา SQL ของเราเป็น Table ตรงๆ ก็จะเรียงตาม Primary Key เป็นต้น ใน Database
- บรรทัดที่ 7-8 เป็นบริเวณที่เราเข้าไปทำธุระกับข้อมูลครับ เวลาที่เราเรียก rst(“ชื่อ filed”) เราจะได้ค่าของ Field นั้นออกมา แต่มาจาก Record ไหนละ ? อันนี้ก็ขึ้นอยู่กับว่าตอนนั้น Recordset มันชี้อยู่ที่ลำดับ Record ไหน
- บรรทัดที่ 9 เป็นการสั่งให้เลื่อนไป record ต่อไป จากนั้นก็เจอ Loop ที่บรรทัดที่ 11 กลับไปที่ บรรทัดที่ 6 ใหม่ ถ้าเจอว่าไม่มี Record แล้ว พอถึงบรรทัดที่ 6 มันก็จะกระเด็นมาที่ บรรทัดที่ 12
แน่นอนครับ ถ้าทำได้เท่าๆ กับการใช้ Query แล้ว จะทำไปทำไม ให้สังเกตุตรง บรรทัดที่ 7-8 ที่เราไปอ่านค่า Supplier มา แล้วไปใช้คำสั่ง dCount เพื่อไปหาว่า Product กี่ราย ที่ Supplier รายนี้ขายให้ บรรทัดที่ 8 ยังมีการตรวจสอบว่าต่ำกว่า 2 ไหม ถ้าต่ำกว่า ก็ msgbox ออกมาเป็นชื่อ Supplier รายนั้น ตรงบริเวณนี้ เราแทรกโปรแกรมได้ เพียบครับ จะมี if จะมี loop ได้อีกเพียบ หรือ จะสร้าง Text ไฟล์ ทำอะไรได้ตามแต่ความต้องการของเราเลยครับ เราสามารถหยิบจับข้อมูลมาตรวจสอบแก้ไขได้อิสระ
วิ่งไปวิ่งมา
RecordSet ที่เปิดแล้ว เราสั่งให้วิ่งไปวิ่งมาได้ 4 แบบคือ
MoveNext | สั่งให้ไป Record ถัดไป |
MovePrevious | สั่งให้ไป Record ก่อนหน้า |
MoveFirst | สั่งให้ไป Record แรก |
MoveLast | สั่งให้ไป Record สุดท้าย |
ตรวจสอบตำแหน่ง
มี function 3 ตัวที่ใช้บ่อยๆ ครับ
EOF | ตรวจว่าอยู่ rec. สุดท้ายหรือยัง ใช้คู่กับ movenext เพื่อดูว่า move ท้ายสุดหรือยัง |
BOF | ตรวจว่าอยู่ rec. แรกหรือยัง ใช้คู่กับ moveprevious เพื่อดูว่า move บนสุดหรือยัง ปกติไม่ค่อยได้ใช้มากครับ |
RECORDCOUNT | ตรวจว่ามี rst นี้มีกี่ record ปกติจะให้ค่าเป็น 0 ถ้าไม่มี record เลย แต่ถ้าอยากทราบจริงๆ ว่ามีกี่ record ขอให้ สั่ง movelast เสียก่อน แล้วค่อยอ่านค่า recordcount ไม่งั้นจะได้ค่าที่ผิด ตย ข้างล่างนี้จะบังคับป record สุดท้ายก่อน แล้วจึงอ่านจำนวน record Sub RecordSetSup() Dim rst As Recordset Set rst = dbEngine(0)(0).OpenRecordset("Suppliers")
If rst.RecordCount = 0 Then MsgBox ("No Record Found") Set rst = Nothing Exit Sub End If rst.MoveLast MsgBox ("Total Record is: " & rst.RecordCount) rst.MoveFirst ‘—Do next command – ‘ End Sub |
แก้ไขข้อมูลผ่าน RECORDSET
นอกจากเราจะอ่านข้อมูลมาตรวจสอบได้แล้ส เรายัง Update ข้อมูลกลับไปได้ด้วยเช่นกัน อันนี้ก็จะแตกต่างจากการใช้ Action Query เพราะพวก Action Query จะเหมาะกับการทำเป็นชุด แต่ การใช้ Recordset จะยืดหยุ่นสูงกว่ามาก
การ Add ข้อมูล
การ Add ข้อมูลลงใน Table โดยใช้ Recordset สามารถทำได้ โดยการใช้คำสั่ง ADDNEW แล้วปิดท้าย ด้วยคำสั่ง Update การระบุค่าที่ต้องการ Add ก็เพียงแต่ ใส่ค่าลงใน Field ตามตัวอย่าง
rst.AddNew
rst(“SupplierName”) = “Mr. Amuro”
rst.Update
ทดลองดูการ Add ข้อมูลลงใน Table Products จำนวน 100 Record โดยการเปิด RecordSet สำหรับ การ Add โดยเฉพาะ
Sub AddTimeProduct()
Dim rst As Recordset
Dim i As Integer
Set rst = dbEngine(0)(0).OpenRecordset("Products”)
For i = 0 To 100
rst.AddNew
rst("ProductName") = "Add Number: " & i & " " & Format(Now, "dd/mm/bb hh:nn")
rst.Update
Next i
Set rst = Nothing
End Sub
การลบข้อมูล
การลบข้อมูลใน RecordSet ใช้คำสั่ง Delete ที่ขณะที่ ตำแหน่งของ Record อยู่ที่ Record ที่ต้องการลบ ทดลองดูตัวอย่างที่ใช้ในการลบข้อมูลที่ได้ทำการ Add ไว้ในตัวอย่างการ Add ข้อมูลข้างต้น โปรแกรมตัวนี้จะตรวจสอบที่ชื่อ ProductName หากมี คำว่า “Add Number “ ก็จะทำการลบ Record นั้นเสีย เพราะเป็นรายการที่เราได้ Add ไปเมื่อสักครู่นี้
Sub DeleteProduct()
Dim rst As Recordset
Dim i As Integer
Set rst = dbEngine(0)(0).OpenRecordset("Products", dbOpenDynaset)
Do Until rst.EOF
If rst("ProductName") Like "Add Number:*" Then
rst.Delete
End If
rst.MoveNext
Loop
Set rst = Nothing
End Sub
การแก้ไขข้อมูล
การแก้ไขข้อมูลบน RecordSet ใช้คำสั่ง EDIT แล้วตามด้วยคำสั่ง Update ในหว่าง EDIT และ Update เราจะกำหนดค่าที่ต้องการเปลี่ยนแปลงลงที่ ตัวแปร RecordSet วงเว็บชื่อ Field ที่ต้องการแก้ไข ซึ่งปกติ หากเราสั่งเปลี่ยนแปลงค่าที่ RecordSet โดยที่ไม่อยู่ในระหว่างคำสั่ง EDIT และ UPDATE จะเกิด Error ขึ้น
Sub ProductPriceAdjust()
Dim rst As Recordset
Dim i As Integer
Dim ratio
Set rst = dbEngine(0)(0).OpenRecordset("Products", dbOpenDynaset)
Do Until rst.EOF
Select Case rst("UnitPrice")
Case Is <= 5
ratio = 0.05
Case Is <= 10
ratio = 0.06
Case Is <= 20
ratio = 0.08
Case Is <= 50
ratio = 0.1
Case Is <= 100
ratio = 0.15
Case Is > 100
ratio = 0.2
End Select
rst.Edit
rst("UnitPrice") = rst("UnitPrice") * (1 + ratio)
rst.Update
rst.MoveNext
Loop
Set rst = Nothing
End Sub
ตัวอย่างโปรแกรมข้างต้นเป็นการ Update ราคาใน Table Product ซึ่งมีกลไกการ Update ตามขนาดราคาที่มีอยู่เดิม โดยเก็บค่าที่ต้องการขึ้นราคาไว้ใน Ratio แล้วนำไปเปลี่ยนราคา ระหว่างที่คำสั่ง EDIT และ UPDATE
ตย ข้างต้นจะเห็นชัดว่า เราทำอะไรกับ recordset ได้ซับซ้อนกว่า การใช้ query เราได้มีโอกาศทดสอบค่าในแต่ละ Record ในแต่ละ Field ก่อนทำการ Update ทำให้เราสามารถตัดสินใจเลือกเงื่อนไขที่เหมาะสมในการ Update ข้อมูล ในแต่ละ Record ได้ดีกว่า
การ Copy RecordSet จาก Form
ตามที่เราทราบว่า Form ก็ทำหน้าที่เปิด RecordSet อยู่เช่นกัน เราสามารถ Copy RecordSet จาก Form ได้โดยการใช้คำสั่ง RecordSetClone จาก Object Form ตัวอย่างต่อไปนี้ ใช้การอ้างอิงด้วย Me
Private Sub Form_Load()
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.MoveLast
MsgBox ("This form have : " & rst.RecordCount & " record.")
Set rst = Nothing
End Sub
เราสามารถอ้างอิง Me.RecordsetClone มาพักไว้ที่ Recordset ของเราเมื่อไรก็ได้ เมื่อเราได้ RecordSet แล้ว เราสามารถทำการบน RecordSet ตัวได้ได้อิสระ เช่นเดียวกับ RecordSet ทั่วไปทุกประการ
การสร้าง WEB PAGE จาก RECORDSET
ตัวอย่างนี้เป็นการแสดงให้เห็นการใช้ RecordSet ในการจัดการที่ซับซ้อน เช่นการ ใช้ RecordSet พิมพ์ Text ไฟล์ ที่มีโครงสร้างแปลก เป็นต้น ในส่วนนี้เราจะทดลองใช้ RecordSet ทำการสร้าง WEB page โดยใช้ Table Customer เป็นแหล่งข้อมูล
Sub WebGen()
Dim filex
Dim rst As Recordset
Set rst = dbEngine(0)(0).OpenRecordset("Customers")
filex = FreeFile
Open "C:\CUSTOMER.HTM" For Output As filex
Print #filex, "<HTML>"
Print #filex, "<BODY BGCOLOR = WHITE>"
Print #filex, "<Font Size = +4>CUSTOMER LIST</font><br>"
Print #filex, "<Font Size = +1>Generate from VBA Since: " & Now() & "</font><br>"
Print #filex, "<TABLE>"
Print #filex, "<TR BGCOLOR=#00007F>"
Print #filex, " <TD><FONT COLOR=#FFFFFF>CUSTOMERID</FONT></TD>"
Print #filex, " <TD><FONT COLOR=#FFFFFF>COMPANYNAME</FONT></TD>"
Print #filex, " <TD><FONT COLOR=#FFFFFF>CONTACT</FONT></TD>"
Print #filex, " <TD><FONT COLOR=#FFFFFF>PHONE</FONT></TD>"
Print #filex, " <TD><FONT COLOR=#FFFFFF>FAX</FONT></TD>"
Print #filex, "</TR>"
Do Until rst.EOF
Print #filex, "<TR BGCOLOR=#F6d6FF>"
Print #filex, " <TD>" & rst("CustomerID") & "</TD>"
Print #filex, " <TD>" & rst("CompanyName") & "</TD>"
Print #filex, " <TD>" & rst("ContactTitle") & " " & rst("ContactName") & "</TD>"
Print #filex, " <TD>" & rst("Phone") & "</TD>"
Print #filex, " <TD>" & rst("Fax") & "</TD>"
Print #filex, "</TR>"
rst.MoveNext
Loop
Print #filex, "</TABLE>"
Print #filex, "</HTML>"
Set rst = Nothing
Close
End Sub
ลองดูโปรแกรม ตย นี้ จะพบว่าไม่มีอะไรที่ซับซ้อน เพียงแต่ใช้การ LOOP ของ RECORDSET ในการพิมพ์ข้อมูลใน TABLE ออกมาเป็นไฟล์ HTML เท่านั้น การพิมพ์ก็ใช้ความสามารถในการจัดการไฟล์ ของ Access จากบทที่แล้ว ความยุ่งยากจะอยู่ที่การจัด TAG HTML ให้ลงตัวมากกว่า ไฟล์ที่ Gen ออกมา จะเป็น HTML ไฟล์ที่เป็นไปตาม Loop ที่เราสั่งพิมพ์ตามลำดับ และปิดท้ายด้วย TAG ตามปกติ หลายท่านที่เขียน ASP เป้น จะร้องอ๋อ ทันทีว่า ความจริง IIS ก็ทำงานแบบนี้นั่นเอง
บทส่งท้าย
บทความที่นำเสนอทั้ง 8 ตอน เป็นการนำเสนอการเขียนโปรแกรมใน Access แต่ผมพยายามคัดเนื้อหาที่สามารถเข้าใจความสามารถของ Access ในการพัฒนาโปรแกรมได้ง่ายออกมาเป็นหลัก และนำเสนอ tip ที่เห็นได้ชัดๆ เนื้อหาหลายๆ ส่วนจะไม่ได้มีโอกาศลงในรายละเอียดมากนัก ผมเองเข้าใจว่า ตำรา Access ที่มีอยู่ตลาดหลายๆ เล่มจะมีขายอยู่มาก เชื่อว่าทั้ง 8 บท คงจะเป็นประโยชน์ในการที่จะศึกษาเนื้อหา Access ในฐานนะ เครื่องมือในการพัฒนาได้ต่อไปนะครับ พบกันใหม่ในโอกาศต่อไปครับ
