ADODB Access

[Dhonan]

I have a table that I need to access multiple times seaching for a new
primary key each time. The only was I have been able to figure out to do
this is to continually open and close the table. This seems to me to be
incredibly inefficient and clugy. Is there a better way?. Example:

Dim rs1 as new adodb.recordset
Dim rs2 as new adodb.recordset
sql="Select * from table1"
rs1.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
do until rs1.eof
SQL="select * from table2 where key1=" & rs1("keyA")
rs2.Open SQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
if not rs2.eof then
Do stuff to rs1 based on the data in rs2
end if
close.rs2
rs1.movenext
Loop

 

[Bill Mosca]

Why don't you open a recordset with the 2 tables inner joined on those 2
fields?

SQL="select * from table1 inner join table2 on table1.key!=table2.key1"

That way you are only getting the records you need.

 

[Dhonan]

Thank you Bill. I have tried to left joins, but the problem comes in that
there could be multipe records in table 1 for the same record in table2. In
this case, as far as I can tell, the query ends up not updateable.

What do you think?

Dennis

 

[Bill Mosca]

Hi Dennis

How about this approach:
SQL="select * " _
& "from table1 " _
& "where table1.keyA IN(SELECT Key1 FROM table2)"


I'm not sure if I got all the fields correctly named, but you get the idea,
right?


--
Bill Mosca, MS Access MVP
http://www.thatlldoit.com
http://mvp.support.microsoft.com/profile/Bill.Mosca
http://tech.groups.yahoo.com/group/MS_Access_Professionals