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?

Answer 1

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

Answer 2

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