วันเสาร์ที่ 4 มิถุนายน พ.ศ. 2554

CONNECT ORACLE ด้วย VBA SCRIPT ใน ACCESS

หากทำการ LINK TABLE จาก ODBC มายัง ACCESS   แล้วมีการเรียกใช้ LINK TABLE ดังกล่าว  ครั้งแรก ACCESS จะทำการถาม LOGIN, PASSWORD ที่จะไป CONNECT กับ DATABASE ที่เราทำ LINK ไว้  นอกจากจะถาม LOGIN PASSWORD เราแล้ว ยังให้เปลี่ยน DSN ของ ODBC ที่ตั้งไว้ได้ด้วย    อาการแบบนี้ จะไปสร้าง ความเสี่ยง ให้ USER ไปเลือก DSN ตัวอื่นที่เราไม่ต้องการได้



วิธีที่ดีที่สุด คือการทำหน้าตา LOGIN ของเราขึ้นมา แล้วทำการ ต่อ CONNECTION ไปยัง ODBC รอไว้ก่อน   ให้ใช้ CODE  sysTestLogin  ตามนี้ได้เลยครับ,  ส่วน Sub testLogin ผมทำมาทดสอบ การ LOGIN นำไปปะที่ formLogin ได้เลย

ตย Code ที่แสดง เป็นการ Login ไป Oracle นะครับ  ถ้าเป็นการ Connect ไปยัง SQL SERVER หรือ ODBC อื่นๆ   การทำ ConnStr อาจแตกต่างกันไปครับ


Option Compare Database
Option Explicit
Sub testLogin()
   
    If sysTestLogin("MYDB", "mydblogin", "xg1234") Then
       '--- GOTO LOGIN PASS PROCESS
       '
       '
   
   
       MsgBox "OK, LOGIN PASS"
    Else
       '--- GOTO LOGIN ERROR PROCESS
       '
       '
      
    End If

End Sub
Function sysTestLogin(sysDSNName, sysLogin, sysPassword)
   
    On Error GoTo log_error
    Dim dbx As Database
    Dim wkODBCDirect  As Workspace
    Dim x, ConnStr
   
    ConnStr = "ODBC;DSN=" & sysDSNName & ";DBQ=" & sysDSNName & ";"
    ConnStr = ConnStr & "UID=" & sysLogin & ";"
    ConnStr = ConnStr & "PWD=" & sysPassword & ";"
   
   
    Set wkODBCDirect = CreateWorkspace("", "", "", dbUseODBC)
    Set dbx = wkODBCDirect.OpenDatabase("", dbDriverNoPrompt, True, ConnStr)
    Set dbx = DBEngine(0).OpenDatabase("", dbDriverNoPrompt, False, ConnStr)
   
    x = SysCmd(acSysCmdSetStatus, "LOGIN PASS !")


    Set dbx = Nothing      ' Close database but keep connection.
    sysTestLogin = True
    Exit Function
log_resume:


    sysTestLogin = False
    Exit Function
   
log_error:
    MsgBox "ODBC Error: Initial connection error" & vbCrLf & vbCrLf & DBEngine.Errors(0) & vbCrLf & vbCrLf, vbExclamation
    Resume log_resume
   
End Function


วันอาทิตย์ที่ 13 กุมภาพันธ์ พ.ศ. 2554

DB-102 DATABASE PROGRAMMING

หนังสือ DATABASE PROGRAMMING เขียนไว้นานแล้วครับ  เป็นยุคที่มีการจัดอบรมโปรแกรมเมอร์กัน เยอะๆ  บริษัทที่ผมทำงานอยู่ในยุคนั้น ก็จัดทำ COURSE นี้ด้วย   ผมเห็นว่าการเขียน APPLICATION ด้วย VB ซึ่งสมัยนั้นก็เป็น VB6 ให้ออกมาเป็น APPLICATION งานฐานข้อมูล นั่น ค่อนข้างใช้เวลา แล้วทำให้ใช้งานจริงๆ  ค่อนข้างยุ่งยาก

ในทางกลับกัน การใช้ ACCESS มาทำ เป็น APPLICATION แบบเต็มตัว ไม่ค่อยมีคนทำกัน ด้วยความที่บ้านเรา ส่วนใหญ่มอง MS ACCESS เป็นส่วนหนึ่งของชุด OFFICE   หนังสือก็หายาก ท้ายที่สุด ก็ตัดสินใจ เขียนขึ้นมา   เพื่อมาใช้ในโครงการอบรมโปรแกรมเมอร์  ที่ทางบริษัทจัดขึ้น



ภายหลัง เมื่อจบโครงการอบรม  ผมก็ใช้ เอกสารชุดนี้ อบรม ให้ โปรแกรมเมอร์ ทั้งลูกน้องตัวเอง และ  ทีมงานของลูกค้า ที่ต้องดูแล APPLICATION ที่ผมทำด้วย MS ACCESS   เพื่อปูพื้นคนที่เป็น PROGRAMMER ให้ เข้ามาใช้ MS ACCESS ในการนำไปใช้เป็น TOOLS ในการพัฒนา โปรแกรมด้านฐานข้อมูล แทนที่จะใช้มันเป็น ฐานข้อมูลเฉยๆ


Donwload กันได้ ตามสะดวกครับ
DB-102 DATABASE PROGRAMMING

ที่เรียกว่า DB-102 เพราะมี DB-101 ด้วย  แต่เป็นเนื้อหา MS ACCESS  ตอนต้นๆ ที่เกี่ยวกับ การใช้งานACCESS ทั่วๆไป ก่อนจะมา CODING   ฉบับ DB-101 ผมไม่ได้เป็นคน จึงไม่ได้มีมา POST กันครับ

8 : RECORD ใน ACCESS

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 ในฐานนะ เครื่องมือในการพัฒนาได้ต่อไปนะครับ   พบกันใหม่ในโอกาศต่อไปครับ