vba ado 连接mysql_Excel VBA 自定义类(ADO)连接数据库

1.首先Excel要引用相应的ActiveX库

2.新增一个类模块

'class name: adosql for vba use

Option Explicit

Private ObjConnection As New ADODB.Connection

Private ObjCommand As New ADODB.Command

Public ObjRecordSet As New ADODB.Recordset

Private para(16) As New ADODB.Parameter

Private Sub class_initialize() '构造函数

ObjConnection.CommandTimeout = 15

ObjConnection.ConnectionTimeout = 15

End Sub

Public Sub openDsn(strDSN As String) '打开数据库连接

If Len(strDSN) = 0 Then

MsgBox "DSN不能为空."

Exit Sub

End If

If Right(strDSN, 1) = ";" Then

ObjConnection.Open strDSN

Else

ObjConnection.Open strDSN & ";"

End If

End Sub

Public Sub setCmd(strQUERY As String, cmdTYPE As Integer) '设置命令

ObjCommand.ActiveConnection = ObjConnection

ObjCommand.CommandText = strQUERY

ObjCommand.CommandType = cmdTYPE '1-语句 4-存储过程

ObjConnection.CursorLocation = 3 '本地游标库提供的客户端游标

ObjRecordSet.CursorType = 3 '静态游标

End Sub

Public Sub inpara(s As Integer, paname As String, paformat As String, palen As String, pavalue As String) '参数个数 参数名 字符类型 长度 值

Set para(s) = ObjCommand.CreateParameter(paname,