0909

 讀取最後一筆資料
   Dim lastid As String
    lastid = Cells(Rows.Count, 1).End(xlUp).Value
    MsgBox lastid


查詢
ㄧ
布局基本架構
Private Sub CommandButton6_Click()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String
    Dim path As String
    Dim idquery As String
 
    If TextBox1.Text <> "" Then
        Call cleardata
 
 
 
    Else
        MsgBox "請輸入資料"
   End If
 
End Sub
===========================
布局例外
Private Sub CommandButton6_Click()
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sql As String
    Dim path As String
    Dim idquery As String
 
    If TextBox1.Text <> "" Then
        On Error GoTo Label1
        Call cleardata
 
 Label1:
    MsgBox "查無此筆資料"
    Call browserdata
    Else
        MsgBox "請輸入資料"
   End If
 
End Sub

=========================
Private Sub CommandButton6_Click()
    Dim conn As New ADODB.Connection  '資料庫連線
    Dim rs As New ADODB.Recordset     '資料集
    Dim sql As String                 '資料庫語言
    Dim path As String                '位址變數
    Dim idquery As String             '資料來源框查詢變數
 
    If TextBox1.Text <> "" Then
        On Error GoTo Label1
        Call cleardata
     
        idquery = TextBox1.Text
        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 where 編號='" + idquery + "'"
        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
       Exit Sub    '跳出結束
 
Label1:
    MsgBox "查無此筆資料"
    Call browserdata
 
    Else
        MsgBox "請輸入資料"
   End If
 
End Sub
============================

Public Sub browserdata()
    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