?9?ADO ?????????

1
?9?ADO ?????????
2
????

• ??ADO?????????
• ??ADO??????????
• ??????????????
• ??????????????
• ??DataGrid????????
• ???Access???MySQL??????
• ???Access SendObject????E-mail???

3
? ?

• 9-1 ADO??
• 9-2 ADO????
• 9-3 DataGrid????
• 9-4 ????

4
9-1 ADO??

• 9-1-1 ADO??
• 9-1-2 ADO????
• 9-1-3 ????

5
9-1-1 ADO??

• ADO?ActiveX Data Object????
• ?????OLEDB????,??????????????????
• ????????????????????????????????????????,?????????
  ?????

6
9-1-1 ADO??(?)

• ADO??
• ??SQL???????
• ??Internet??????
• ?????????
• ?????????XML????
• ??XML????
• ???????????

7
ADO??

• ???????
• ??????(Stored Procedures)
• ????????????
• ??????
• ????????????
• ???????????

8
9-1-2 ADO????
9
9-1-3 ????

• ??ADO?????,????ADO?????????
• ?????DAO?ADO??,??DAO??ADO??

10
9-2 ADO????

• 9-2-1 ????
• 9-2-2 ?????
• 9-2-3 ??????
• 9-2-4 ??????????
• 9-2-5 ?????
• 9-2-6 ???????????

11
9-2-1 ????

• ADO??????????
• ?????????(ADODB.Connection)
• ??DSN???

12
?????????

• ?conModule??????????
• Public adCon As New ADODB.Connection ??????????
• Public adRs As New ADODB.Recordset
  ???????????

13
?????????(?)

• 2. ????????
• 1. Public Sub checkCon(i As Integer)
• 2. On Error GoTo chkcon_err
• 3. If i 1 Then
• 4. If Not adCon.state adStateOpen Then
  Call openCon()
• 5. Else
• 6. Call closeCon
• 7. End If
• 8. Exit Sub
• 9. chkcon_err
• 10. MsgBox Err.Description
• 11. End Sub

14
?????????(?)

• 3. ????????
• 1. Public Sub openCon()
• 2. On Error GoTo opnCon_err_end
• 3. Dim cn_str As String
• 4. cn_str DRIVER MySQL ODBC 3.51 Driver
  " _ "SERVER localhost" _
• " DATABASE mysal" _
• "User ?? Password ?? OPTION3"

15
3. ????????(?)

• 5. If Not adCon.state adStateOpen Then
• 6. adCon.CursorLocation adUseClient
• 7. adCon.ConnectionString cn_str
• 8. adCon.Open
• 9. End If
• 10. Exit Sub
• 11. opnCon_err_end
• 12. MsgBox Err.Description
• 13. End Sub

16
4. ????????

• 1. Public Sub closeCon()
• 2. If adCon.state adStateOpen Then
• 3. adCon.Close
• 4. Set adCon Nothing
• 5. End If
• 6. End Sub

17
9-2-2 ?????

• 1. ?????????
• ???????.Open ???,???,???,???
• ?????????????
• OpenADO??????
• ???SQL??,????Select?????
• ?????????????
• ????????????,??????9-1??
• ?????????,??????9-2??

18
?9-1??????????
?????? ?????
adOpenForwardOnly ????????,???? ???????????
adOpenStatic ????????,??????????? ????????????????????,
adOpenKeyset ?????Static?Dynamic??,????????????????????????????????????,?????????????????????????,?????????
adOpenDynamic ?????????????,?????????????,???????,?????Commit????,??????
19
?9-2 ???????
?????? ?????
adLockReadOnly ???????
adLockBatchOptimistic ????,????????,????????????BatchUpdate???,???????
adLockPessimistic ?????,????????Edit???,?????
adLockOptimistic ??????????Edit???,?????,????Update????????
20
2. ?????????

• 1. Public Function openRs(rstr As String) AS
  ADODB.Recordset
• 2. On Error Goto openRs_err
• 3. If adRs.state adStateOpen Then adRS.Close
• 4. adRs.Open rstr, adCon, adOpenDynamic,
  adLockBatchOptimistic
• 5. adRs.MarshalOptions adMarshalModifiedOnly
• 6. Set adRs.ActiveConnection Nothing
• 7. Set openRsadRS
• 8. Exit Function
• 9. openRS_er
• 10. Msgbox Err.Description
• 11. End Function

21
3. ?????

• 1. Public Sub closeRs()
• 2. If adRs.state adStateOpen Then
• 3. adRs.Close
• 4. Set adRs Nothing
• 5. End If
• 6. End Sub

22
9-2-3 ????????

• ?????,?????ADO????????
• ??????

23
1. ????????

• ??On Open ????
• 1. Private Sub Form_Open(Cancel As Integer)
• 2. Dim str As String
• 3. Call checkCon(1)
• 4. str Select From cuinfo
• 5. Call openRs(str)
• 6. End Sub

24
??????

• ?????,??On Close????????????
• 1. Private Sub Form_Close()
• 2. adRs.Filter ""
• 3. Set adRs.ActiveConnection adCon
• 4. adRs.Filter adFilterPendingRecords
• 5. adRs.UpdateBatch
• 6. adRs.Close
• 7. Set adRs Nothing
• 8. End Sub

25
????

• ?????????On Click????
• ??adRs.Addnew
• adRs.Update

26
3. ????

• 3.1 ????????
• ??????Filter ???? ????? ????
• ????????????gt?lt,?ltgt(?????)
• ???????????, ?????????????????
  , ??????????????????????

27
3. ????(?)

• ??adRs.Filter "CU_No '" Me!CU_No "'
• ???????adRs.EOF
• ???adRs.Nomatch??
• ??????,?????adRs.Filter(??adRs.FilteradFilterN
  one),???????????,?????????????????,??????????

28
Filter?????????
???? ? ?
adFilterAffectedRecords ???????????????????????
adFilterConflictingRecords ????????????????
adFilterFetchedRecords ??????????????????
adFilterNone ??????????????????
adFilterPendingRecords ??????????????????,??????????
29
????????

• ?????????On Click????
• 1. Private Sub ??_Click()
• 2. adRS.Filter CU_No Me!CU_No
• 3. If adRS.EOF Then
• 4. MsgBox "???????
• 5. Else
• 6. Me!CU_No adRs!CU_No
• 7. .(??????)
• 8.
• 9. End If
• 10. End Sub

30
4. ????

• ??
• adRs.Delete
• ?????????On Click????

31
5. ??/????

• ??
• adRs.Update
• (?????adRs.Edit)
• ??
• ?????????On Click????,?????????????,?????,????????
  ??,????????,?????

32
??/????????

• 1. Private Sub??_Click()
• 2. adRs.Filter "
• 3. adRs.Filter "CU_No '" Me!CU_No "'
• 4. If Not adRs.EOF Then
• 5. With adRs
• 6. !CU_No Me!CU_No
• 7. .
• 8. .Update
• 9. End With
• 10 End If
• 11 End Sub

33
6. ????

• ??
• adRs.UpdateBatch
• ??
• ?????????On Click????

34
????????

• 1. Private Sub??_Click()
• 2. adRs.Filter "
• 3. Set adRs.ActiveConnection adCon
• 4. adRs.Filter adFilterPendingRecords
• 5. adRs.UpdateBatch
• 6. Set adRs.ActiveConnection Nothing
• 7. End Sub

35
9-2-4 ??????????

• 1. ?????????SQL??
• ????
• adCon.Execute SQL??
• ????????
• adCon.BeginTrans ????????
• adCon.Execute str ????????
• adCon.CommitTrans ??????

36
9-2-4 ??????????(?)

• 3. ????????,?????????????
• ????
• adCon.RollBackTrans ??????

37
9-2-5 ?????

• ??????,?????????????????,?????????????
• ???????
• ????????(Command)??
• Dim adCmd AS ADODB.Command (??????????)
• ????Parameter????
• Dim qryPar AS ADODB.Parameter (??????????)

38
????(?)

• ??????????????????
• Dim str As String
• ???????On Open??????
• Str Select From cuino Where CU_No ?
• ????????????
• adCmd.CommandText str
• ????????????????
• adCmd.CommandType adCmdText

39
????(?)

• ???????????
• adCmd.Prepared True
• ??????,???cuno, ??????adVarchar,????255
• Set qryPar adCmd.CreateParameter(cuno,adVarchar
  , adParmInput, 255)
• ???????????
• adCmd.Parameters.Append qryPar

40
????(?)

• ????????????
• adCmd.ActiveConnection adCon
• ???????????
• adCmd(cuno) Me!CU_No
• ???????On Click??????
• Set adRs adCmd.Execute

41
??????

• ????????????????
• ??????????,??????????????????,?????????????
• ???????????,??SQL????Where?????????????,????Filter
  ???????
• ???????????

42
9-2-6 ???????????

• ??????????????(?)??????
• ADO???DAO???Find??,????Filter???????

43
9-3 DataGrid????

• 9-3-1 DataGrid????
• 9-3-2 DataGrid????

44
9-3-1 DataGrid????

• ADO??????DataGrid??,?????????????
• ?????????,?????????DataGrid???,????????????????,??
  ??????????

45
DataGrid??????

• ???????ADO???????,?????????
• ???????tblDa,???????????,???????????SQL??
• Select Distinct table From sysTable Where
  database 'mysal'
• ???????qryDa,???????????,????????????SQL??,?????
  ????2(????????)
• Select qryID, qryName From qryTable Where
  database 'mysal'

46
DataGrid??????(?)

• 4. ????????tblCmd,??????????????
• 5. ????????qryCmd,????????????
• 6. DataGrid??????dtlData,

47
DataGrid??????(?)

• DataGrid?????????
• ?????????????ActiveX???(c)?
• ???ActiveX?????????,???Microsoft DataGrid
  Control, Version 6.0?,???????

48
DataGrid??????(?)

• 7. ?????DataGrid????,????????????????,???????dtlDa
  ta
• 8. ???????????On Open??????,??????,???????????
• Private Sub Form_Open(Cancel As Integer) Call
  checkCon(1) ??????
• End Sub

49
DataGrid??????(?)

• 9. ??????????????On Click??????,??????
• Private Sub tblCmd_Click()
• Dim str As String
• If IsNull(tblDa) Then Exit Sub
• str "Select From " Me!tblDa ""
• Call openRs(str)
• Set dtlData.DataSource adRs
• End Sub

50
DataGrid??????(?)

• 10. ??????????????On Click??????,??????
• Private Sub qryCmd_Click()
• Dim str As String, rstr as String
• Dim rsq As Recordset
• Dim dbs As Database
• If IsNull(qryDa) Then Exit Sub
• rstr"Select from qryTable Where qryIDqryDa
  
• Set dbs CurrentDb
• Set rsq dbs.OpenRecordset(rst)
• str rsq!qrySQL
• Call openRs(str)
• Set dtlData.DataSource adRs
• End Sub

51
DataGrid??????(?)

• 11. ???????????On Close??????,????????,??????????
• Private Sub Form_Close(Cancel As Integer) Call
  checkRs
• End Sub

52
????
53
9-3-2 DataGrid????

• 1. DataSource??????????
• Set ???????.DataSource ?????
• 2. Columns???????
• ?????????.Columns(i),i ?0??
• 3. Columns(i).Value???????
• ???????????.Columns(i).Value
• ?????????.Columns(i).Value??

54
9-3-2 DataGrid????(?)

• 4. Columns(i).Width???????
• ?????????.Columns(0).Width230(?twis???)
• 5. Columns(i).Caption???????
• ?????????.Columns(0).Caption????
• 6. HeadFont.Size???????????
• ?????????.HeadFont.Size 10

55
9-3-2 DataGrid????(?)

• 7. Font.Size???????????
• ?????????.Font.Size 10
• 8. AllowAddNew/ AllowDelete/ AllowUpdate????/??/?
  ?
• ?????????.AllowAddNew True
• ???????.AllowDelete False
• 9. AllowArrows???????
• ?????????.AllowArrows TrueFalse
• True,????????????????
• False,???????????

56
9-3-2 DataGrid????(?)

• 10. WrapCellPointer?????????
• ?????????.WrapCellPointer TrueFalse
• True,??????????????
• False,?????????????
• 11. TabActionTab???
• ?????????.TabAction 012
• 0 (dbgControlNavigation),Tab?????????)
• 1 (dbgColumnNavigatio) ,Tab????????,???????????,?
  ????????
• 2 (dbgGridNavigation) ,?1??,????WrapCellPointer?,
  ??????????

57
9-4 ????

• 9-4-1 ?Access?????
• 9-4-2 ?Email?????
• ???????????

58
9-4-1 ?Access?????
59
9-4-2 ?Email?????
60
SendObject ????

• SendObject ????,?????? Access ????????????????????
  ????????????,???????
• ??????????Outlook?Microsoft Exchange????? Mail
  Applications Programming Interface (MAPI)
  ?????????
• ?????????? Excel 2000 (.xls)???? (.txt)?RTF ??
  (.rtf) ? HTML (.html) ?????

61
SendObject ????(?)

• SendObject ???????
• ??DoCmd.SendObject ???, ???, ???, ???, ???, ???,
  ???, ???, ???
• ???????????,??????????????????????????
• ???????????
• ?????????,???acFormatXLS?acFormatTXT?acFormatHTML
  ??acFormatRTF?????

62
SendObject ????(?)

• ??????
• ?????
• ???????
• ?????

63
SendObject ????(?)

• ???????
• ????????,?????????????
• ???????????????,??????????
• ??????????,???????????????
• ??? ???? ? ???? ??????,??????????????????????

64
SendObject ????(?)

• ??????????????????
• ???? ?(-1),??????????????,??????
• ???? ?(0),??????,????????????
• ?????? ?