dynamic queries

[SAm]

Hi

I am running a query and then another query on top of that. so the first one
has a iif statement for department and second query uses the department to
look up a rate. i am looping in vba to get this queries to run for multiple
identical tables. which means, in each loop i will rewrite the query (1 and
2) to select from a table_loop_x, so that each table can be queried. for one
query this is simple. my question is since this is one query on top of
another, what is the best way to do this.

alternatives:

1. store query1 as a query and then use vba to save my qurey as a querydef.
problem is i don't like using dao, i am more familiar with ado.

2. open first query as a recordset and then query that recordset.

not sure if there is another way to do this. please help me out. (also, if
you have the dao script it will help immensely, since i am not so familiar
with that)

thanks,

sam

 

[Klatuu]

The first problem is your database design which leads to the current problem.
You must expect to have many similar problems in the future. You should not
have 12 identical tables. You should have 1 table with a field to idenitify
which of the 12 groups the record belongs.

But, to answer the question, you can modify the SQL of a store query. So,
you could loop through each table, modify the query to run for that table,
and run the query.

 

[SAm]

let me clarify. i am only designing reports. these different identical tables
represent one table of different databases. so i have huge databases that are
set up for multiple companies. each database is in access that has about 50
or so tables. i am using one of them. i use link to each table. so i have
maybe 20 or 30 identical links.

you answer i didn't understand. are you saying that the only way to do this
(or the advised way) is to use dao and modifying the querydef?

thanks,

sam

 

[Klatuu]

No DAO involved. It is all VBA manipulating Access Objects.

Dim qdf As QueryDef
Dim varTables as Variant
Dim lngX As Long
Dim strSQL As STring
Dim strTableName As String

Set qdf = CurrentDb.Querydefs("QueryName")
strSQL = qdf.SQL
varTables = Array("Table1","Table2", "Table3", "Table4")
For lngX = 0 To Ubound(varTables)
strTableName = varTables(lngX)
'Here use some logic to put the name of the table in the SQL
qdf.SQL = strSQL
'Execute the query
Next lngX

 

[SAm]

thanks a lot.

actually for the looping i have a special table that i need for my lookup.

thanks a bunch,

sam