VBA连接数据库

VBA连接数据库（Mysql数据库）

    1 ODBC方式：

        首先要建立好ODBC数据源（这里命名myODBC为例），设置好要连接的DB信息

        Dim myrec as New ADODB.Recordseet

        Dim connString as String

        Dim sql as String

        connString = “Provider = MSDASQL;Data Source = myODBC;”

        sql = "select *  from table"

        myrec.Open sql,connString,adOpenForwardOnly

        While Not myrec.EOF
            Worksheets("Sheet1").Range("A2") = myrec.fields(1)

            myrec.MoveNext
        Wend

        myrec.Close

        set myrec = Nothing

    2  字符匹配串的方式：

       创建连接时直接指定IP，数据库名，账号和密码

       Dim conn AS New ADODB.Connection

       Dim myrec as New ADODB.Recordseet

       Dim connString as String

       Dim sql as String

       sql = "select *  from table"

       connString = "DRIVER={Mysql ODBC 5.1 Driver};SERVER = 127.0.0.1;PORT = 3306;DATABASE = dbname;UID = root；PWD = password"

       conn.ConnectionString = connString

       conn.Open

       myrec.Open sql,conn,adOpenForwardOnly

        While Not myrec.EOF
            Worksheets("Sheet1").Range("A2") = myrec.fields(1)

            myrec.MoveNext
        Wend

        myrec.Close

        conn.Close

        set myrec = Nothing

        set conn = Nothing

        注：当IP和PORT指定为空时，或指定不合法时，连接会默认为本地（127.0.0.1）3306端口