If you have dozens or more queries, the easiest thing (using VBA) is to
combine the queries and parametes either in an array, or a table. For
my explanation, I will put the sql code in one table that will have 2
columns, an RowNumber column (you can use autonum) and a sql column -
where you will store your sql code - this would be a memo column so you
don't have to worry about the 255 char limit of a text column. Another
table that I will call ParamTable will contain the Where Clauses and
parameters. You can then loop through the table using DAO code. Then
you display the results of all the queries throug one form (or report).
In the Display form you will have the maximum number of textboxes that
the largest query will return. Display the form in datasheet view.
Generally, this will be a subform on a main form. You invoke the
queries through the main form and display the results of each query in
the subform.
the subform will be based on a results table. As you loop through your
parameter list in the main form, you will populate the results table
with the results of the current query and display those results in the
subform. You can hide the columns that are not in use in the subform by
looping through the controls collection of the subform and hide the
textboxes that are not in use. Here is some sample code:
Sub RunQueries()
Dim DB As DAO.Database
Dim RSsql As DAO.Recordset, RSparam As DAO.Recordset
Dim strSql As String, i As Integer, j As Integer
Dim RSresult As DAO.Recordset, RSresultTable As DAO.Recordset
Set DB = CurrentDB
Set RSsql = DB.OpenRecordset("Select sqlcolumn from sqlTable Where
IdentityCol = " & queryNumberOfyourChoice)
Set RSparam = DB.OpenRecordset("Select paramcolumn from ParamTable Where
paramNum = " & paramNumberOfyourChoice
strSql = RSsql(sqlcolumn) & RSparam
Set RSresult = DB.OpenRecordset(strSql)
Set RSresultTable = DB.OpenRecordset("ResultTable")
Do While Not RSresult.EOF
RSresultTable.AddNew
For i = 0 To RSresult.Fields.Count - 1
RSresultTable(i) = RSresult(i)
Next
RSresultTable.Update
RSresult.MoveNext
Loop
RSsql.Close
RSparam.Close
RSresult.Close
RSrestultTable.Close
End Sub
You can list the main body of the queries (the main sql) in sqlTable.
You list the Where clauses in the ParamTable. This is just the main
guts to give you an idea of how you can easily reduce the work of
hundreds of queries to a short simple routine as above. I left out
details like using a static number so you can individually iterate
through each query.
If you are not currently using these kinds of techniques in your VBA
coding, then hopefully this will give you an idea of the things you can
do with VBA. There are tons of examples on using these techniques on
the internet.
hth
Rich
*** Sent via Developersdex
http://www.developersdex.com ***
