【MySQL + VBA】从Access将数据update到MySQL

MS Access, MySQL, Ado

---

一. Access连接到MySQL：

在本机状态下，用mysql（8.0）做后台，用Access（2019）做前端`
并在VBA界面中，点『运行』-『引用』-microsoft activex data objects 6.1 library，做好ADO的引用

连接MySQL的准备

Option Compare Database
Option Explicit

	Const MYDRIVER = "Driver={MySQL ODBC 8.0 Unicode Driver};"      '驱动程序
	Const MYSERVER = "Server=127.0.0.1;"                            '服务器
	Const MYPORT = "Port=3306;"                                     '端口
	Const MYDATABASE = "Database=abc;"                              'MySQL数据库名称
	Const MYUSER = "User=root;"                                     '用户名
	Const MYPASS = "PWD=123456789abcd;"                             'Mysql密码
	Public constr As String

ADO的定义

在Option Compare Database下

    Public cn As New ADODB.Connection                              
    Public rs As New ADODB.Recordset

组装连接MySQL的准备

	constr = MYDRIVER & MYSERVER & MYPORT & MYDATABASE & MYUSER & MYPASS
	cn.ConnectionString = constr
	cn.Open

二、update

1. 不同字段多条件或一个条件更新

Dim strSQL1 As String
	strSQL1 = "UPDATE Mesa_面试者 SET " & _
				"招聘渠道汇总 = If(招聘渠道 In ('前程无忧','拉勾','智联','Boss直聘','猎聘','人才热线','58同城'),'网络招聘','推荐'), " & _
				"期望薪酬=replace(期望薪酬,'元/月',''), " & _
				"电话=replace(电话,'+86 ','')

2. 同一字段多条件update

Dim strSQL2 As String
	strSQL2 = "UPDATE Mesa_面试者 SET 工作经验 = CASE " & _
				"when 工作经验 Like '*年*'then replace(工作经验,'年','') " & _
				"when 工作经验 Like '*以上*'then replace(工作经验,'以上','') " & _
				"when 工作经验 Like '*工作经验*'then replace(工作经验,'工作经验','') " & _
				"when 工作经验 Like '*暂无工作经验*' then '0' " & _
				"when 工作经验 Like '*在读学生*' then '0' " & _
				"WHEN 工作经验='3' THEN '3～4' " & _
				"WHEN 工作经验='4' THEN '3～4' " & _
				"WHEN 工作经验='5' THEN '5～7' " & _
				"WHEN 工作经验='6' THEN '5～7' " & _
				"WHEN 工作经验='7' THEN '5～7' " & _
				"WHEN 工作经验='8' THEN '8～9' " & _
				"WHEN 工作经验='9' THEN '8～9' " & _
				"WHEN 工作经验='10' THEN '10～15' " & _
				"WHEN 工作经验='11' THEN '10～15' " & _
				"WHEN 工作经验='12' THEN '10～15' " & _
				"WHEN 工作经验='13' THEN '10～15' " & _
				"WHEN 工作经验='14' THEN '10～15' " & _
				"WHEN 工作经验='15' THEN '10～15' " & _
				"Else 工作经验 " & _
				"END;"

三、执行

Dim cm_1 As New ADODB.Command '创建一个Command对象的实例
cm_1.ActiveConnection = cn
cm_1.CommandText = strSQL1
cm_1.Execute

Dim cm_2 As New ADODB.Command '创建一个Command对象的实例
cm_2.ActiveConnection = cn
cm_2.CommandText = strSQL2
cm_2.Execute

四、释放内存

	Set cm_1 = Nothing
	Set cm_2 = Nothing
	cn.Close: Set cn = Nothing

五、完整代码

Option Compare Database
Option Explicit

'连接MySQL的准备
	Const MYDRIVER = "Driver={MySQL ODBC 8.0 Unicode Driver};"      '驱动程序
	Const MYSERVER = "Server=127.0.0.1;"                            '服务器
	Const MYPORT = "Port=3306;"                                     '端口
	Const MYDATABASE = "Database=abc;"                              'MySQL数据库名称
	Const MYUSER = "User=root;"                                     '用户名
	Const MYPASS = "PWD=123456789abcd;"                             'Mysql密码
	Public constr As String

'ADO的定义    	
	Public cn As New ADODB.Connection                              
	Public rs As New ADODB.Recordset

Private Sub Command0_Click()

'组装连接MySQL的准备
	constr = MYDRIVER & MYSERVER & MYPORT & MYDATABASE & MYUSER & MYPASS
	cn.ConnectionString = constr
	cn.Open
	
'update
	'1. 不同字段多条件或一个条件更新	
	Dim strSQL1 As String
	strSQL1 = "UPDATE Mesa_面试者 SET " & _
			"招聘渠道汇总 = If(招聘渠道 In ('前程无忧','拉勾','智联','Boss直聘','猎聘','人才热线','58同城'),'网络招聘','推荐'), " & _
			"期望薪酬=replace(期望薪酬,'元/月',''), " & _
			"电话=replace(电话,'+86 ','') "
			
	'2. 同一字段多条件update
	Dim strSQL2 As String
	strSQL2 = "UPDATE Mesa_面试者 SET 工作经验 = CASE " & _
			"when 工作经验 Like '*年*'then replace(工作经验,'年','') " & _
			"when 工作经验 Like '*以上*'then replace(工作经验,'以上','') " & _
			"when 工作经验 Like '*工作经验*'then replace(工作经验,'工作经验','') " & _
			"when 工作经验 Like '*暂无工作经验*' then '0' " & _
			"when 工作经验 Like '*在读学生*' then '0' " & _
			"WHEN 工作经验='3' THEN '3～4' " & _
			"WHEN 工作经验='4' THEN '3～4' " & _
			"WHEN 工作经验='5' THEN '5～7' " & _
			"WHEN 工作经验='6' THEN '5～7' " & _
			"WHEN 工作经验='7' THEN '5～7' " & _
			"WHEN 工作经验='8' THEN '8～9' " & _
			"WHEN 工作经验='9' THEN '8～9' " & _
			"WHEN 工作经验='10' THEN '10～15' " & _
			"WHEN 工作经验='11' THEN '10～15' " & _
			"WHEN 工作经验='12' THEN '10～15' " & _
			"WHEN 工作经验='13' THEN '10～15' " & _
			"WHEN 工作经验='14' THEN '10～15' " & _
			"WHEN 工作经验='15' THEN '10～15' " & _
			"Else 工作经验 " & _
			"END;"

'执行								
    Dim cm_1 As New ADODB.Command '创建一个Command对象的实例
    cm_1.ActiveConnection = cn
    cm_1.CommandText = strSQL1
    cm_1.Execute
    
    Dim cm_2 As New ADODB.Command '创建一个Command对象的实例
    cm_2.ActiveConnection = cn
    cm_2.CommandText = strSQL2
    cm_2.Execute

'释放内存
	Set cm_1 = Nothing
    Set cm_2 = Nothing
    cn.Close: Set cn = Nothing
end sub