sql query

[ruomingxu]

I have a problem with recordset/SQL statement. The following is the code.

Private Sub Command1_Click()
Dim SqlQuery As String
Dim RcdSet As Recordset
Dim cnt As Integer

SqlQuery = "SELECT * FROM temp_test;"
Set RcdSet = CurrentDb.OpenRecordset(SqlQuery
cnt = RcdSet.RecordCount
RcdSet.Close
End Sub

The table "temp_test" has 6 rows. The variable cnt should return a number
"6" but instead a "1". Is there something wrong with the SQL statement? But
if I use this one:

Set RcdSet = CurrentDb.OpenRecordset("temp_test")

the variable cnt returns a number "6".

Why? Please help and thanks.

 

[David F Cox]

I believe it is because the recordset is positioned at the first record, and
only counts 1 record seen so far. If I recall correctly you have to move to
the last record to get the correct count that way.

 

[John Vinson]

SqlQuery = "SELECT * FROM temp_test;"
Set RcdSet = CurrentDb.OpenRecordset(SqlQuery
cnt = RcdSet.RecordCount
RcdSet.Close
End Sub

The table "temp_test" has 6 rows. The variable cnt should return a number
"6" but instead a "1". Is there something wrong with the SQL statement?

No, just with your expectations.

When you use the OpenRecordset method, Access checks very quickly to
see if the recordset has any records at all; an immediate RecordCount
will return 0 if there are none, and (usually) will return 1 if there
are 1 OR MORE records. Traversing the entire recordset might take a
long while if there were 5,225,412 records in the query... so it
continues to do so in the background while letting your code execute.

If you need a complete count, insert a line

RcdSet.MoveLast

prior to the RecordCount line.

John W. Vinson[MVP]

 

[ruomingxu]

David,

Thanks for your tip. Can you tell me how to move to the last record to get
the correct count, the syntax? I am not familiar with it. I appreciate your
help.

Richard

I believe it is because the recordset is positioned at the first record, and
only counts 1 record seen so far. If I recall correctly you have to move to
the last record to get the correct count that way.

I have a problem with recordset/SQL statement. The following is the code.

[quoted text clipped - 19 lines]

Why? Please help and thanks.

 

[ruomingxu]

John,

got it. It worked. Thanks.

Richard

SqlQuery = "SELECT * FROM temp_test;"
Set RcdSet = CurrentDb.OpenRecordset(SqlQuery

[quoted text clipped - 4 lines]

The table "temp_test" has 6 rows. The variable cnt should return a number
"6" but instead a "1". Is there something wrong with the SQL statement?

No, just with your expectations.

When you use the OpenRecordset method, Access checks very quickly to
see if the recordset has any records at all; an immediate RecordCount
will return 0 if there are none, and (usually) will return 1 if there
are 1 OR MORE records. Traversing the entire recordset might take a
long while if there were 5,225,412 records in the query... so it
continues to do so in the background while letting your code execute.

If you need a complete count, insert a line

RcdSet.MoveLast

prior to the RecordCount line.

John W. Vinson[MVP]