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
执行即可!