VBA连接mysql数据库 代码示例

数据库信息的设置：将数据库设置分为几个模块。

①DB配置（config）

服务器：servervip

数据库信息：database

用户名：user

密码：pqssword

②连接语句strConn

strConn = "Driver={MySQL ODBC 5.2 Unicode Driver};" & "Server=ip;" & "database=database;" & "USER=user;" & "PASSWORD=password;" & "OPTION=3;"

其中option指的是

'DB信息

Public Db_sevip, Db_name, Db_user, Db_pwd As String '设置具体数据库

Public db_connection As ADODB.Connection  '定义数据库连接

Public Records As ADODB.Recordset                    '定义数据记录集对象

Private Sub set_db_config() '数据库的具体设置

Db_sevip = "ip"
Db_name = "database"
Db_user = "user"
Db_pwd = "password"
 Debug.Print Db_sevip
End Sub

Public Sub open_mysql_db()  '打开sql数据源  设置连接方式
Call set_db_config '调用数据源的配置
Dim strConn As String
strConn = "Driver={MySQL ODBC 5.2 Unicode Driver};" & "Server=ip;" & "database=database;" & "USER=user;" & "PASSWORD=password;" & "OPTION=3;"

Debug.Print strConn
Set db_connection = New ADODB.Connection
db_connection.Open (strConn)
End Sub

Public Sub close_db()
If db_connection.State = 1 Then
db_connection.Close
      Set Conn = Nothing
End If
End Sub

Public Function read_sql_count(sql As String) As Integer
  Set Records = CreateObject("ADODB.recordset")
  Records.CursorType = adOpenStatic        '设置游标类型,否则无法获得行数
  Records.CursorLocation = adUseClient       '设置游标属性,否则无法获得行数
  
  Records.Open sql, db_connection
  
  Debug.Print Records.RecordCount
  
  read_sql_count = Records.RecordCount
End Function


Public Sub   excute_Sql(sqlStr As String)
    Dim strConn As String, strSQL As String

    On Error GoTo Err
    
    db_connection.CommandTimeout = 10
    db_connection.Execute sqlStr
    
    Exit Sub
Err:
    MsgBox "sql 执行出错" + Chr(10) + sqlStr

End Sub