ADODB Access

  • Thread starter Thread starter Dhonan
  • Start date Start date
D

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
 
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.
 
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
 
Back
Top