Among the many things I miss in Access from Foxpro is the ability to enter SQL statements on-the-fly into the command box. So, as a first cut, I created a form with a textbox and a button. The textbox holds the SQL code, and a button that calls a subroutine to stuff the code into a scratch query defined in the .MDB Queries collection.

There are lots of possible refinements, error checking, parsing of other commands, etc, but already I’m taken with this as it eliminates half the clicking around when doing SQL queries. Thanks to Martin Green’s Office tips for most of the code. Here is the VBA code for the command button.

Private Sub cmdExecute_Click()
' The following code processes an on-the-fly SQL command
' entered in the text box. The command requires a "scratch"
' query be included in the database query collection. The code
' takes the SQL string, passes it to the scratch query, and
' then executes it.
' Code cribbed from Martin Green's Office Tips at
' http://www.fontstuff.com

Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

strSQL = Trim(Me.txtQuery.value)

'Here is the code which will apply the SQL statement to the query:

Set db = CurrentDb
Set qdf = db.QueryDefs("qryScratch")
qdf.SQL = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryScratch"
End Sub