【MySQL + VBA】从Access将excel数据insert到MySQL

MS Access, MySQL, Ado

---

一. Access连接到MySQL：

1. 在本机状态下，用mysql（8.0）做后台，用Access（2019）做前端`
2. 并在VBA界面中，点『运行』-『引用』-
   microsoft activex data objects 6.1 library，
   microsoft excel 16.0 object library,

连接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

二、Excel的准备

1. Excel的连接

    Dim EXL As New Excel.Application
    EXL.Workbooks.Open "C:\Users\Administrator\Desktop\mesa_入职.xlsx"
    EXL.ActiveWorkbook.Sheets(1).Select

2. Excel的取数范围

	Dim Crow As Long, Lrow As Long

    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    For Crow = Lrow To 2 Step -1

	Dim 地区 As String, 职级 As String
	Dim 入职日期 As String, 离职日期 As String

    地区 = Sheets("mesa_入职").Range("B" & Crow).Value
    职级 = Sheets("mesa_入职").Range("N" & Crow).Value    
    入职日期 = Sheets("mesa_入职").Range("F" & Crow).Value
    离职日期 = Sheets("mesa_入职").Range("G" & Crow).Value

三、SQL文（insert）

Dim sqlstr As String
    sqlstr = "INSERT INTO mesa_入职_临时(地区, 职级, 入职日期, 离职日期) " & _
            "VALUES('" & 地区 & "'," & _
                    "'" & 职级 & "'," & _
                    "'" & 入职日期 & "'," & _
                    "'" & 离职日期 & "')"

四、执行

Dim cm As New ADODB.Command
    cm.ActiveConnection = cn
    cm.CommandText = sqlstr
    cm.Execute
Next

五、释放内存

    Set cm = 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
	
'Excel的准备
	'1. Excel的连接	
	Dim EXL As New Excel.Application
	EXL.Workbooks.Open "C:\Users\Administrator\Desktop\mesa_入职.xlsx"
	EXL.ActiveWorkbook.Sheets(1).Select
			
	'2. Excel的取数范围
	Dim Crow As Long, Lrow As Long

    Lrow = Cells(Rows.Count, 1).End(xlUp).Row
    For Crow = Lrow To 2 Step -1
    
	Dim 地区 As String, 职级 As String
	Dim 入职日期 As String, 离职日期 As String

    地区 = Sheets("mesa_入职").Range("B" & Crow).Value
    职级 = Sheets("mesa_入职").Range("N" & Crow).Value
    
    入职日期 = Sheets("mesa_入职").Range("F" & Crow).Value
    离职日期 = Sheets("mesa_入职").Range("G" & Crow).Value

'SQL文（insert）

	Dim sqlstr As String
    sqlstr = "INSERT INTO mesa_入职_临时(地区, 职级, 入职日期, 离职日期) " & _
            "VALUES('" & 地区 & "'," & _
                    "'" & 职级 & "'," & _
                    "'" & 入职日期 & "'," & _
                    "'" & 离职日期 & "')"
'执行								
Dim cm As New ADODB.Command
    cm.ActiveConnection = cn
    cm.CommandText = sqlstr
    cm.Execute
Next

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