Access VBA accessing element of recordset

Question

How do I select the actual variable if the recordset is JOINed from to tables?

 Dim db As DAO.Database
 Set db = CurrentDb
 Dim rs As DAO.Recordset
 Set rs = Currentdb.OpenRecordset("SELECT tableA.data, tableB.data “ & _
                                  “FROM tabelA INNER JOIN tableB ON tableA.ID = tableB.xyz”)

 Debug.Print (srs!tableA.data) ' does not work

Yes, data could be an element of tableA or B ... but how to specify?

THank you, Reinhard

Workaround like "as TabA_data" works but there must be a more elegant solution?

The alias is certainly the most elegant solution for this situation (at least if you can't rename the fields). :-) — Andre
– Andre, Commented Sep 29, 2023 at 10:39

Applecore · Accepted Answer · 2023-09-29 09:51:03Z

3

If you do indeed have two fields in different tables with the same name, then you have several options.

Firstly, you could actually name the fields differently in the tables. For example, Address in table Customer could become CustomerAddress and Address in Supplier could be SupplierAddress. This is probably the approach that I would recommend, as it prevents ambiguity/confusion.
Secondly, as you have mentioned, you could alias the field names in the query.
Finally, you could refer to the fields in the recordset by their position:

Debug.Print rs(0)  ' Recordsets are 0-indexed, so this returns the first field

I'm not keen on doing this, as if extra fields are introduced into the recordset, this may no longer work.

answered Sep 29, 2023 at 9:51

Applecore

4,0792 gold badges11 silver badges15 bronze badges

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

3 Comments

Tenior Over a year ago

Accessing fields of recordsets by their index does definitely still work. rs.fields(0) will return the first field in the recordset.

Applecore Over a year ago

What I meant, is if you originally SELECT FieldA, FieldB, and then alter the SQL to be SELECT FieldA, FieldC, FieldB, rs(1) will now return data for FieldC rather than FieldB.

Tenior Over a year ago

I see, sorry for the confusion.

Collectives™ on Stack Overflow

Access VBA accessing element of recordset

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related