Why the Recordset from VBA just return one record?

Question

I have 3 tables, "persons", "per_resi" and "residence" This three tables form a many to many relation.

Table "person" fields: id, name etc....

Table "residence" fields: id, Street etc.....

Table "per_resi" fields: person_id and residence_id (together principal index)

Well, the problem is when I design a query in the graphic Access tool it Works as it should be.

But if I do in VBA it only return 1 record.

Dim svivienda As String
Dim rvivienda As Recordset

svivienda = "SELECT tbl_persona.Id, tbl_vivienda.Calle, tbl_vivienda.Numero " _
          & "FROM tbl_vivienda INNER JOIN (tbl_persona INNER JOIN tbl_perso_viv ON tbl_persona.Id = tbl_perso_viv.Id_persona) " _
          & "ON tbl_vivienda.Id = tbl_perso_viv.Id_vivienda WHERE tbl_persona.Id = " & 168 & ";"

Set rvivienda = CurrentDb.OpenRecordset(svivienda, dbOpenDynaset)

I have tried LEFT JOIN and RIGHT JOIN but always the same just one record on the recordset.

Any ideas?

MS access 2013

Thanks in advance.

Answer 1

Thank guys,

This was a very novel question.

Here is the answer.

The RecordCount property does not report the amount of records you have.

The value of the RecordCount property equals the number of records that have actually been accessed. For example, when you first create a dynaset or snapshot, you have accessed (or visited) only one record. If you check the RecordCount property immediately after creating the dynaset or snapshot (assuming it has at least one record), the value is 1. To visit all the records, use the MoveLast method immediately after opening the Recordset, and then use MoveFirst to return to the first record. This is not done automatically because it may be slow, especially for large result sets.

Count the number of records in a DAO Recordset

Thanks!!!

Answer 2

Add following statement :

rvivienda.MoveNext 

will return the next record of the recordset or :

rvivienda.MoveLast

will return the last record of the recordset

You will see the result.

The goosie2018's answer is CORRECT. I just show you the simple way to understand.

SUMMARY

So, I think the recordset you get from the database will not show the result look like an Array or a List, but a cursor. And the default cursor points to the first row, so if you use :

rvivienda.RecordCount

you should receive the number of records you actually got.

Sorry for my English ! And thanks for reading.