Suppose you have two tables named Table1 and Table2 with one or more fields named the same way. Now, consider this query:

rs.Open "SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id"

In our example, both table1 and table2 share the same field name “field1”, but with different content. Our problem is how to reference each field so to gain access to its value. The statement:

? rs("field1")

is ambiguous because it might refer both to table1 or table2; usually it returns the field value related to last joined table (table2 in the example), but it’s not guaranteed and might depend on the specific OLE DB providers and other factors. Specifying the table name – such as rs(“table1.field1”) – returns an error because ADO doesn’t store field names with their table path, so using the following query doesn’t help:

rs.Open "SELECT table1.*, table2.* FROM table1 INNER JOIN table2 ON table1.id = " _    & "table2.id"

To solve this problem you must use aliases for field names. This method forces you to specify all the ambiguous fields:

rs.Open "SELECT table1.field1 as Name, table2.field1 as Surname, * FROM table1 " _    & "INNER JOIN table2 ON table1.id = table2.id"

Now you can refer to any field by using the alias instead of real name:

? rs("Name"), rs("Surname")