vba Excel连接数据库

PostgreSql:

 

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

第二步 创建ODBC数据源

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

 

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

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

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<br><br>

 

 

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

 

执行即可!