Record count in Access table using VBA

Question

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

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")
...

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?

mwolfe02 · Accepted Answer · 2023-01-17 20:56:53Z

12

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    
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

If you plan to loop through the recordset after doing the RecordCount check, make sure you move back to the first record before you start your loop:

rst.MoveFirst

edited Jan 17, 2023 at 20:56

answered Jul 20, 2011 at 18:07

mwolfe02

24.3k10 gold badges102 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RazorSky Over a year ago

I was using a Linked table and once I added .MoveLast it worked fine. I didn't have .MoveLast before and it worked for every local table but not a linked table. There should never be more than 300 in my table. I know you should do more query less vba but this works for now. Thank you.

Eric Isaacs Over a year ago

For the original code, the record set doesn't seem to be being used other than to get the record count. Therefore, the .MoveFirst isn't necessary. .MoveLast will provide the accurate count by itself.

mwolfe02 Over a year ago

@EricIsaacs: Good point. I updated my answer accordingly.

LittleBobbyTables - Au Revoir · Accepted Answer · 2012-09-27 14:11:47Z

3

The problem with strsql is that when the string represents a parameter query the above code does not work.

In this case I would use a meter in the code as simplified below:

rs.movelast
X = rs.recordcount
Rs.movefirst
Do until rs.eof
    For i = 1 to X
        If i<=17 then
            Do things
        Else
            Do things
        End if
    Next i
Loop

edited Sep 27, 2012 at 14:11

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

answered Sep 27, 2012 at 9:08

Costas Alimonos

311 bronze badge

1 Comment

LittleBobbyTables - Au Revoir Over a year ago

Aren't you missing an rs.movenext in your loop?

Eric Isaacs · Accepted Answer · 2023-01-16 17:54:40Z

0

You need to move through the recordset to get an accurate count of records in DAO. Since the recordset is only used to get a count of the records, you can just do rst.MoveLast and then the rst.RecordCount will be accurate.

Depending on the type of recordset being used (ex: forward only) the rst.MoveFirst line suggested by others above may not always work. If you're not touring through all the records individually, just do the .MoveLast without the .MoveFirst.

... Set rst = db.OpenRecordset(strSQL)

rst.MoveLast If rst.RecordCount > 17 Then ...

answered Jan 16, 2023 at 17:54

Eric Isaacs

1992 silver badges6 bronze badges

Collectives™ on Stack Overflow

Record count in Access table using VBA

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related