VBA对MySQL的的数据库进⾏读取和写⼊操作
"以下代码⽤于32位系统,Office 2003,环境,MySql版本5.1
"在使⽤前需要先安装MySql的驱动,进⾏正确配置
mysql下载odbc失败"注意:必须给出正确的服务器名,数据库名,表名,数据库连接的⽤户名,密码
选项显式
Dim Cnn作为ADODB.Connection"定义ADO连接对象
Dim记录为ADODB.Recordset"定义ADO记录集对象
"连接到数据库
函数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
"关闭连接
函数CnnClose()
如果Cnn.State = 1那么
Cnn.Close
结束如果
结束函数
"取得记录集
函数GetRecordset(ByVal SqlStr As String)
Set Records = CreateObject(“dset”)
Records.CursorType = adOpenStatic"设置游标类型,否则⽆法获得⾏数
Records.CursorLocation = adUseClient"设置游标属性,否则⽆法获得⾏数
"对于连接对象的执⾏⽅法产⽣的记录集对象,⼀般是⼀个只读并且只向前的记录集
"如果需要对记录集进⾏操作,譬如修改和增加,则需要⽤⼀个记录对象
“并正确设置好CursorType和LockType为适当类型,然后调⽤打开⽅法打开
Records.Open SqlStr,Cnn "使⽤这个语句,⾏数将返回-1,设置记录= Conn.Execute(SqlStr)
结束函数
"写⼊Excel表
函数输⼊表(ByVal) SheetName As String)
Dim Columns,Rows As Integer
Dim i,j As Integer
Columns = Records.Fields.Count
Rows = Records.RecordCount
如果Records.EOF = False且Records.BOF = False那么
对于i = 0到⾏ - 1
对于j = 0到列 - 1个
表格(SheetName)。细胞(i + 2,j + 1)。选择
表格(SheetName) .Cells(i + 2,j + 1)= Records.Fields.Item(j).Value
Next
Records.MoveNext
Next
End If
Sheets(SheetName).Cells(1,1)。选择
MsgBox“输出!”,vb OKOnly ,“MySql to Excel”
End Function
"把Excel写⼊MySql中的数据库
函数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。打开“插⼊新表值("”&ActiveSheet.Cells(i,1).Value&“",”&“"”&ActiveSheet.Cells(i,2 ).Value
&“")”,cnn,0
For i = 2 To
Sows 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。打开SqlStr,cnn,0不能⽤这条语句实现下⼀个
MsgBox“插⼊!”,vbOKOnly,“Excel To MySql”
End Function
"
清除对象功能ClearObj()
设置Cnn = Nothing
Set Records = Nothing
End Function
"获得数据表的字段名称
"OpenSchema可以获得数据库的各种信息
函数InputColumns(ByVal SheetName As String)
CnnOpen“localhost”,“mydb”,“employees”,“root”,“”
Set Records = Cnn.OpenSchema( adSchemaColumns)
Dim i As Integer
i = 1
⽽Not Records.EOF
Sheets(SheetName).Cells(1,i)= Records!COLUMN_NAME
i = i + 1
Records.MoveNext
Wend
CnnClose
ClearObj
结束函数

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