Is there a better way to do it?

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

   I have 2 tables which share a common field. In a view, i must show the
fields from the first table and 1 field from the second table, At this
moment, I create two recordsets:

Set RS1 = MyConn.Execute ("Select * FROM tblDoctori where UserName="& Session("UserName") &"")

Set RS2 = MyConn.Execute ("Select strLocalitate, strLM FROM tblHospitals where strLM = "& RS1("strLM") &"")

...

' then display it.

My question is, is there a better way to do it?

My impression is that for every item in the first recordset, there is a new trip to db which might be very resource consumming.

PS If this is wrong, the solution has to work for MS SQL, mySQL and Access in the same time.