>>Open("SELECT * from table")

This never scales. First off, Select * is bad practice. Second, what happens when you start getting 1000, 2000 or more rows...


>>.Open("SELECT * FROM table WHERE pkey = 1111")


Certainly better

>I ask this in the context of a lookup table that needs to be hit up about 100 times in a big loop. Right now, everything is "Open" where I SELECT for the single key and I'm thinking of changing that if some benefit will be derived by opening the entire table all at once and using Find in the loop.
>
>It's a big programming task and I was looking for some informed opinions first.
>


If you are using SQL Server, why don't you do these sort of operations in a stored procedure. What exactly are you trying to accomplish? Are you looking to return a result set? Or, are you looking to update data? If it is all data driven, I ask again why not do this sort of thing in a stored proc since there is most likely, not a need to return records to a client.


In reality, this issue has nothing to do with ADO per-se...