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?

Pulsimeter

Answer 1

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

Stemware

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. – Manchu

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. – Od

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

Answer 2

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

Elwaine

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

Answer 3

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

Od