Is this lookup method possible in Access VBA?

Question

I'm using Access 2007 connecting to a MS SQL db through ODBC. I have a table called 'bases' it looks like this:

Name     ID     Site
--------------------
Home      1      111
Work      2      111
Car       3      111
Castle    4      111
Store     1      222
Rest      2      222

Now the tricky part is that the only part of this table THAT WILL KNOW is Site.
I know I can do a DLookup and get the first record (Home,1) and store these in variables.
BUT I need to somehow get the next record (Work, 2) and store these in other variables and then loop though the table until I have every record while the Site is 111, stored in variables.
Any ideas? The purpose of this is to save these returned values as strings and put them in a complicated Query eventually.

Answer 1

I'm a bit rusty, but you should be able to do a query like.

SELECT * FROM Bases WHERE Site = 111

Return this to a record set, then simply loop through the record set adding items to an array for storage and future use, depending on what you need.

If you are not familiar with recordsets, have a look at this article.

Answer 2

As onedaywhen said before me, looping through a resultset to construct a second query is not the optimal solution.

I don't know how the second (complicated) query looks like, but can't you do something like this?

select * from OtherTable 
where Name in 
(
    select Name from Bases where Site = 111
)