ADODB Access

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
 
B

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.
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

stuck in a loop 5
query on query 1
Drop Down doesn't populate form 0
Run time error in VBA Code 1
Treeview Control 0
Recordset 3
Data Type Conversion error 5
Split not working correctly 2

Top