Sub AccessTableTotal()
Dim Conn As New ADODB.Connection
Dim Rec As New ADODB.Recordset
Dim rsTemp As New ADODB.Recordset
Dim strSQL As String
With Conn
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\TotalData.mdb; " '改成自己的mdb路径和名称
.Open
End With
strSQL = "select * from 汇总表"
Rec.Open strSQL, Conn, adOpenKeyset, adLockPessimistic
If Rec.RecordCount > 0 Then
Conn.Execute "Delete * from 汇总表"
End If
Set Rec = Nothing
'判断有几张数据表
Set rsTemp = Conn.OpenSchema(adSchemaTables)
Do While Not rsTemp.EOF
Debug.Print rsTemp!TABLE_NAME
If Left(rsTemp!TABLE_NAME, 1) <> "~" And Left(rsTemp!TABLE_NAME, 4) <> "MSys" And rsTemp!TABLE_NAME <> "汇总表" Then
strSQL
一段多个access表汇总的简单样例 (备忘 根据情况修改相应代码可实现excel多表入access汇总)
这是一个使用VBA在Access中将多个表格数据汇总到一个汇总表的代码示例。首先建立ADODB连接,然后清空现有汇总表,接着遍历数据库中的表格,跳过系统表和临时表,将数据插入汇总表,并更新tablename字段。最后删除汇总表中ttl为0的记录。
摘要由CSDN通过智能技术生成