首先，你得安装了Access，Office全家桶自带，在数据存储和读取方面，和Excel完全不在一个量级，但我们今天只是将它做为数据存储和读取做为实例和大家一起研究研究。
本次示例场景定义为：记录用户操作日志，表头包含用户名，用户日志，录入日期。
▲ 回复"小火箭"，获取源代码
按照惯例，附上源码。
Sub btn_Rocket2Access()'// VBA写入Access数据示例'// 2024-08-24 By Sdx孙大侠//公众号: Excel小火箭Dim Cnn As ObjectDim SQL As String, dbPath As String, str As StringDim ws As WorksheetDim i As DoubleDim UserName As String, UserAction As String, Userdate As DateUserName = Environ("UserName") '//获取用户名UserAction = "操作了小火箭" '//获取操作记录Userdate = Now()Set Cnn = CreateObject("adodb.connection")dbPath = ThisWorkbook.Path & "\userlogs.accdb"str = "'" & UserName & " '" & "," & "'" & UserAction & "'" & "," & "#" & Userdate & "#" '//值SQL = "Insert into userLogs (UserName, UserActions, UserDate) VALUES (" & str & ")"With Cnn.provider = "microsoft.ace.oledb.1.0" '//打开数据库.connectionstring = "data source=" & dbPath.Open.Execute (SQL).CloseEnd WithSet Cnn = NothingEnd SubSub btn_Access2Rocket()'// VBA读取Access数据示例'// 2024-08-24 By Sdx孙大侠//公众号: Excel小火箭Dim Cnn As Object, Rst As ObjectDim SQL As String, dbPath As String, str As StringDim ws As WorksheetDim i As DoubleSet Cnn = CreateObject("adodb.connection")Set Rst = CreateObject("ADODB.Recordset")dbPath = ThisWorkbook.Path & "\userlogs.accdb"With Cnn.provider = "microsoft.ace.oledb.1.0" '//打开数据库.connectionstring = "data source=" & dbPath.OpenEnd WithSQL = "select * from userlogs "Set Rst = Cnn.Execute(SQL)Set ws = ActiveWorkbook.Worksheets(1)With wsFor i = 1 To Rst.Fields.Count.Cells(1, i) = Rst.Fields(i - 1).NameNext.Range("a2").CopyFromRecordset RstEnd WithCnn.CloseSet Rst = NothingSet Cnn = NothingEnd Sub
▲ 回复"小火箭"，获取源代码
插入数据 INSERT INTO 表名[(列名1,…)]修改数据 UPDATE 表名SET 列名1＝表达式1，列名2＝表达式2，… WHERE 条件；删除数据 DELETE FROM 表名 WHERE 条件；查询数据 SELECT 字段 FROM 表名 WHERE 条件;数据排序 SELECT * FROM 表名 ORDER BY NO DESC;
希望本期文章对你有所启发，VBA源代码已同步上传，回复"源码"获取下载地址。
