【MySQL + VBA】Access根据combobox和文本框取值，更新列表框

Allan_lam

已于 2023-05-06 15:47:02 修改

阅读量994

点赞数

分类专栏： Ado VBA MySQL 文章标签： mysql microsoft vba access 数据库

于 2022-07-11 13:33:05 首次发布

本文链接：https://blog.csdn.net/Allan_lam/article/details/125719428

版权

VBA 同时被 3 个专栏收录

6 篇文章 0 订阅

订阅专栏

MySQL

6 篇文章 0 订阅

订阅专栏

Ado

5 篇文章 0 订阅

订阅专栏

该博客介绍了如何使用Access 2019作为前端，通过VBA和ADO（ActiveX Data Objects）库连接到本地MySQL 8.0服务器进行数据操作。详细步骤包括设置连接字符串、定义ADO对象、构建SQL更新语句以及根据用户选择动态调整查询条件。最后，展示了如何执行SQL查询并将结果加载到Access列表中，并释放相关资源。

摘要由CSDN通过智能技术生成

MS Access, MySQL, Ado

一. Access连接到MySQL：

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

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

2.ADO的定义

在Option Compare Database下

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

3.组装连接MySQL的准备

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

二、update的sql文

dim kontakt as string
	kontakt = "SELECT 部门, 应聘职位, 姓名, 性别, @范围, 搜索, 应约 " & _
					"from Mesa_面试者 " & _
					"where TIMESTAMPDIFF(@单位, @范围, @日期)=0 @状态 " & _
					"order by @范围, 午前午後 asc, 约见时间"
					                
	kontakt = Replace(kontakt, "@范围", "" & cb_9 & "日期")
	kontakt = Replace(kontakt, "@日期", "'" & Forms![fenster_导航]![txt_2] & "'")

三、select case

   Select Case Me.cb_9
   	Case "约见"
   		kontakt = Replace(kontakt, "@状态", "and 约见时间>'0'")
   	Case Else
   		kontakt = Replace(kontakt, "@状态", "")
   	End Select

	Select Case Me.cb_10
		Case "日"
			kontakt = Replace(kontakt, "@单位", "DAY")
		Case "週"
			kontakt = Replace(kontakt, "@单位", "WEEK")
		Case "月"
			kontakt = Replace(kontakt, "@单位", "MONTH")
		Case "季"
			kontakt = Replace(kontakt, "@单位", "QUARTER")
		Case "年"
			kontakt = Replace(kontakt, "@单位", "YEAR")
	End Select

四、执行

With rs
	Set .ActiveConnection = cn
		.Source = kontakt
		.LockType = 3 '' adLockOptimistic
		.CursorType = 1 '' adOpenKeyset
		.CursorLocation = 3 '' adUseClient
		.Open
End With

With Me.List_面试
	.RowSourceType = "Table/Query"
	.ColumnCount = rs.Fields.Count
	.ColumnHeads = True
	Set .Recordset = rs
End With

五、释放内存

    Set rs = Nothing
    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 cb_10_AfterUpdate()

'组装连接MySQL的准备
	constr = MYDRIVER & MYSERVER & MYPORT & MYDATABASE & MYUSER & MYPASS
	cn.ConnectionString = constr
	cn.Open
	
'update的sql文
dim kontakt as string
	kontakt = "SELECT 部门, 应聘职位, 姓名, 性别, @范围, 搜索, 应约 " & _
					"from Mesa_面试者 " & _
					"where TIMESTAMPDIFF(@单位, @范围, @日期)=0 @状态 " & _
					"order by @范围, 午前午後 asc, 约见时间"
					                
	kontakt = Replace(kontakt, "@范围", "" & cb_9 & "日期")
	kontakt = Replace(kontakt, "@日期", "'" & Forms![fenster_导航]![txt_2] & "'")

'select case
	Select Case Me.cb_9
		Case "约见"
			kontakt = Replace(kontakt, "@状态", "and 约见时间>'0'")
		Case Else
			kontakt = Replace(kontakt, "@状态", "")
		End Select
										
	Select Case Me.cb_10
		Case "日"
			kontakt = Replace(kontakt, "@单位", "DAY")
		Case "週"
			kontakt = Replace(kontakt, "@单位", "WEEK")
		Case "月"
			kontakt = Replace(kontakt, "@单位", "MONTH")
		Case "季"
			kontakt = Replace(kontakt, "@单位", "QUARTER")
		Case "年"
			kontakt = Replace(kontakt, "@单位", "YEAR")
	End Select

'执行
	With rs
		Set .ActiveConnection = cn
			.Source = kontakt
			.LockType = 3 '' adLockOptimistic
			.CursorType = 1 '' adOpenKeyset
			.CursorLocation = 3 '' adUseClient
			.Open
	End With

	With Me.List_面试
		.RowSourceType = "Table/Query"
		.ColumnCount = rs.Fields.Count
		.ColumnHeads = True
		Set .Recordset = rs
    End With

'释放内存
	Set rs = Nothing
	Set cn = Nothing
end sub