vba Excel连接数据库

PostgreSql:

 

第一步 在网上下载postres的驱动程序，之后安装，下载地址:https://www.devart.com/odbc/postgresql/download.html

第二步 创建ODBC数据源

点击“开始-》控制面板-》管理工具-》数据源（ODBC）-》用户DSN-》添加”

 

安装上图配置好之后写入VBA代码并导入包文件

 

Private Sub CommandButton1_Click()

    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim dataBase As String
    Dim userName As String
    Dim password As String
    Dim DBname As String
    Dim openCommand As String
    dataBase = "PostgreSQL35W"
    userName = "postgres"
    password = "test1234"
    DBname = "testRPA"
    openCommand = "DSN=" & dataBase & ";" & "UID=" & userName & ";" & "PWD=" & password & ";" & "Database=" & DBname
    cnn.Open openCommand
    Dim SQL As String
    '検索
    SQL = "select id from test where ID = '1001'"
    rs.Open SQL, cnn
    While Not rs.EOF
        MsgBox rs!ID
        rs.MoveNext
    Wend
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cn = Nothing
    
    '削除
    SQL = "delete from test where ID = '1001'"
    Call cnn.Execute(SQL)
    
    '修正
    SQL = "update test set NAME = 'name123' where ID = '1002'"
    Call cnn.Execute(SQL)
    
    
    
End Sub

 

 

Mysql:

下载数据源链接，之后创建mysql的数据源

下载地址
https://dev.mysql.com/downloads/connector/odbc/

创建数据源

VBA包文件也要引入

写入VBA代码

'MySql接続
Private Sub mySql_Click()
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    conn.ConnectionString = "DSN=mysqlConntion;Server=localhost;DB=zhd_sale_demo;UID=root;PWD=123qwe!@#;OPTION=3;"
    conn.Open

    rs.Open "select * from `ims_wcy_hand_over`", conn
     While Not rs.EOF
        MsgBox rs!WEID
        rs.MoveNext
    Wend
    
    rs.Close: Set rs = Nothing
    conn.Close: Set conn = Nothing
End Sub

 

执行即可!