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