vba如何读取mysql数据_VBA对MySql数据库进⾏读取和写⼊
操作
'以下代码⽤于32位系统,Office 2003，环境,MySql版本5.1
'在使⽤前需要先安装MySql的驱动，进⾏正确配置
'注意：必须给出正确的服务器名、数据库名、表名、数据库连接的⽤户名、密码Option Explicit
Dim Cnn As ADODB.Connection '定义ADO连接对象
Dim Records As ADODB.Recordset '定义ADO记录集对象
'连接到数据库
Function CnnOpen(ByVal ServerName As String, ByVal DBName As String, ByVal TblName As String, ByVal User As String, ByVal PWD As String) '服务器名或IP、数据库名、登录⽤户、密码Dim CnnStr As String '定义连接字符串Set Cnn =
CreateObject("ADODB.Connection") '创建ADO连接对象
Cnn.CommandTimeout = 15 '设置超时时间
CnnStr = "DRIVER={MySql ODBC 5.1 Driver};SERVER=" & ServerName & ";Database=" & DBName & ";Uid=" & User &
";Pwd=" & PWD & ";Stmt=set names GBK" '
Cnn.ConnectionString = CnnStr
Cnn.Open
End Function
'关闭连接Function CnnClose()
If Cnn.State = 1 Then
Cnn.Close
End If
End Function
'取得记录集
Function GetRecordset(ByVal SqlStr As String)
Set Records = CreateObject("dset")
Records.CursorType = adOpenStatic'设置游标类型，否则⽆法获得⾏数Records.CursorLocation = adUseClient '设置游标属性，否则⽆法获得⾏数
'对于Connection对象的Execute⽅法产⽣的记录集对象，⼀般是⼀个只读并且只向前的记录集
'如果需要对记录集进⾏操作，譬如修改和增加，则需要⽤⼀个Recordset对象
'并正确设置好CursorType和LockType为适当类型，然后调⽤Open⽅法打开Records.Open SqlStr, Cnn'使⽤这个语句,⾏数将返回-1,Set Records = Conn.Execute(SqlStr)End Function
'写⼊Excel表
Function InputSheet(ByVal SheetName As String)
Dim Columns, Rows As Integer
Dim i, j As Integer
Columns = Records.Fields.Count
Rows = Records.RecordCount
If Records.EOF = False And Records.BOF = False Then
For i = 0 To Rows - 1
For j = 0 To Columns - 1
Sheets(SheetName).Cells(i + 2, j + 1).Select
Sheets(SheetName).Cells(i + 2, j + 1) = Records.Fields.Item(j).Value
Next
Records.MoveNextmysql下载odbc失败
Next
End If
Sheets(SheetName).Cells(1, 1).Select
MsgBox "Output!", vbOKOnly, "MySql to Excel"
End Function
'把Excel写⼊MySql中的数据库Function InsertToMySql(ByVal SheetName As String, ByVal TblName As String)
Dim SqlStr As String
Dim i, j As Integer
Dim Columns, Rows As Integer
Columns = VBAProject.func_public.GetTotalColumns(SheetName)
Rows = VBAProject.func_public.GetTotalRows(SheetName)
Set Records = CreateObject("dset")
'取得结果集并插⼊数据到数据库
Set Records = CreateObject("ADODB.Recordset")
'以下语句提供了插⼊思路，我只是把单条记录的插⼊⽅式改为循环，以把所有的记录添加到表中
'rs.Open "insert  into  newtable  values('" & ActiveSheet.Cells(i, 1).Value & "'," & "'" & ActiveSheet.Cells(i, 2).Value & "')", cnn, 0
For i = 2 To Rows
SqlStr = "INSERT INTO " & TblName & " values('" & Sheets(SheetName).Cells(i, 1).Value & "'" '注意：" values('"，字母“v”之前是有空格的
For j = 2 To Columns
SqlStr = SqlStr & ",'" & Sheets(SheetName).Cells(i, j).Value & "'"
Next
SqlStr = SqlStr & ")"
Set Records = Cnn.Execute(SqlStr) 'rs.Open SqlStr, cnn, 0  不能⽤这条语句实现
Next
MsgBox "Insert!", vbOKOnly, "Excel To MySql"
End Function
'清除对象
Function ClearObj()
Set Cnn = Nothing
Set Records = Nothing
End Function
'获得数据表的字段名称
'OpenSchema可以获得数据库的各种信息
Function InputColumns(ByVal SheetName As String) CnnOpen "localhost", "mydb", "employees", "root", ""
Set Records = Cnn.OpenSchema(adSchemaColumns)
Dim i As Integer
i = 1
While Not Records.EOF
Sheets(SheetName).Cells(1, i) = Records!COLUMN_NAME i = i + 1
Records.MoveNext
Wend
CnnCloseClearObjEnd Function

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。