Access连接到MySql
MS Access, MySql, Ado
一. Access怎样连接到MySQL:
在本机状态下,用mysql做后台,用Access做前端
1. 连接MySQL的准备
- 驱动程序
Const MYDRIVER = “Driver={MySQL ODBC 8.0 Unicode Driver};” - 服务器
Const MYSERVER = “Server=localhost;” 或者 Const MYSERVER = “Server=127.0.0.1;” - 端口
Const MYPORT = “Port=3306;” - 数据库名称
Const MYDATABASE = “Database=abc;” - 用户名
Const MYUSER = “User=root;” - Mysql密码
Const MYPASS = “PWD=123456789abcd;”
Const MYPASS = "PWD=123456789abcd;"
Const MYDRIVER = "Driver={MySQL ODBC 8.0 Unicode Driver};"
Const MYSERVER = "Server=127.0.0.1;"
Const MYPORT = "Port=3306;"
Const MYDATABASE = "Database=abc;"
Const MYUSER = "User=root;"
Const MYPASS = "PWD=123456789abcd;"
2. ado的定义
Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
2.定义连接准备
Option Compare Database
Public constr As String 或 Public constr$
Private Sub
dim constr As String 或 dim constr$
组装连接MySQL的准备
constr = MYDRIVER & MYSERVER & MYPORT & MYDATABASE & MYUSER & MYPASS
3. 有效运作的部分
cn.ConnectionString = constr
cn.Open
Set Me.subform_入职.Form.Recordset = CurrentDb.OpenRecordset(select * from tbl_abc, dbOpenDynaset)
4.清除内存
cn.Close
Set rs = Nothing
Set cn = Nothing
5.完整代码
Option Compare Database
Const MYDRIVER = "Driver={MySQL ODBC 8.0 Unicode Driver};"
Const MYSERVER = "Server=127.0.0.1;"
Const MYPORT = "Port=3306;"
Const MYDATABASE = "Database=abc;"
Const MYUSER = "User=root;"
Const MYPASS = "PWD=123456789abcd;"
Public constr As String
Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
Private Sub cb_2_AfterUpdate()
constr = MYDRIVER & MYSERVER & MYPORT & MYDATABASE & MYUSER & MYPASS
cn.ConnectionString = constr
cn.Open
Set Me.subform_abc.Form.Recordset = CurrentDb.OpenRecordset(select * from tbl_abc, dbOpenDynaset)
cn.Close
Set rs = Nothing
Set cn = Nothing
end sub
Option Compare Database
Const MYDRIVER = "Driver={MySQL ODBC 8.0 Unicode Driver};"
Const MYSERVER = "Server=127.0.0.1;"
Const MYPORT = "Port=3306;"
Const MYDATABASE = "Database=abc;"
Const MYUSER = "User=root;"
Const MYPASS = "PWD=123456789abcd;"
Public constr As String
Public cn As New ADODB.Connection
Public rs As New ADODB.Recordset
Private Sub cb_2_AfterUpdate()
constr = MYDRIVER & MYSERVER & MYPORT & MYDATABASE & MYUSER & MYPASS
cn.ConnectionString = constr
cn.Open
rs.CursorLocation = adUseServer
dim sqlstring as string
sqlstring="select * from tbl_abc"
rs.Open sqlstring, cn, adOpenKeyset, adLockOptimistic
Set Me.subform_入职.Form.Recordset = rs
cn.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
end subn
总结:
- 之前access的sql大部分还是和mysql的是通用的