使用VBA在Access表中的记录计数 [英] Record count in Access table using VBA
问题描述
我正在尝试获取表的记录计数,如果计数大于17,则创建一个新表.
I'm trying to get the record count of a table, and if count is greater than 17, create a new table.
Dim rst As DAO.Recordset
strSQL = "Select * from SKUS"
Set rst = db.OpenRecordset(strSQL)
If rst.RecordCount > 17 Then
Set tdf = db.CreateTableDef("161-0363")
Set fld = tdf.CreateField("SKUS", dbText, 30)
tdf.Fields.Append fld
Set fld = tdf.CreateField("Count", dbInteger)
tdf.Fields.Append fld
db.TableDefs.Append tdf
End If
此代码不会创建新表,但是当我将if语句更改为此时,它会起作用:
This code doesn't create a new table, but when I change the if statement to this, it works:
...
If rst.RecordCount > 0 Then
Set tdf = db.CreateTableDef("161-0363")
...
因此,我认为RecordCount返回1.为什么会这样呢?我肯定知道表中有18行.
有人可以帮我吗?
So the RecordCount is returning 1, I think. Why is this happening? I know for sure the table has 18 rows in it.
Can anyone help me out?
推荐答案
您必须强制记录集遍历所有行以获得准确的计数.试试这个:
You have to force the recordset to move through all the rows to get an accurate count. Try this:
...
Set rst = db.OpenRecordset(strSQL)
rst.MoveLast
rst.MoveFirst
If rst.RecordCount > 17 Then
...
除非您对未显示在代码段中的记录集进行其他操作,否则可以通过使用域计数功能检查记录计数来简化代码:
Unless you are doing something else with the recordset that you're not showing in your snippet, you can simplify your code by just doing a check of the record count using the domain count function:
If DCount("*", "SKUS") > 17 Then
这篇关于使用VBA在Access表中的记录计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!