vba操作mysql_查询

1. 环境配置：https://www.cnblogs.com/SH170706/p/7714829.html
2. MySQL的ODBC驱动下载及安装：https://blog.csdn.net/weixin_38746118/article/details/94878714
3. 报错Error 1918：安装visual c++ 2013，下载老版本的odbc，https://dev.mysql.com/downloads/connector/odbc/5.3.html
4. 关于ConnectionString 的option=3：指定连接器/ ODBC应该如何工作的各种标志的数值的总和,https://stackoverflow.com/questions/4407841/whats-the-option-n-in-the-mysql-odbc-connection-string

feild有20个peoperties，第19个是KEYCOLUMN，测试发现如果哪几个字段是主键它的KEYCOLUMN就是true，否则就是false。似乎这个是标识主键的属性，但没搜到证据。

需要将获取connection对象，关闭和查询封装成方法，select语句的条件和字段用数组传入。

Sub TestConnectTodb()
    'Dim rs As ADODB.Recordset
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    conn.ConnectionString = "DRIVER={MySql ODBC 5.3 Unicode Driver};Server=localhost;Database=jd;uid=root;pwd=;Option=3;"
    
    'On Error GoTo closeC
    conn.Open
    'can not set rs after rs.open, it seems better than setted in sql sentence
    rs.MaxRecords = 100                          
    
    sql1 = "select * from s1"
    'sql1 = "select pk,ppk,order from q"
    rs.Open sql1, conn

    With rs			' it seems that this block is useless
    	'default -1,    返回一个 Recordset 对象中的记录数目。
        rc = .RecordCount                        
        'default 0,now 100,设置或返回从一个查询返回 Recordset 对象的的最大记录数目。
        mr = .MaxRecords                          
        'default -1,返回一个 Recordset 对象中的数据页数。
        pc = .PageCount                           
        'default 10,设置或返回 Recordset 对象的一个单一页面上所允许的最大记录数。
        ps = .PageSize                                
    End With
    
    
    'On Error GoTo closeRC
    With ThisWorkbook.Worksheets("MySqlData")
         '.Visible = True

         .Cells.ClearContents
         .Range("a1:c1").Value = Array("pk", "ppk", "order")

         '.Range("A2").CopyFromRecordset rs            'fill data into sheet way 1,recommend this
         
         arr1 = rs.GetRows                              'fill data into sheet way 2
         'arr1 = rs.GetRows(, , Array("pk", "order"))       'can select partial fields from data
         arr = Application.Transpose(arr1)
         .Range("A2").Resize(UBound(arr, 1) - LBound(arr, 1) + 1, UBound(arr, 2) - LBound(arr, 2) + 1) = arr

    End With
    Set fs = rs.Fields
    For Each f In fs
        Debug.Print f.Name                               'get all filed names-->pk, ppk, order
        For Each p In f.Properties
        	'it seems that fieldA.KEYCOLUMN=true -->fieldA IS PRIMARY KEY??
            Debug.Print vbTab & p.Name & "-->" & p.Value        
        Next p
    Next f
    Set pses = rs.Properties


closeRC:
    rs.Close
    
closeC:
    conn.Close
    Set conn = Nothing
    Set rs = Nothing

End Sub