VBA操作数据库

操作数据库"/>

VBA操作数据库

相关背景：

对于数据分析同学，一般SQL，EXCEL是必备技能，但对于VBA和Python可能有的同学不会；在处理本地数据上(诸如excel、txt|csv文本），后续尝试使用VBA或者Python写一个sql查询的GUI界面（后续有空再写）；

以下仅仅为一些笔记

excel简易sql查询插件，可转：

'一、Connection对象'1 建立和数据库的连接'.Open' Dim con As New Connection' Set con = CreateObject("ADODB.Connection")    ' con.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.Path & "/Database/exceldata.xls"' 数据连接' con.Open "Provider=Microsoft.ace.Oledb.12.0;" _& "Extended Properties=Excel 12.0;" _& "Data Source=" & ThisWorkbook.FullName'Conn.Open:打开数据库的连接'provider=microsoft.jet.oledb.4.0 数据库引擎版本，该引擎主要用于excel2003；Provider=Microsoft.ace.Oledb.12.0;用于excel2007及以上版本'extended properties=excel 8.0 连接的是Excel8.0版本(excel2000以后的版本),Excel不是标准的数据库格式,所以要设置扩展属性'data source=" & ThisWorkbook.Path & "/数据库.xls" 数据库路径'************以下是连接其他数据库或文件的字符串表达式*********************************'1 Mysql数据库'strDriver = "Provider=SQLOLEDB;DataSource=" & Path & ";Initial Catolog=" & strDataName'2 TXT文件'strDriver = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='text;IMEX=1;HDR=NO;FMT=Delimited;';Data Source=" & Path'3 MSSQL数据库'strDriver = "Provider=MSDASQL;Driver={SQL Server};Server=" & Path & ";Database=" & strDataName'4 Oracle数据库'strDriver= "Provider=madaora;Data Source=MyOracleDB; User Id=UserID; Password=Password"'2 执行sql语句'.Execute SQL'增加新表格：.Execute "Create 表格名 字段和属性"'增加新记录：.Execute "Insert into 表名 (字段1, 字段2,... 字段n) VALUES(值1,值2,... 值n)"'删除记录：  .Execute "Delete from 表名 where 条件'修改旧记录：.Execute "Update 表名称 SET 列1 = 新值,列2=新值 WHERE 列名称 = 某值'筛选记录:   .Execute "Select 字段 from 表 where 条件'二、Recordset对象'作用 打开记录集操作记录'1 打开游标(记录集)'rst.Open sql或command语句等,已打开的conn链接,' Set rst = CreateObject("ADODB.Recordset")'2 添加新记录'AddNew 单个字段或数组,单个值或数组'或'       rst.AddNew '添加新的记录
'       rst.Fields("姓名") = "伍天明" 'Fields("字段名")表示某列的记录
'       rst.Fields("年龄") = 28
'       rst.Fields("性别") = "男"
'       rst.Update '添加记录后要更新'3 修改记录'rst.Update 字段数组, 值或数组'4 删除记录'rst.delete'5 在记录中循环'BOF 在记录的最前面'EOF 在记录的结尾'GetRows(默认值-1,Start, 字段)'Start 0从当前记录开始,1从第一条记录,2从最后一条记录开始

以上转载自蓝色幻想教学

一般操作步骤：

if con.State=ADODB.ObjectStateEnum.adStateOpen then "连接成功" 可用来判断数据库链接是否成功

ActiveSheet.Range("A2").CopyFromRecordset rst 可以rst.Open query_sql, con, 1, 1把返回的数据集写到活动工作表里

写入表头

With ActiveSheetcols = rs.Fields.CountFor i = 0 To cols - 1.Cells(1, i + 1).Value = rs.Fields(i).Name  ' 写入表头Next.Cells(2, 1).CopyFromRecordset rs    ' 数据写入
End With

用来判断rst是否成功返回
if rst.State=ADODB.ObjectStateEnum.adStateOpen then "rst.open 成功返回"

关于数据库引擎与连接串

' provider=microsoft.jet.oledb.4.0 数据库引擎版本，该引擎主要用于excel2003；
' Provider=Microsoft.ace.Oledb.12.0;用于excel2007及以上版本

关闭链接对象和记录集

Set con = Nothing
Set rs = Nothing

本地查询sql样例：

select t2.group,sum(t1.销售额) as sales 
from [Sheet1$] as t1 
inner join [分组$c4:d7] as t2 
on t1.姓名=t2.姓名 
where date_field<#2023/4/24# 
group by t2.group