Previewing a Temporary SELECT Query
Last night I was working on a database where I wanted to run a temporary query from a linked table that I had. The query was temporary because it's being used in a tool that I'll only run a handful of times. Nevertheless, the data from the query is going to end up in a production database, so I wanted to view the results of the query before clicking the button that said, "Yes, go ahead and make this data live." The query is written using SQL text in a text box on a form because I'm going to pass it to a function, and the fact is that there are a few linked tables, with different schemas and criteria.
Creating a temporary query is pretty straight forward. DAO has had the ability to create a QueryDef object without a name for many years, and the database engine treats these as temporary in the sense that you can use them but they're not saved to the file. These work great for action queries, and perhaps even SELECT queries when you're going to process a Recordset. Again however, I really wanted to view the data in the query before processing the Recordset. Enter my dilemma.
It would be really great if the OpenQuery method had an acDialog parameter like OpenForm and OpenReport that would open the datasheet view of a query but block running code until the query window was closed. Barring this, it looks like I might need a form or some way to fake it. The problem with a form is that nothing appears in the form until there are controls on it. This means that creating a temporary form to display the temporary query is somewhat cumbersome. The other issue with using a form of course is that you can't create them at runtime in an ACCDE or MDE. Guess I'll use a query and try to fake the experience.
The ultimate point here of course is that the query is temporary. After viewing the data, I want it to go away. Here's some code written behind a button named cmdPreviewQuery which does this. As you can see, the SQL for the query is retrieved from a text box on the form called txtQuery.
Private Sub cmdPreviewQuery_Click()
On Error GoTo PreviewQueryErrorHandler
' name of the temporary query
Const TEMPQUERYNAME As String = "_TEMP_"
Dim qd As DAO.QueryDef
Dim db As DAO.Database
' create a query using the specified SQL text in txtWFQuery
Set db = CurrentDb
Set qd = db.CreateQueryDef(TEMPQUERYNAME, Me.txtWFQuery)
' open the query and wait for it to close
DoCmd.OpenQuery TEMPQUERYNAME
While (CurrentData.AllQueries(TEMPQUERYNAME).IsLoaded)
DoEvents
Wend
Cleanup:
' suppress errors in case the query does not exist
On Error Resume Next
' delete the query when we're done with it
DoCmd.DeleteObject acQuery, TEMPQUERYNAME
On Error GoTo 0
Exit Sub
PreviewQueryErrorHandler:
MsgBox "Unhandled error: " & Err.Number & vbCrLf & Err.Description, vbExclamation
Resume Cleanup
End Sub
Once the query is created using CreateQueryDef in DAO, we'll open the query using DoCmd.OpenQuery as mentioned earlier. Since there is no 'modal' or 'dialog' experience for the query, I'm using a loop that waits for the query to close by checking the IsLoaded property of the AccessObject object for the query. The DoEvents statement in the loop ensures you can still interact with the query if you wanted. This is handy because you could use the query designer to modify the query to build what you want - all interactively while the code is running.
I'd love to hear feedback about this. Has anyone needed to do this before and if so, how did you accomplish it?
