Joining tables & traversing with VBA

[peskywinnets]

Up until now I've only ever referenced one table in VBA for the purposes of traversing.

First question... it's possible to join tables & then treat as one 'entity'?

If so, then what happens in the scenario where the two tables have the same name for a a field, for example table1.ID & table2.ID?

For a single table I'd normally just do something like this...

if rst!ID = 1 then blah blah

but if both tables have a field called ID, then how do I differentiate them?

(Sorry for the n00bescent line of questionage)

 

[stopher]

If you mean "join" as in the query sense i.e. that there is a common key, then just create a join query with the two tables and use the query as your recordset source. You can rename fields in the query if there is a conflict of field names.

If you want to refer to two tables independently then you can create more than one recordset..

Set rst1 =
Set rst2 =

hth
Chris

 

[arnelgp]

if your intension of Joining is joining in query, then the resulting recordset will only have one ID (if you are joining in this field).
you can differentiate them (if both have same field names) by renaming the field of the other table in the query, ie:

if both have "firstname" field you can rename the other to table2_firstname:

select t1.firstname, t2.firstname as table2_firstname from table1 as t1 inner join table2 as t2 on t1.id = t2.id;