0907 vba

ADODB.Connection  元件
宣告：Dim 元件變數   as new  ADODB.Connection
使用：Dim conn As New ADODB.Connection
 ConnectionString = "driver={mysql odbc 5.1 driver};database=persondb;server=127.0.0.1;port=3306;uid=root;password=12345678;opt=3;smt=SET NAMES 'gb2312'"
conn.Open ConnectionString

ADODB.Recordset:   資料集
宣告：Dim 元件變數   as new  ADODB.Recordset
使用：Dim rs As New ADODB.Recordset
           rs.Open sql, conn 


SQL 語言    資料表 persondata
Dim sql As String
ql = "select * from persondata"

例子

宣告元素後ˋ，先布局 conn sql，再open 資料集，用完結束，先關資料集，再關連線裝置
由外到內，由大架構到小架構
資料庫=>連線裝置=>資料集=>資料庫操作函數=>Excel VBA應用程式

Private Sub CommandButton2_Click()

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String

 ConnectionString = "driver={mysql odbc 5.1 driver};database=persondb;server=127.0.0.1;port=3306;uid=root;password=12345678;opt=3;smt=SET NAMES 'gb2312'"
 conn.Open ConnectionString
 sql = "select * from persondata"
 rs.Open sql, conn
 MsgBox " mysql資料表打開 "

 rs.Close
 conn.Close

End Sub

資料庫操作函數
元件.BOF
元件.EOF
元件.MoveFirst()
元件.MoveLast()
元件.MoveNext()
元件.MovePrevioous()
用do while not rs.EOF()
     ... rs("編號")
     ... rs("姓名")
     ... rs("血型") 
     ... rs("學歷")
     ... rs("職務")
    loop

例子
Private Sub CommandButton3_Click()
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String

 ConnectionString = "driver={mysql odbc 5.1 driver};database=persondb;server=127.0.0.1;port=3306;uid=root;password=12345678;opt=3;smt=SET NAMES 'gb2312'"
 conn.Open ConnectionString
 sql = "select * from persondata"
 rs.Open sql, conn

 rs.MoveFirst
 Do While Not rs.EOF
    MsgBox rs("姓名")
    rs.MoveNext
 Loop

 rs.Close
 conn.Close
End Sub



Excel VBA應用程式
1.找到有效位址  Cells(Rows.Count, 1).End(xlUp).Select
2.取得有效位置的絕對位置  path = Selection.Address
3.偏移植 填入欄位值
       Range(path).Offset(1, 0).Value = rs("編號")
       Range(path).Offset(1, 1).Value = rs("姓名")
       Range(path).Offset(1, 2).Value = rs("血型")
       Range(path).Offset(1, 3).Value = rs("學歷")
       Range(path).Offset(1, 4).Value = rs("職務")
4.移下一筆資料表指標
Private Sub CommandButton4_Click()
    Call cleardata
    Sheets(1).Cells(1, 1).Value = "編號"
    Sheets(1).Cells(1, 2).Value = "姓名"
    Sheets(1).Cells(1, 3).Value = "血型"
    Sheets(1).Cells(1, 4).Value = "學歷"
    Sheets(1).Cells(1, 5).Value = "職務"
   
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String
    Dim path As String

    ConnectionString = "driver={mysql odbc 5.1 driver};database=persondb;server=127.0.0.1;port=3306;uid=root;password=12345678;opt=3;smt=SET NAMES 'gb2312'"
    conn.Open ConnectionString
    sql = "select * from persondata"
    rs.Open sql, conn
   
    rs.MoveFirst
    Do While Not rs.EOF
       Cells(Rows.Count, 1).End(xlUp).Select
       path = Selection.Address
       Range(path).Offset(1, 0).Value = rs("編號")
       Range(path).Offset(1, 1).Value = rs("姓名")
       Range(path).Offset(1, 2).Value = rs("血型")
       Range(path).Offset(1, 3).Value = rs("學歷")
       Range(path).Offset(1, 4).Value = rs("職務")
       rs.MoveNext
 Loop

 rs.Close
 conn.Close
   
   
End Sub
Public Sub cleardata()
    Dim rcount As Integer
    rcount = Sheets(1).UsedRange.Rows.Count
    Range("A2:E" & rcount).ClearContents
End Sub