Group queries into one action

  • Thread starter Thread starter Beginner
  • Start date Start date
B

Beginner

I have created a macro to perform a series of "Openquery"
actions. Since the number of queries is really large, is
it possible to consolidate the SQL statements into one
Procedure or whatever so that there is only one to be
maintained instead of the individual queries.

Thanks for your help
Beginner
 
I have created a macro to perform a series of "Openquery"
actions. Since the number of queries is really large, is
it possible to consolidate the SQL statements into one
Procedure or whatever so that there is only one to be
maintained instead of the individual queries.

Thanks for your help
Beginner
Click to expand...

One thing I've done when I need to execute multiple queries in
sequence (e.g. for data migration), is to use a Table. Create a Table
containing a text field QueryName, a yes/no field RunThis, and a sort
field SequenceNo specifying the order in which the queries should be
run; then write VBA code to iterate through the table executing the
queries in turn. Sample code:

Private Sub RunThemAll()
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim rs As DAO.Recordset
Dim prm As Parameter
On Error GoTo Proc_Error
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT QueryName FROM tblQueries" _
& " WHERE RunThis = True ORDER BY SequenceNo;", dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
Set qd = db.OpenQuerydef(rs!QueryName)
For Each prm In qd.Parameters ' if you have any parameter queries
prm.Value = Eval(prm.Name)
Next prm
qd.Execute dbFailOnError
rs.MoveNext
Loop
Proc_Exit:
Exit Sub
Proc_Error:
MsgBox "Error " & Err.Number & ":" & vbCrLf & Err.Description
Resume Proc_Exit
End Sub
 
Thank you for your response. This will still require to
maintain the queries. I would like to know if we could
consolidate all queries into a file and then run the file
so that the individual queries can be eliminated from the
database .

Please let me know if this is possible

Thanks in advance
 
Thank you for your response. This will still require to
maintain the queries. I would like to know if we could
consolidate all queries into a file and then run the file
so that the individual queries can be eliminated from the
database .
Click to expand...

You could store the actual SQL string of the queries in a text (or
Memo, if they exceed 255 bytes) field in the query table, and use the
RunSQL method to execute them as you step through the table. Never
done it but it should work!

I'm curious why you have a repetitive need to execute multiple action
queries; just what are they doing?
 
Back
Top